thank you albao
This Stored Procedure shows how to accept a comma delimited list of ID’s and return records from a table that match the items of the delimited list:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
| -- =============================================-- Description: Returns one or more user records from the Users table.-- This sproc allows clients to have to make only one call to retrieve more than one record from a table (as opposed to calling the sproc for each record).-- The parameter is a varchar that can be a comma delimited list of UserId's.---- For example: A parameter of '1,8,9,45' will return four users of which have a UserId---- of 1, 8, 9, or 45-- =============================================ALTER PROCEDURE [dbo].[GetUsers] -- Declare the SP Parameter. @UserIdList Varchar(MAX) --The UserIdList is a comma delimited list of UserId'sAS --Create a var that defines the char that is used for the UserIdList delimiter --We need to do this because a list such as (1,2,4,99) returns true in the T-SQL IsNumeric function - which screws up the IsNumeric condition later on in this SP DECLARE @ListDelimiter VARCHAR(1) SET @ListDelimiter = ',' --Remove white spaces from the parameter string SET @UserIdList=REPLACE(@UserIdList,' ','') --Create the temporary table that will hold rows of ID's that were passed in the parameter CREATE TABLE #tmpUserList (UserId INT) DECLARE @UserId INT --This UserId var will hold the UserId value that was derived from the UserIdList --Loop until the UserIdList var has a length of zero WHILE(LEN(@UserIdList) > 0) BEGIN print (@UserIdList) --Set the UserId to the first value in the UserIdList IF(CHARINDEX(@ListDelimiter, @UserIdList) > 0) SET @UserId = LEFT(@UserIdList, CHARINDEX(@ListDelimiter, @UserIdList) - 1) ELSE SET @UserId = @UserIdList --Remove the first value from the UserIdList SET @UserIdList = RIGHT(@UserIdList, LEN(@UserIdList) - LEN(@UserId)) --Insert the UserId into the temporary table IF(ISNUMERIC(@UserId) = 1) INSERT INTO #tmpUserList (UserId) SELECT @UserId END BEGIN -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Get all the users that have a matching UserId in the tmpUserList table SELECT DISTINCT U.UserId, U.UserName,U.Email,U.FirstName, U.LastName, U.Password, U.MobilePhone, U.IsActive, U.DateAdded FROM Users U JOIN #tmpUserList UL ON U.UserId = UL.UserId BEGIN DROP TABLE #tmpUserList END END |