dynamic column without building sql string

[GISql]

Hi,

I have a parameter called @version which can be 1, 2, 3, 4, 5

I have 5 columns in my table, price_1, price_2, etc.
I have a stored procedure to get a price which has a parameter called @version

So depending on the value, I have to get price_1, _2, etc.

Right now I build an SQL string and using exec to execute this string.

... PRICE_' + @VERSION + ' as SALES_PRICE ...

Is there an easier way to do this? Preferably without building this SQL string, since this is a pain in the ass debugging.

Thanks you in advance,

G.I.

 

[r937]

as far as building the code in a stored proc is concerned, your method is fine

having 5 prices on the same row isn't -- consider normalizing that table structure

r937.com | rudy.ca

 

[GISql]

Why is this not normal? Ok, I can put these in a seperate table, but that's not the problem here.

For the article different prices are used in different situations. That's why it's possible to have 5 different prices. The reason why this table is this way does not matter in this case, since the table can not be changed anymore now.

So I just want a solution to my problem, but thanks for your advice.

 

[r937]

as far as building the code in a stored proc is concerned, your method is fine



r937.com | rudy.ca

 

[bborissov]

If you don't want dynamic SQL and have ONLY 5 prices you could do something like that:

SELECT ....
       CASE @Version WHEN 1 THEN Price_1
                     WHEN 2 THEN Price_2
                     WHEN 3 THEN Price_3
                     WHEN 4 THEN Price_4
                     WHEN 5 THEN Price_5 END AS Price
...

Not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[GISql]

Of cource, that makes sense. Just one question: Is this faster than building a string in SQL?

 

[bborissov]

Hmmm,
It supposed to be faster, BUT I am not so sure
If you often use this query it sould be fatser, but if not I doubt there would be significant speed difference.
You could test it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.