VARCHAR in parameter of IN clause in SQL Stored Procedure

Question

I have this string in PHP:

$temp = 'one, two, three, four, five, six';

I call the SQL server Stored Procedure in this way:

$tsql_callSP = "{call spThisIsCalled($temp)}";

I have the following as the Stored Procedure in SQL server and there is soome problem, I don't know what:

CREATE PROC spThisIsCalled(
    @serverVar VARCHAR(100)
)
AS
BEGIN
    SELECT Something, @serverVar
    FROM (  .............
            WHERE
                (........ IN
                ([@serverVar]))
                AS PVT;
END

The @serverVar in the Stored Procedure are the column names but somehow it is not working due to some problem. Please help.

Does `@ServerVar` is set when you call the Stored Procedure from PHP? — henrique romao, Aug 01 '17 at 19:19
SQL Server does not support macro-substitution. You would need use some DYNAMIC SQL for this — John Cappelletti, Aug 01 '17 at 19:20
You cannot use a variable to specify a column name. You need to build dynamic SQL if you want to specify a column name as an argument. — pmbAustin, Aug 01 '17 at 19:20
None of the hundreds of questions like this on this site helped you? — Tab Alleman, Aug 01 '17 at 19:24
@TabAlleman Yes, I was not able to find the exact answer that I wanted and that could help. Thanks for the participation tho. :) — Jeet Patel, Aug 02 '17 at 12:05

Maurício Pontalti Neri · Answer 1 · 2017-08-02T12:31:14.863

0

I figured it is not possible. you can try to move all your code to a variable and then execute, some thing like

CREATE PROC spThisIsCalled(
    @serverVar VARCHAR(100)
)
AS
BEGIN

declare @sqlCommand varchar(max) = 
'SELECT Something, ' + @serverVar +  ' FROM (  .............
            WHERE
                (........ IN
                (' +  @serverVar + ' ]))AS PVT; ' ;

EXEC SP_EXECUTESQL @sqlCommand
End

edited Aug 02 '17 at 12:31

answered Aug 01 '17 at 19:26

Maurício Pontalti Neri

1,070
1
9
17

Thanks for responding. I am not sure why did you include those 'N' in front of SELECT and after @serverVar. Please help with that. – Jeet Patel Aug 02 '17 at 12:29
You do not need use 'N' because you are using a varchar data type. I edited the code. – Maurício Pontalti Neri Aug 02 '17 at 12:30
Thanks. Really appreciate. :) – Jeet Patel Aug 02 '17 at 12:31

VARCHAR in parameter of IN clause in SQL Stored Procedure

1 Answers1