Stored Procedure Problem

[sonya9879]

Hi,

I am having a problem trying to pass a variable to a stored procedure.

create procedure test
@word varchar(10),
@sectionID varchar(max)
as
begin
SELECT * FROM [testtable] WHERE sectionID IN (@sectionID)
end

sectionID sometimes is just one value, like for example 1 and sometimes several different values: 1,2,3,4,5

If I pass one value it works fine, when I pass more than one value I get an error: Conversion failed when converting the varchar to int.

I have tried to use CONVERT(int,@sectionID) and CAST (@sectionID As int) but I got the same error. Is there another way of doing this?

 

[sonya9879]

I think I found an answer to this by creating a function and loop the comma delimited string. Just in case someone else has the same problem in the future, thats a way to sort the problem out .

 

[sonya9879]

here is a function that does the looping:

ALTER FUNCTION [dbo].[listToTable](@list as varchar(8000), @delim as
varchar(10))
RETURNS @listTable table(
Position int,
Value varchar(8000)
)
AS
BEGIN
declare @myPos int
set @myPos = 1

while charindex(@delim, @list) > 0
begin
insert into @listTable(Position, Value)
values(@myPos, left(@list, charindex(@delim, @list) -
1))

set @myPos = @myPos + 1
if charindex(@delim, @list) = len(@list)
insert into @listTable(Position, Value)
values(@myPos, '')
set @list = right(@list, len(@list) - charindex(@delim,
@list))
end

if len(@list) > 0
insert into @listTable(Position, Value)
values(@myPos, @list)

RETURN
END

And I will call it like:

SELECT *
FROM testtable
WHERE (sectionID IN (
Select value from dbo.listToTable(@sectionID, ',')
))

 

[DocuMan]

create procedure test
(
@word varchar(10),
@sectionID varchar(max)
)
as
begin
EXECUTE ( 'SELECT * FROM [testtable] WHERE sectionID IN ( ' + @sectionID + ')' )
end