How to pass a string of interger to a SP with an in() clause

mikezx10 · #1 (**permalink**) 09-29-09, 18:15

I need to pass a string to my sp but dont know how the EXEC string is below as well as the sp.

--EXEC [spGetUserCompanyUsers] '1,2,3'
ALTER PROCEDURE [dbo].[spGetUserCompanyUsers]
@CompanyIds varchar(50)
AS
BEGIN

SELECT [User].UserId
, [User].UserDesc
, Companies.CompanyId
FROM [User]
INNER JOIN UserCompany ON [User].UserId = UserCompany.UserId
INNER JOIN Companies ON UserCompany.CompanyId = Companies.CompanyId
WHERE Companies.[Name] IN(@CompanyIds)

END

Brett Kaiser · #2 (**permalink**) 09-29-09, 19:19

well it looks ok, except your IN needs to have the string deconstructed and convert to int

mikezx10 · #3 (**permalink**) 09-29-09, 19:25

How do i do that?

gvee · #4 (**permalink**) 09-30-09, 07:11

What version of SQL Server are you on?

mikezx10 · #5 (**permalink**) 09-30-09, 07:39

SQL2005 is the version

m.timoney · #6 (**permalink**) 09-30-09, 11:20

easiest ways are to use the Split function in a CLR function

another is
SQL Tip: HowTo Convert Delimited String to Table

m.timoney · #7 (**permalink**) 09-30-09, 11:40

actually that example is pretty hideous

i'd probably go for something like

Code:

create function ConvertStringToList
(
@string varchar(1000),
@delimiter char
)
returns @rtn table (VAL int)
as
begin

	declare @index int
	set @index = CHARINDEX(@delimiter, @string)


	IF @index >0
	BEGIN
		set @rtn = ConvertStringToList(SUBSTRING(@string, @index + 1, LEN(@string) - @index),@delimiter,@partiallist)
		insert into @rtn values(SUBSTRING(@string, 1, @index - 1)
	END
	ELSE
		insert into @rtn values(@string)
    return
end

how ever that was written on the fly and hasn't been tested

Internet Services

Web Development

Digital Marketing

Consumer Tech