SQL Server question...

[JMaster]

I'm writing a SQL query, and I was wondering, is it possible to store a column name in a variable?
The reason is because I need to write a value to a certain column, depending on what that value is.

 

[Argo]

What language are you programming in?

 

[JMaster]

Isn't it just SQL Query Language?

 

[Argo]

Wait, so you're just typing in the query at the command line? Then what's the problem, why can't you just provide the correct column as you type in the query? I don't get it.

 

[Buddha Bart]

you could write a stored procedure, but I think Argo's direction is better. Do this with whatever language you're using.

 

[JMaster]

Originally posted by: Argo
Wait, so you're just typing in the query at the command line? Then what's the problem, why can't you just provide the correct column as you type in the query? I don't get it.

Actually it's a stored procedure. Sorry for the confusion.

 

[calpha]

Yes...in a SP...at least in MS-SQL (or transact SQL) you can store a column name in a variable. The simplest solution would be to build your query string w/ the column in question in a varchar variable and then run an EXEC <variable> w/i the SP.

In the case of what you're talking about----it sounds like it would just be easier for you to build an if else statement looking at the value/column combo and just running an EXEC for each case of the if clause.

As far as doing it in a 3GL....as long as you're not using friggin cursor's you'll be faster in most cases running it in the SP. If the statement that gets "Exec'd" is highly dynamic.....then the benefits of a SP are mostly lost as the best solution would end up being using a "Create Procedure With Recompile"....which when I ever find myself in that case, I just move it out of hte SP b/c there's no use in it being in the SP anymore. Speed difference is neglible, and when my sp's get to that point, I find that they're getting too complex, and need to be reworked anyway.

 

[JMaster]

Originally posted by: calpha
Yes...in a SP...at least in MS-SQL (or transact SQL) you can store a column name in a variable. The simplest solution would be to build your query string w/ the column in question in a varchar variable and then run an EXEC <variable> w/i the SP.

In the case of what you're talking about----it sounds like it would just be easier for you to build an if else statement looking at the value/column combo and just running an EXEC for each case of the if clause.

As far as doing it in a 3GL....as long as you're not using friggin cursor's you'll be faster in most cases running it in the SP. If the statement that gets "Exec'd" is highly dynamic.....then the benefits of a SP are mostly lost as the best solution would end up being using a "Create Procedure With Recompile"....which when I ever find myself in that case, I just move it out of hte SP b/c there's no use in it being in the SP anymore. Speed difference is neglible, and when my sp's get to that point, I find that they're getting too complex, and need to be reworked anyway.

Oh, ok thanks. My scenario is: there are 12 possible columns to write data to (1 for each month of the year), but only 1 of them for each row. I can do 12 different if - else statements, but I was just looking for a way to do 1 INSERT statement using a variable as my column name. I'm going to look into the EXECUTE statement, as well. In terms of execution time, there are THOUSANDS of rows to process, so the EXECUTE statement would probably be faster, no?

 

[calpha]

You can use a variable as your column name in the case you're talking about. Execute will work for that.

I'm going to look into the EXECUTE statement, as well. In terms of execution time, there are THOUSANDS of rows to process, so the EXECUTE statement would probably be faster, no?

As compared to what? Processing it via 3GL? Not enough info to determine----but in most cases---yes.