Use of '' + in SQL Server 2005 Stored Procedure to build SQL string

I'm building a stored procedure which is rather stretching my experience. With the help of people who responded to this thread [Nested if statements in SQL Server stored procedure SELECT statement I think I'm most of the way there :)

In short, the SP takes a series of paramterised inputs to dynamically build an SQL statement that creates a temporary table of id values ordered in a specific way. The remainder of the SP, which returns the data according to the requested 开发者_如何学Pythonpage from the id values in this temporary table is all sorted.

Reconsider the use of dynamic SQL - you should really know what you are doing if you go that route.

What is the problem you are trying to solve? I am sure people here will be able to find a better solution than the dynamic SQL you are proposing to use.

Take a look at CONVERT() and CAST() for the integers.

To concatenate integer values into the dynamic SQL statement you need to convert to a varchar e.g:

....WHERE
    OT.site_id = ' + CAST(@siteid AS VARCHAR)

If the SQL statement is always going to be less than 4000 chars, I'd at least consider using sp_executesql to use parameterised SQL.

e.g.

DECLARE @SQL NVARCHAR(4000)
DECLARE @siteid INTEGER
SET @siteid = 1

SET @SQL = 'SELECT * FROM MyTable WHERE site_id = @siteid'
EXECUTE sp_executesql @SQL, N'@siteid INTEGER', @siteid

All in all, what you're doing is not likely to be very performant/scalable/maintainable and you don't really gain much from having it as a sproc. Plus you need to be very very careful to validate the input as you could open up yourself to SQL injection (hence my point about using sp_executesql with parameterised SQL).

You need to cast the int param to be a char/varchar so that you can add it to the existing string. The fact that you aren't surrounding it with quotes in the final sql means it will be interpreted as a number.