MSSQL - List as input param for a SPROC - Databases forum at WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

MSSQL - List as input param for a SPROC

MSSQL, Stored Proc, Subquery, IN clause

8foldpath

2:56 am on Mar 7, 2008 (gmt 0)

I'm trying to optimize some queries on my site. I have absolutely no problems creating and running stored procedures, however, I am having issues with one scenario. I'd like like to pass a list of numeric values to an IN Clause. It seems pretty basic I know, but for some reason I'm unable to get it. Could someone help me verify if this is possible? and if not - how do you format the values and the query? I have spent hours reading SQL books searching the web. I can't find any examples of people passing lists into Stores Procedures.

@values = N'3444,2433,2343' Doesn't seem to work.

Any Insight to doing this with a stored proc?

8foldpath

11:28 am on Mar 7, 2008 (gmt 0)

I should have browsed WebmasterWorld before posting. I've found a thread that covers the issue here: [webmasterworld.com...]

ZydoSEO

5:53 pm on Mar 7, 2008 (gmt 0)

You can also create a user defined function that parses the list and returns it as a TABLE. I find this is frequently a very handy tool and preferable in some situations over building dynamic SQL. Both have their uses. It might look similar to the following:

CREATE FUNCTION [dbo].[fnDelimitedTextToTable] (
@ipText TEXT,
@ipDelimiter VARCHAR(2)
)
RETURNS @ARRAY TABLE (FieldValue VARCHAR(1024))
AS

BEGIN
DECLARE @lvStartChar INT
DECLARE @lvDelimiterPos INT
DECLARE @lvEndChar INT
DECLARE @lvListLength INT
DECLARE @lvValueLength INT

DECLARE @lvItemString VARCHAR(1000)

SET @lvListLength = DATALENGTH(@ipText)
SET @lvStartChar = 1
SET @lvDelimiterPos = CHARINDEX(@ipDelimiter, @ipText, @lvStartChar)
SET @lvEndChar = CASE @lvDelimiterPos WHEN 0 THEN DATALENGTH(@ipText) ELSE @lvDelimiterPos - 1 END
SET @lvValueLength = (@lvEndChar + 1) - @lvStartChar

WHILE @lvEndChar <= @lvListLength AND @lvValueLength > 0
BEGIN
SET @lvItemString = RTRIM(LTRIM( SUBSTRING (@ipText, @lvStartChar, @lvValueLength) ))

IF DATALENGTH(LTRIM(@lvItemString)) != 0
BEGIN
INSERT @ARRAY (FieldValue) VALUES (@lvItemString)
END

SET @lvStartChar = @lvEndChar + (LEN(@ipDelimiter) + 1)
SET @lvDelimiterPos = case (CHARINDEX(@ipDelimiter, substring(@ipText , @lvStartChar, @lvListLength), 1)) when 0 then 0 else CHARINDEX(@ipDelimiter, substring(@ipText , @lvStartChar, @lvListLength), 1) + (@lvStartChar - 1) end

SET @lvEndChar = CASE @lvDelimiterPos WHEN 0 THEN DATALENGTH(@ipText) ELSE @lvDelimiterPos - 1 END
SET @lvValueLength = (@lvEndChar + 1) - @lvStartChar
END
RETURN
END

Then you can use the results of the function call in a JOIN as if it were an actual table similar to the following

CREATE PROCEDURE [dbo].[pGetEmployeesByEmpIDAndDeptID]
@ipEmployeeIDList VARCHAR(256),
@ipDepartmentID INT
AS
SELECT *
FROM tEmployee e WITH (NOLOCK)
JOIN fnDelimitedTextToTable(@ipEmployeeIDList, ',') dt2t WITH (NOLOCK)
ON e.EmployeeID = dt2t.FieldValue
WHERE DepartmentID = @ipDepartmentID
GO

Probably a bad example. Not sure when I'd ever look for a specific list of employeeids for a given department... but it will do for an example.

So the proc might get called similar to:

EXEC pGetEmployeesByEmpIDAndDeptID('100,200,300', 123)

[edited by: ZydoSEO at 6:20 pm (utc) on Mar. 7, 2008]