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.
....