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.
| Author |
Topic |
|
Mortal Wombat
Starting Member
10 Posts |
 Posted - 2011-07-15 : 07:41:05
|
Hi All,I'm working on a stored proc in which one of it's parameters is a Varchar containing a comma delimited list of integer numbers. E.g. '49,51,50'I then wish to use this as part of an 'in' statement within a WHERE clause checking that an integer id exists within the list. Obviously I'm having difficulty doing so due to a 'Conversion failed when converting the varchar value '49,51,50' to data type int' error.A simplified version of my code looks something like this: DECLARE @listOfIds VARCHAR(20)SET @listOfIds = '49,51,50'SELECT Item.nameFROM Item WHERE @listOfIds IS NULL OR Item.id in (@listOfIds) I am simply trying to state that if no list of Ids is provided then show all the items otherwise only show the items that appear in the @listOfIds (Item.id is an INT)Is there anyway I can achieve this without having to use dynamic sql that I'm missing?Anyone's help or advice would be greatly appreciated. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
Mortal Wombat
Starting Member
10 Posts |
Posted - 2011-07-15 : 09:10:09
|
| Thanks Lumbago,Very helpful indeed. As I'm working with legacy code that uses dynamic SQL I'm going to keep it uniform by also using dynamic SQL this time, but the use of the UDF you kindly outlined is something I will use in the future. Cheers ! |
 |
|
|
|
|
|
Varchar parameter to stored proc