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

using the nvarchar parameter for IN clause would only possible if you would create a dynamic sql statement, which is in general not a good idea. You could split the string and insert it into a temp table/table variable. — Bernhard Kircher
– Bernhard Kircher, Commented Aug 12, 2011 at 19:29

Community · Accepted Answer · 2017-05-23 11:47:55Z

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.

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Aug 12, 2011 at 19:27

nekno

19.3k5 gold badges45 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Stored Procedure - using parameters internally

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related