Hi all,
I have a stored procedure which requests some inputs from the user.
One of my variables needs to be a list of numbers which I then will be using to pull records from a table which match with the variables. I am expecting the user to enter into @MyList something like 1,5,17,22,25,34 (basically a random set of numbers with a delimiter).
i.e. CREATE PROCEDURE sp_MySP @MyList
SELECT * FROM MyTable WHERE MyField IN @MyList
MyField is an INT field. I've spend too many hours trying to get this to work so now I asking the experts...is this possible?
What problems am I getting?
The stored proc is the basis of a Crystal report. After the user has entered in the numbers, it doesn't return any rows.
I've tried things like WHERE STR(MyField) IN @MyList, getting the input to be in the format '1', '4', '12' etc (i.e enclosing each value within its own single-quotes). Nothing seems to work.
I'm pretty sure the bottom line problem is that @MyList needs to be an array whereas SQL is expecting it to be a single value. I just want users to enter in their numbers separated by a comma or other delimiter.
Ideas?
Danster
I have a stored procedure which requests some inputs from the user.
One of my variables needs to be a list of numbers which I then will be using to pull records from a table which match with the variables. I am expecting the user to enter into @MyList something like 1,5,17,22,25,34 (basically a random set of numbers with a delimiter).
i.e. CREATE PROCEDURE sp_MySP @MyList
SELECT * FROM MyTable WHERE MyField IN @MyList
MyField is an INT field. I've spend too many hours trying to get this to work so now I asking the experts...is this possible?
What problems am I getting?
The stored proc is the basis of a Crystal report. After the user has entered in the numbers, it doesn't return any rows.
I've tried things like WHERE STR(MyField) IN @MyList, getting the input to be in the format '1', '4', '12' etc (i.e enclosing each value within its own single-quotes). Nothing seems to work.
I'm pretty sure the bottom line problem is that @MyList needs to be an array whereas SQL is expecting it to be a single value. I just want users to enter in their numbers separated by a comma or other delimiter.
Ideas?
Danster