Writing a simple SQL Server 2005 dynamic sql

Question

I am writing a fairly simple SQL query in SQL 2005, but am running into an issue and can't figure out what is wrong.

For documentation purposes, the query has to be a dynamic sql

A snippet of my query is:

@RecCreatorID int
....
....
IF (@RRecCreatorID IS NOT NULL)
    Begin
        Set @strSQL = @strSQL + ' AND RecCreatorID = @RecCreatorID'
    End

However, when I run PRINT @strSQL, what I get is

And RecCreatorID = @RecCreatorID

How can I get the actual value of @RecCreatorID to be displayed?

Answer 1

@RecCreatorID int
....
....
IF (@RRecCreatorID IS NOT NULL)
    Begin
        Set @strSQL = @strSQL + ' AND RecCreatorID =' +  cast(@RecCreatorID as varchar(50))
    End

another solution is to use sp_executesql to execute query

Answer 2

Use sp_executesql. That stored procedure will accept parameters which you can then use inside your dynamic SQL (parameter substitution). For example:

Set @strSQL = @strSQL + ' AND RecCreatorID = @RecCreatorID_PARAM'


exec sp_executesql @SQL,
N'@RecCreatorID_PARAM int',
@RecCreatorID_PARAM = @RecCreatorID

Although this doesn't do much for displaying purposes, it's still a better way of handling dynamic SQL than concatenation, imho.