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 |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2002-06-24 : 10:09:38
|
| Hello all. I am very new to writing cursors and using results stored within them for my own personal gain.Here is what I have managed to screw up today. I have a cursor to store Question ID's. This cursor builds a list of ID's for example: 77,78,79,80,81,82.I am storing these ID's in a variable called vchIDList which is a varchar. I want to use vchIDList in a Where clause but I want to use it like this: Where intQuestionID IN (@vchIDList). (I left all the proper syntax stuff off.)My problem is that vchIDList is a varchar, which means it puts those nice little single quotes (') around the string. So SQL is reading: Where intQuestionID IN ('77,78,79,80,81,82'). Well, SQL is having a tough time converting this string to an integer.Is there a way to strip the single quotes off of a string? Or does anyone have a better way to tackle this? I am considering nesting this cursor inside of another one, but I thought I would check with you all before I do that!Thanks in advance!Adrian Miller |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-24 : 10:24:55
|
| Uh...I think you are going about this all wrong. Relational Databases operate on sets of data. You are attacking this problem like you are working with a 4GL programming language. I don't think you need a cursor at all to do this.Post some more about what you are working with. Post some create table statements, some sample data (can be made-up, don't expose your private data) and the results you are trying to achieve. We can show you a way to get where you are going that will be much simpler and far outperform your iterative cursor-based methods.<O> |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 10:26:12
|
| There is a better way, almost certainly. With more detail provided we can construct a set-based solution and do away with your cursors.Provide your DDL ( the CREATE TABLE scripts for the relevant tables ) and a description of what you're trying to do and we'll take it from there.Jonathan Boott, MCDBA |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-24 : 10:47:55
|
You sayquote:
...I have a cursor to store Question ID's. This cursor builds a list of ID's for example: 77,78,79,80,81,82.
What is the SQL that heads up this cursor? A set based approach would be a lot faster and ease up your problemsOr you could...SELECT TAB1.*, Q.Description FROM(Your full SQL that relates to the cursor) TAB1join QUESTION Q1 on Q1.Q_ID = TAB1.Q_IDThat assumes that you have a question table called QUESTION and all you want is to list the question descriptions via my assumed ID col Q_ID.Post some DDL for a more accurate situation.Dan |
|
|
|
|
|
|
|
|
Painted myself into a corner