Error in sql stored procedure

I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks.

ALTERprocedure [dbo].[USP_Account_Search_Mod]

@.ClientCode VARCHAR(7)=''

,@.DebtorName VARCHAR(25)=''

,@.DebtorNumberINT= 0

,@.AccountNumber VARCHAR(30)=''

,@.ReferenceNumber VARCHAR(30)=''

,@.TierINT= 0

,@.Status VARCHAR(5)=''

,@.UserIDINT

,@.MonthDateTime=NULL

,@.FromDateDateTime=NULL

,@.ToDateDateTime=NULL

,@.OriginalMindecimal= 0

,@.OriginalMaxdecimal= 0

,@.CurrentMindecimal= 0

,@.CurrentMaxdecimal=0

,@.lstAmountSelect VARCHAR(3)

,@.IsActivebit= 1

AS

DECLARE

@.SQLTier1Select VARCHAR(2000)

,@.SQLTier2Select VARCHAR(2000)

,@.Criteria VARCHAR(2000)

,@.SQL VARCHAR(8000)

,@.CRI1 VARCHAR(100)

,@.CRI2 VARCHAR(100)

,@.CRI3 VARCHAR(100)

,@.CRI4 VARCHAR(100)

,@.CRI5 VARCHAR(100)

,@.CRI6 VARCHAR(200)

,@.CRI7 VARCHAR(500)

,@.CRI8 VARCHAR(500)

,@.CRI9 VARCHAR(500)

SELECT @.CRI1=''

,@.CRI2=''

,@.CRI3=''

,@.CRI4=''

,@.CRI5=''

,@.CRI6=''

,@.CRI7=''

,@.CRI8=''

,@.CRI9=''

,@.Criteria=''

SELECT @.DebtorName=REPLACE(@.DebtorName,'''','''''');

Print @.DebtorName

if(SELECT UserTypeIDFROM dbo.tbl_Security_UsersWhere UserID= @.UserID)= 3AND @.ClientCode=''

return(-1)

IFLEN(@.DebtorName)> 0

SET @.CRI1=' AND Name like '+'''%'+ @.DebtorName+'%'''

IF @.DebtorNumber> 0

SET @.CRI2=' AND Number = '+CAST(@.DebtorNumberAS VARCHAR(7))

IFLEN(@.AccountNumber)> 1

SET @.CRI3=' AND AccountNumber like '+'''%'+ @.AccountNumber+'%'''

IFLEN(@.ReferenceNumber)> 0

SET @.CRI4=' AND Account like '+'''%'+ @.ReferenceNumber+'%'''

IFLEN(@.ClientCode)> 1

SET @.CRI5=' AND Customer = '+''''+ @.ClientCode+''''

SET @.Status=RTRIM(@.Status)

IF((@.StatusNotIN('ALL','ALA','ALI'))AND(LEN(@.Status)>1))

BEGIN

IF(@.Status='PAID')

SET @.CRI6=''

IF(@.Status='CANC')

SET @.CRI6=' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'

END

--PRINt @.CRI6

IFLEN(CONVERT(CHAR(8), @.Month, 112))> 0

BEGIN

IF(LEN(CONVERT(CHAR(8), @.FromDate, 112))> 0ANDLEN(CONVERT(CHAR(8), @.ToDate, 112))> 0)

BEGIN

SET @.CRI7=' AND Received BETWEEN '+''''+CONVERT(CHAR(8), @.FromDate, 112)+''''+' AND '+''''+CONVERT(CHAR(8), @.ToDate, 112)+''''

END

ELSE

BEGINSET @.CRI7=' AND DATEPART(mm, Received) = DATEPART(mm, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''+') AND DATEPART(yy, Received) = DATEPART(yy, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''

END

END

IF @.lstAmountSelect='ALL'

SET @.CRI8=''

elseIF @.lstAmountSelect='DR'

BEGIN

SET @.CRI8=' AND OriginalBalance >= '+convert(Varchar,@.OriginalMin)+'AND OriginalBalance<='+convert(Varchar,@.OriginalMax)+' AND CurrentBalance >= '+convert(Varchar,@.CurrentMin)+'AND CurrentBalance<='+convert(Varchar,@.CurrentMax)

END

ELSEIF @.lstAmountSelect='OLC'

BEGIN

SET @.CRI8=' AND OriginalBalance < CurrentBalance '

END

ELSEIF @.lstAmountSelect='OGC'

BEGIN

SET @.CRI8=' AND OriginalBalance > CurrentBalance '

END

ELSEIF @.lstAmountSelect='OEC'

BEGIN

SET @.CRI8=' AND OriginalBalance = CurrentBalance '

END

SELECT @.Criteria= @.CRI1+ @.CRI2+ @.CRI3+ @.CRI4+ @.CRI5+ @.CRI6+ @.CRI7+ @.CRI8

--PRINT @.Criteria

--PRINT @.CRI7

if @.Status='ALL'OR @.Status='ALA'OR @.Status='ALI'--All Period

BEGIN

if(@.Status='ALL')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'

END

if(@.Status='ALA')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'

END

if(@.Status='ALI')--All Inactive

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'

END

END

ELSEIF @.Status='PAID'

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'

END

ELSE

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

END

SELECT @.SQL=CASE @.Tier

WHEN 0THEN @.SQLTier1Select+' UNION '+ @.SQLTier2Select+'ORDER BY NAME ASC'

WHEN 1THEN @.SQLTier1Select+'ORDER BY NAME ASC'

WHEN 2THEN @.SQLTier2Select+'ORDER BY NAME ASC '

END

PRINT @.SQL

--SELECT @.SQL

EXEC(@.SQL)

Could you help us help you better by opening your stored procedure up in a query analyzer (like SQL 2005 QA or SQL Express Managment Studio) and parsing the query to determine what line number and what the syntax error is?

That is a lot of code and would be difficult for us to go over line by line to determine what the issue is.

|||

I do not have SQL Query Analyzer. Here is the error I get when I saw in event viewer

Transaction (Process ID 64) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction., Error Source:.Net SqlClient Data Provider.

|||

there's a lock on one of the tables your trying to do an insert on or update on. Talk with your DBA about that error and let him/her know what the Process ID is to help track it down.

Out of curiosity, what environment are you writing your stored procedures in?

|||

I'm writing my stored procedure in SQL Server Management Studio Express

|||

In SQL Server Management Studio Express you can parse your query (stored procedure syntax) and it will either tell you the syntax error along with the line number or tell you that the command was successful, which means you can run (execute) it and thus create your stored procedure.