Help me on Dynamic Stored Procedure

Question

I have written a dynamic stored procedure.

Its created successfully.

But now when i execute it by sending parameters , it displays an error.

I just need someone to rectify this error

Thanks

---------------------------Stored Procedure---------------------

CREATE PROCEDURE [dbo].[spSearch]
@TableName VarChar(100),
@Col VarChar(100),
@SearchValue varchar(50)
AS
Declare @SQL VarChar(1000)
SELECT @SQL = 'SELECT * FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL =@SQL + ' WHERE '+@Col
SELECT @SQL =@SQL + ' Like '+@SearchValue+'%'
Exec ( @SQL)

------------------------------------------------

That error is about % signe as

Incorrect syntax near '%'.

Plz rectify my code

Thanks

Answer 1

Can you replace EXEC(@SQL) with PRINT @SQL and see how dynamic sql is built?

Probably  you need SELECT @SQL =@SQL + ' Like '+@SearchValue+'''%'''

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

alter PROCEDURE [dbo].[spSearch]
@TableName VarChar(100),
@Col VarChar(100),
@SearchValue varchar(50)
AS
Declare @SQL VarChar(1000)
SELECT @SQL = 'SELECT * FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL =@SQL + ' WHERE '+@Col
SELECT @SQL =@SQL + ' Like '''+@SearchValue+'%'''

Exec ( @SQL)

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

thanks v.vt

It works fine. Superb

Answer 4

CREATE PROCEDURE [dbo].[spSearch]
@TableName VarChar(100),
@Col VarChar(100),
@SearchValue varchar(50)
AS
Declare @SQL nvarchar(1000),
        @param nvarchar(1000)
SELECT @SQL = 'SELECT * FROM ' + quotename(@TableName) + ' WHERE ' +
               quotename(@Col) LIKE @searchvalue + ''%'''
-- PRINT @SQL
EXEC sp_executesql @SQL, N'@searchvalue varchar(50)', @searchvalue

1) Always include a PRINT @SQL, so that you see what you are generating.

2) Embed table and column names in quotename to avoid surprises if the parameters has interesting values.

3) Use sp_executesql with parameters for things you can parameterise, like @searchvalue.

Then again, this is a fairly useless stored procedure. If you feel compelled to pass table or column name as a parameter, this indicates that the data model is flawed. Or that you are relatively new to SQL and should stay away from advanced features like dynamic SQL.

---

Erland Sommarskog, SQL Server MVP, [e-mail]