homepage Welcome to WebmasterWorld Guest from 54.205.7.136
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MSSQL - List as input param for a SPROC
MSSQL, Stored Proc, Subquery, IN clause
8foldpath




msg:3593668
 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




msg:3593964
 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




msg:3594301
 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]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved