passing multiple numeric values as a parameter

[dwhdwh]

I have a requirement where I want to pass multiple key values to select data from a table as below:

CREATE PROCEDURE a (@jobno varchar(100)) AS
select * from TabA where JobNo in (@jobno)

I cannot however pass in more than 1 key as it fails:
exec a '8002,8003'
Syntax error converting the varchar value '8002,8003' to a column of data type int.

How can I pass in multiple numeric values to be used in an 'IN' statement?

Thanks,
Dave.

 

[JayKusch]

What is the data type of JobNo that is in the table?


Thanks

J. Kusch

 

[dwhdwh]

It's an integer field.

 

[cheyney]

try dynamic tsql:


CREATE PROCEDURE a (@jobno varchar(100)) AS
DECLARE @execstring varchar(200)

SET @execstring = 'select * from TabA where JobNo in (' + @jobno + ')'

EXEC(@execstring)


cheyney

 

[dwhdwh]

It almost defeats having a stored proc. Are you saying it can't be done or is this just a workaround?

Thanks for the suggestion anyway, maybe I'll go with that.

 

[tokuzumi]

You may have to use the "WHILE" function. Looking at SQL online, here is the basic syntax.

WHILE Boolean_expression
{ sql_statement | statement_block }
[ BREAK ]
{ sql_statement | statement_block }
[ CONTINUE ]

Not sure if this is going to be what you will need. I'm running into the same issue as you, and I am looking into this. I'll reply if I come up with anything.

 

[swampBoogie]

http://www.sqlteam.com/item.asp?ItemID=11499

 

[KraGiE]

GEEZ! how lame are you guys being? heh

EXECUTE ('SELECT * FROM TABLE WHERE FIELD IN ' + @Param + ')

Looping works, but will still need that execute.

.NET Developer/Contracting Consultant.
<a href=&quot;

http://www.inklass.com&quot;>InKlaSs.com</a>

 

[dwhdwh]

As I mentioned before, I was trying to come up with a solution that uses static SQL for performance reasons. I'm just surprised that there isn't a solution to support. The previous link has some advantages of using static, but could be costly as it needs to insert records.

 

[osjohnm]

Hi

Have a look at the following thread and the url links posted there.

thread183-447149

Hope this helps

John

 

[dwhdwh]

Many thanks to all those that took the time to respond. I have copied how I am using it for any others uing SQL Server '97 to use it.

CREATE PROCEDURE BuildList
( @Array varchar(1000))
AS
begin
declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert #IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

GO

- - - - - - - - - - - - - - -

CREATE PROCEDURE a (@jobno varchar(100)) AS

Create table #IntTable (Item int)

exec BuildList @JobNo

select * from ManagementSummary where JobNo in (select * from #IntTable)

drop table #IntTable
GO

- - - - - - - - - - - - - - - -

declare @jn varchar(50)
set @jn = '8002,10103,51100,51102'
exec a @jn

 

[nsampson1]

Hi dwhdhw

instead of using

where JobNo in (@jobno)

in your stored proc, try using:

where charindex(jobno,@jobno) > 0

that should work


Neil

 

[dwhdwh]

Your right, with a slight modification to support numeric id's it will work (as shown below). Not sure if this method is more efficient than the solution I used as with the conversions SQL would not be able to use index, however for small tables a good alternative.
Thanks.

select * from ManagementSummary where charindex( rtrim(convert(char(5),jobno))+',' ,@jobno) > 0

exec a '836,876,887,2039,' -- need extra ',' at end