Passing Int to dynamic stored procedure fails

Question

I have a dynamic stored procedure in SQL Server that works well to pivot a table:

CREATE PROCEDURE dbo.DynamicPivotTableInSql
    @ColumnToPivot  NVARCHAR(255),
    @ListToPivot    NVARCHAR(255),
    @SurveyID           INT=10
AS
BEGIN
    DECLARE @SqlStatement NVARCHAR(MAX)

    SET @SqlStatement = N'SELECT * 
                          FROM 
                              (SELECT
                                   [resp_id], [benefit], [weight]
                               FROM Segment_Responses) myResults
                          PIVOT 
                              (SUM([weight])
                                   FOR [' + @ColumnToPivot + ']
                                   IN (' + @ListToPivot + ')) AS PivotTable';
 
    EXEC (@SqlStatement)
END

and I call it like this

EXEC DynamicPivotTableInSql 
         @ColumnToPivot = 'benefit',
         @ListToPivot = '[OBSERVABILITY], [COST], [EASE OF USE], [SERVICE]'

Here is where I run into problems. You'll notice I have hardcoded @SurveyID = 10 and if I try to add that as a where statement in the stored procedure like this:

FROM Segment_Responses 
WHERE survey_id = ' + @SurveyID + '

and run the stored procedure again I get this error:

Conversion failed when converting the nvarchar value ' SELECT * FROM ( SELECT [resp_id], [benefit], [weight] FROM Segment_Responses where survey_id=' to data type int.

I've tried to solve this many ways (e.g., passed the Int variable instead of hard coding it) but always get the same result. Any ideas what is going on?

Answer 1

The + only works with strings. If you use a number TSQL assumes you are trying to use the addition operator, and tries to convert the string argument to int.

eg this

select 1 + '2'

works and returns 3.

Use CONCAT instead of +, or use an explicit conversion on the int.

eg

WHERE survey_id = ' + cast(@SurveyID as varchar(20)) + '