SP problem with IN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP problem with IN

Author

Topic

jbezanson
Starting Member

35 Posts

Posted - 2007-01-10 : 10:23:01

NOTE: this is not done with any server side code but must be done from the Enterprise Manager or Query Analyser

I have an odd problem with my stored procedure. I want to pass into the procedure (in 1 parameter) a comma delimited list of ID numbers. The ID database field is type INT

I know if the field type were VARCHAR I would do this

...
WHERE ID IN ('1,2,3')

and if it is type INT I would do this

...
WHERE ID IN (1,2,3)

The problem the only way I can pass in the list of ids to the stored procedure is:

myProc '1,2,3'

and the only datatype I can get to accept that is a VARCHAR (@Param1 VARCHAR(255)) but this causing an error because the ID field is INT and it can't convert the @Param1 to INT.

my code is

CREATE PROCEDURE myProc
  @Param1 VARCHAR(255)
AS
  SELECT *
  FROM myTable
  WHERE ID IN (@Param1)

The specific error

Syntax error converting the varchar value '1,2,3' to a column of data type int.

Again ID is type INT and I have no control over this.

Justin Bezanson
www.aspnetguy.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-10 : 10:29:14

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-10 : 10:37:26

One solution is to use dynamic SQL:


CREATE PROCEDURE myProc
  @Param1 VARCHAR(255)
AS

exec ('
  SELECT *
  FROM myTable
  WHERE ID IN ( '+@Param1+' )
')

Another is to load the values in the string into a temp table and use that in your query:


  SELECT *
  FROM myTable
  WHERE ID IN ( select ID from #temp )

There have been various solutions posted on SQLTeam for converting a string to a temp table, so I won't get into how to do that. You can search for it.

CODO ERGO SUM

jbezanson
Starting Member

35 Posts

Posted - 2007-01-10 : 12:45:57

quote:
Originally posted by harsh_athalye

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thanks that post solved my problem

cheers

Justin Bezanson
www.aspnetguy.com

jbezanson
Starting Member

35 Posts

Posted - 2007-06-25 : 12:45:41

LOL this solution has saved my butt a few times now. Many thanks.

Justin Bezanson
www.justinbezanson.com
www.geekdaily.net - web development and technology blog and news
www.offsidegames.com - free online flash games
www.thrufare.com - free proxy website

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources