Passing Int to dynamic stored procedure fails-CodePudding

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?

CodePudding user response：

Just to try to add some clarity, when you add together two different types, SQL Server will (where it can) implicitly convert one to the other - the result must be a single type after all.

It decides which one to convert "to the other" based on an order of precedence.

So where you are trying to concatenate a varchar with an int, the int has the higher order of precedence. This is also a common cause of errors and bugs when using a case expression when mixing types in different execution paths of the expression.

You need to be explicit and cast the int to a varchar.

Ideally you would use a parameterised query which would also reuse the cached execution plan - this may be beneficial if the cardinality of the data is similar but sometimes making the value part of the query dynamically can be advantagous, it depends on the use-case.

CodePudding user response：

This is why the syntax EXEC (@SQL) is strongly suggested against. Use sys.sp_executesql and parametrise your statement:

SET @SQL = N'SELECT ...
FROM ...
WHERE survey_id = @SurveyID ...;';

EXEC sys.sp_executesql @SQL, N'@SurveyID int',SurveyID;

CodePudding user response：

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.

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