Dynamic Sql Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Sql Error

Author

Topic

pericherla
Starting Member

3 Posts

Posted - 2007-11-05 : 17:49:35

I Changed whole Stored procedure for using Dynamic SQL

I found below 3 errors(Embbeded in Code)

CREATE PROCEDURE sp_getByCampusID
-- Add the parameters for the stored procedure here
@Rid int
, @clrCode int
, @clerkCode varchar(30)
, @strStatus varchar(1)
, @strTime varchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

If @Rid = '' BEGIN

Declare @SQL VarChar(1000)
SELECT @SQL = 'select * From [RequestPayment].[dbo].[EmpPurposeRfpmaster] '

If @clerkCode <> '-1'
SELECT @SQL = @SQL + 'WHERE clerk_ID IN ('+ @clrCode + ')'
END

If @strStatus <> '-1' BEGIN
If @strStatus = 'A' Or @strStatus = 'W'

SELECT @SQL = @SQL + 'WHERE STATUS = '+ @strStatus +' AND AP_REVIEW_DATE IS NULL'

IF @strStatus = 'R'
SELECT @SQL = @SQL +'WHERE AP_REVIEW_DATE IS NOT NULL'
END
ELSE
SELECT @SQL = @SQL + 'WHERE STATUS != '+ A +' AND STATUS != '+ W +''
End

If @strTime <> '-1' BEGIN
If @strTime = '1'
SELECT @SQL = @SQL + 'WHERE datediff(month, input_date, getdate()) <' + 1 + ''
If @strTime = '2'
SELECT @SQL = @SQL +'WHERE datediff(month, input_date, getdate()) < ' +3+''
If @strTime = '3'
SELECT @SQL = @SQL + 'WHERE datediff(month, input_date, getdate()) < '+ 6 +''
If @strTime = '4'
SELECT @SQL = @SQL +'WHERE datediff(month, input_date, getdate()) < '+ 12 + ''
If @strTime = '5'
SELECT @SQL = @SQL +'WHERE datediff(year, input_date, getdate()) >' + 1 +''
End
SELECT @SQL = @SQL + 'ORDER BY NAME'
END
ELSE
SELECT * From [RequestPayment].[dbo].[EmpPurposeRfpmaster] WHERE M.ID = @Rid
End --This is causing Error

EXEC (@SQL)--This is causing Error

END --This is causing Error

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-05 : 17:52:42

Run PRINT @SQL before the EXEC so you can see the badly formed statement that you are trying to execute.

Also, you really should be avoiding dynamic SQL here. There are ways to do what you want without dynamic SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 18:47:23

you need to enclose your string value in single quote

this is one of them

SELECT @SQL = @SQL + 'WHERE STATUS = ''+ @strStatus +'' AND AP_REVIEW_DATE IS NULL'

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 01:15:23

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources