Sql:In Clause in Stored Procedure:How to Pass Values

SQL : in clause in stored procedure:how to pass values

For SQL Server 2005, check out Erland Sommarskog's excellent Arrays and Lists in SQL Server 2005 article which shows some techniques how to deal with lists and arrays in SQL Server 2005 (he also has another article for SQL Server 2000).

If you could upgrade to SQL Server 2008, you can use the new feature called "table valued parameter":

First, create a user-defined table type

CREATE TYPE dbo.MyUserIDs AS TABLE (UserID INT NOT NULL)

Secondly, use that table type in your stored procedure as a parameter:

CREATE PROC proc_GetUsers @UserIDTable MyUserIDs READONLY 
AS
SELECT * FROM dbo.Users
    WHERE userid IN (SELECT UserID FROM @UserIDTable)

See details here.

Marc

SQL Stored Procedure for using IN clause with multiple values

You can pass all values in single varchar(max) object.

Then in your sp, you can split your values by using split function and then put 'IN' clause on it.

Create procedure sp_test
@var1 nvarchar(max)
as
begin
    select * from tblEmp e
    Inner join tblDepartment d on d.DeptID = e.DeptID
    where d.DeptID IN select value from dbo.split(@var1))
end

Passing parameters to IN clause in SQL Server

Use any of the split functions from here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Stored procedure - Send a list of parameters in for use with the IN clause

I don't believe that the 'IN' clause can handle an expression, I believe you need a literal.

Try:

declare @sql NVARCHAR(MAX)

declare @SPType NVARCHAR ( 30 )
declare @SPCodeA int

set @SPCodeA  = 75
set @SPType  = '''I'',''M'''
SET @sql = 'select * from table where CodeA=' + CONVERT(NVARCHAR(MAX), @spcodea) +' and Type in (' + @sptype + ')'

EXEC (@sql)

How to pass string array in 'In' clause in sql stored procedure

I don't think you can pass a TVP-value using the SSMS gui (or at least I'm not aware of how to do it), but you have to do it in t-sql code, like so:

-- declare a variable using the user-defined type
DECLARE @MyUsers MyUserIDs 

-- insert some data into it
INSERT INTO @MyUsers(UserID) VALUES (1),(2),(4) 

-- and pass it into the proc
EXEC Test_in_Query @MyUserids = @MyUsers 

Cannot pass string to IN clause in stored procedure

In SQL Server 2016+, you can use string_split():

WHERE dbo.Segment.Id IN (SELECT value FROM STRING_SPLIT(@SegmentIds, ','))

You can use any string split function for this purpose. You can also just use `like:

where ',' + @SegmentIds + ',' like '%,' + convert(varchar(255), dbo.Segment.Id) + ',%'

---

Related Topics