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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-20 : 09:33:15
|
| Vanessa writes "I am on an NT Workstation, running SQL Server 7 - not sure about sp's.I am writing a stored procedure that includes a cursor that is defined like "SELECT column FROM table WHERE someID IN (@var)". @var is declared as a varchar. The problem is, @var may sometimes be one value - and the cursor works fine. If it's a CSV tho ("4,8,9"), I first get an error about converting @var to an Integer (SomeID is an Integer). I modified the query to be "...WHERE CAST(someID as varchar(10)) IN (@var)" and it no longer gives me an error, but any CSVs return no records when they should return records. The books online have info about what you CANT do in a cursor, and the "IN" clause is not listed so I am hoping that somehow this is possible. Please help. TIA-Vanessa" |
|
|
Vanessa
Starting Member
4 Posts |
Posted - 2001-12-20 : 10:14:55
|
quote:
Please read our articles on dynamic SQL. I'd start in the FAQ and then search the site on dynamic SQL.
Bill, I read thru the dynamic SQL articles - and found the Intro to Dynamic SQL Part 2 which talked about using INs. It suggested to build the entire sql statement into a variable and then using EXEC(@SQL). I tried this with my cursor:CURSOR FORWARD_ONLY STATIC FOR @SQL;But this just gives me errors. Can you give me any more concrete information about using an IN in a cursor? Thanx |
 |
|
|
btrimpop
Posting Yak Master
214 Posts |
Posted - 2001-12-20 : 16:53:36
|
| You need to include the entire cursor declaration in your variable:set @sql = 'declare yourcursor cursor for select ... etc etc etc'exec(@sql)open yourcursorAlso, using this approach you can't embed variables in the sql string like 'declare yourcursor cursor for select column from table where column = @var'. You have to embed the value that is in @var into the string like this.Assuming @var is a character type otherwise you need to do the appropriate conversion:set @sql = 'declare yourcursor cursor for select column from table where column = ' + @varHope this helps."In theory there is no difference between theory and practice. But in practice there is!" |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2001-12-20 : 17:19:42
|
| If you can find a way to do what you want without cursors, I strongly recommend it.If you can't though, rather than putting your entire cursor code into dynamic sql, I would use the dynamic sql to populate a temp table, then run your cursor though that.Damian |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2001-12-20 : 17:44:10
|
| The reason it is returning 0 records is because it is seeing @var as a single string '4,8,9' Which obviously doesn't match any of the someID values.You could write a routine do some string manipulations (using the , as the delimiter) parse out the individual numbers and convert each to an int. HTH-Chad |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2001-12-20 : 17:55:38
|
That sounds pretty ugly Chad.To do it with dynamic sql, declare @var varchar(100)declare @sql varchar(1000)SELECT @var = '4,5,6'SELECT @SQL = 'SELECT column FROM table WHERE someID IN (' + @var + ')'Exec(@SQL)Now, if you want to run a (uuuuggghhhhh) cursor with it, insert the results of that Exec into a temp table, then open your cursor on the temp table.Damian |
|
|
|
Vanessa
Starting Member
4 Posts |
Posted - 2001-12-21 : 07:26:21
|
| Thank you all for your suggestions. I have found that working with a temp table has been the best solution. I appreciate the help :)-Vanessa |
|
|
|
|
|
|
|
|
Using