SQL Stored Procedure - using parameters internally

Question

I have a stored proc that I am passing two parameters into. One parameter is a table-valued parameter and the other is a nvarchar. Here is the stored proc:

    ALTER PROCEDURE [dbo].[_sp_TestProc]    
    @P1 As [dbo].[FileIdTableType] Readonly,
    @P2 As NVARCHAR (MAX)

AS
BEGIN   
    SET NOCOUNT ON;
    SELECT DISTINCT [Field1], [Field2], [Field3]
      FROM [MyTable] WHERE [Field1] IN (@P1)
      AND [Field2] IN (@P2)

END

From code, I am then passing in my DataTable as @P1 and a string of values as @P2 that looks like "'Value1', 'Value2', 'Value3', 'Value4'"

It's @P2 that is causing me problems. The Table Type works fine. Normally when using parameters such as this in my stored procs, I create a string internally, format my string with the parameters, then call sp_executeSql to have the query run. However, this won't work when using Table-Valued parameters.

Does anyone know how to work with table valued parameters along with nvarchar like this? I'm sure I'm missing something simple when trying to format this correctly. Thanks,

-Scott

Answer 1

You need to parse @P2 into a temp table or table var first. You can't use the IN operator with a single string that lists a number of values.

See this SO question.