Help with Dynamic SQL syntax - 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 2000 Forums

SQL Server Development (2000)

Help with Dynamic SQL syntax

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-12 : 09:24:53

Peter writes "What is wrong with my syntax? I have no problem running the if part of this SP, but the else part doesn't return a recordset. Actually without the like clause in the dynamic SQL, I am fine. So I believe the error is in the like clause part of the dynamic SQL.

CREATE PROCEDURE SelectElementCodes
@allowAll bit,
@IntNum varchar (10),
@stringcode varchar(1000),
@text varchar(15)
AS
If @allowAll = 1
SELECT GLCodes.CostCenter, GLCodes.Description FROM GLCodes Where (CompanyNumber = @IntNum) ORDER BY Description
Else
Declare @SQL varchar(1000)
SET @SQL = 'SELECT GLCodes.CostCenter, GLCodes.Description FROM GLCodes WHERE CompanyNumber = ' + @IntNum + ' AND CostCenter IN (' + @stringcode + ') AND Description Like ' + @text + '%' + ' ORDER BY Description'
EXEC (@SQL)

Thanks for your help."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources