I want to write a SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter.
How to do this ?
I can pass the user ids as a string separated by comma. So that I can use the
select *
from users
where userid in (userids)
E.g. : I want to select records for id's 5,6,7,8,9
How to write the stored procedure ?
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
Just use it like this will work
Create procedure sp_DoctorList
@userid varchar(100)
as
begin
exec ('select * from doctor where userid in ( '+ @userid +' )')
end
you could use dynamic sql. Pass the in statement to a Sql SP via a variable and concatenate it into a query in the SQL and execute using sp_execute sql
create procedure myproc(@clause varchar(100)) as
begin
exec sp_executesql 'select * from users where userid in ( ' + @clause +' )'
end
see my previous answer to this
this is the best source:
http://www.sommarskog.se/arrays-in-sql.html
create a split function, and use it like:
SELECT
*
FROM YourTable y
INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value
I prefer the number table approach
For this method to work, you need to do this one time table setup:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
Once the Numbers table is set up, create this function:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
(
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO
You can now easily split a CSV string into a table and join on it:
select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,')
OUTPUT:
ListValue
-----------------------
1
2
3
4
5
6777
(6 row(s) affected)
Your can pass in a CSV string into a procedure and process only rows for the given IDs:
SELECT
y.*
FROM YourTable y
INNER JOIN dbo.FN_ListToTable(',',@GivenCSV) s ON y.ID=s.ListValue
Assuming T-SQL, you can use this nice function (that returns a table).
DROP FUNCTION sp_ConvertStringToTable
GO
CREATE FUNCTION sp_ConvertStringToTable(@list ntext)
RETURNS @tbl TABLE (Position INT IDENTITY(1, 1) NOT NULL,
Value INT NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen
SET @pos = charindex(' ', @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (Value) VALUES(convert(int, @str))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(' ', @tmpstr)
END
SET @leftover = @tmpstr
END
IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (Value) VALUES(convert(int, @leftover))
RETURN
END
GO
In this way:
SELECT * FROM Users
WHERE userid IN
( SELECT Value FROM sp_ConvertStringToTable('1 2 3') )
You can change the stored function to work with comma separated strings instead of space separated ones.
If you don't want / can't use a stored function you can include the code of it inside the stored procedure where needed.
EDIT: this is incredibly more performant than the string concatenation.
try this this works for me
DECLARE @InClause NVARCHAR(100)
SET @InClause = 'tom,dick,harry'
DECLARE @SafeInClause NVARCHAR(100)
SET @SafeInClause = ',' + @InClause + ','
SELECT * FROM myTable WHERE PATINDEX(',' + myColumn + ',', @SafeInClause) > 0
Quick and dirty..
CREATE PROCEDURE SelectUsers (@UserIds VARCHAR(8000))
AS
SELECT * FROM Users
WHERE userid IN (SELECT CONVERT(VARCHAR(8000), value) FROM STRING_SPLIT(@UserIds, ','))
EXEC SelectUsers @UserIds = 'a1b2,c3d4,e5f6'
You can also use Find_IN_SET instead of IN. See the query below
create procedure myproc(IN in_user_ids varchar(100))
begin
select * from users where FIND_IN_SET(userid, in_user_ids);
end
