Assigning Values to Variables in Dynamic Query

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Assigning Values to Variables in Dynamic Query

Author

Topic

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 10:30:44

I have a stored procedure with a large amount of code that I desperately need help with. I have several variables that I would like to have values assigned to in the SELECT portion of the query (to build a dynamic query based on the values). Here is an example of what it is I'm tying to do.

DECLARE @Code Int, @Weight Float

SELECT A.key, B.plant, C.cmd

IF (A.key = '1')
BEGIN
SET @Code = 1
SET @Weight = 20
END

IF (B.plant = 'K')
BEGIN
SET @Code = 2
END
ELSE
SET @Weight = 19
END

FROM table A, table B, table c

WHERE <where clause>

Now this isn't the actual code (the actual code is probably to long to post here), but it is an example of what I'm trying to do.

The problem is when I attempt to compile the code I get an error on the FROM line of the code. I can't figure out why I'm getting this error, but I think I'm not assigning the values to the variable correctly or I just don't understand how to go about assignnig more that one value to more than one variable in SQL.

Any help is greatly appreciated. Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 10:37:46

why are you trying to set variables to literals in a select statment?

IF (A.key = '1')
BEGIN
SET @Code = 1
SET @Weight = 20
END

IF (B.plant = 'K')
BEGIN
SET @Code = 2
END
ELSE
begin
SET @Weight = 19
END

SELECT A.key, B.plant, C.cmd
FROM table A, table B, table c
WHERE <where clause>

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 10:46:51

This is the deal with this monsterous thing...the code that I am dealing with is for a report. The problem is, is that the code (being used in a view for one report and a stored procedure for another) was grossly inefficient (the report that used the view would take about 15 minutes to return data). I've been handed the nice job of optimizing the code. So what I've done is take what I could from the stored procedure and view and create a new stored procedure for a particular report. Now the original view used CASE WHEN statements, and used the same statements about 20 times. I didn't like that so I pushed everything into IF ELSE statements.

The @Code, and @Weights are declared variables that should be populated depending on the IF ELSE statements.

Am I going about this the wrong way? I have thougt about creating a function that contains all of the IF ELSE statements and then doing something like...

DELCARE @Code INT

SELECT a.key, b.plant, c.cmd, @Code = <function return value>

FROM <tables> WHERE <something>.

I'm not sure if this would help though. I haven't been doing SQL long (if you couldn't tell)...done a lot of java and C++ though...figured I could make it work?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 10:58:25

java and c++ are procedural languages. sql is a different concept - set based.
Functions are usually not very efficient but may simplify code.

You should change the view based report to use a sp - all interface should be via sp's.

You can set the variables as I showed above but not in the same statement that returns data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 14:09:38

One last question. I have converted the code that I have from using IF ELSE statements to using CASE WHEN statements...for example:

SELECT <list of items>, Code = CASE

WHEN <something> THEN <value for code>

etc., etc.

My question is: Can I use IF ELSE statements within the CASE WHEN statements? For example is it a good idea to do the following:

SELECT <list of items>, Code = CASE

WHEN (A.key = 'R')
IF(B.plant = 'K')
BEGIN
THEN 4
END

ELSE
THEN 1

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 14:16:12

Try it - no you can't.

an if is a control of flow statement and so invalid within a select.

SELECT <list of items>,
Code = CASE WHEN A.key = 'R'
then case when B.plant = 'K'
THEN 4
ELSE
1
end
else ?
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 14:44:53

I really appreciate your help on this. I have used your suggestions from above to try and optimize the code and I think I'm getting a lot closer to getting it done. This is a snippet of what I have so far:

WHEN(SUBSTRING(A.in_item_key, 3, 6) = 'ADJUST'
AND A.in_tran_type = 'R' AND A.in_tran_ordtp = 'B')
THEN (-1 * A.in_tran_qty)

--Assign Code 2 if I and M are main product
WHEN(A.in_tran_type = 'I' AND A.in_tran_ordtp = 'M'
AND C.in_comcd_key IN ('BDL','LVS','STR')
AND G.pm_shop_d_OvrYld > 0)
THEN CASE WHEN(C.uom_key = 'LB')
THEN A.in_tran_qty / G.pm_shop_d_OvrYld * 100
ELSE --UOM != LB
(A.in_tran_qty / G.pm_shop_d_OvrYld * 100 ) * F.en_uomcv_fctr

WHEN(G.pm_shop_d_OvrYld = 0 AND G.pm_shop_d_FinYld > 0 AND C.uom_key = 'LB')
THEN = A.in_tran_qty / G.pm_shop_d_FinYld * 100

For some reason it doesn't like the last else in the statement. Any suggestions?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 15:00:08

WHEN(SUBSTRING(A.in_item_key, 3, 6) = 'ADJUST'
AND A.in_tran_type = 'R' AND A.in_tran_ordtp = 'B')
THEN (-1 * A.in_tran_qty)
--Assign Code 2 if I and M are main product
WHEN(A.in_tran_type = 'I' AND A.in_tran_ordtp = 'M'
AND C.in_comcd_key IN ('BDL','LVS','STR')
AND G.pm_shop_d_OvrYld > 0)
THEN CASE WHEN(C.uom_key = 'LB')
THEN A.in_tran_qty / G.pm_shop_d_OvrYld * 100
ELSE --UOM != LB
(A.in_tran_qty / G.pm_shop_d_OvrYld * 100 ) * F.en_uomcv_fctr
end
WHEN(G.pm_shop_d_OvrYld = 0 AND G.pm_shop_d_FinYld > 0 AND C.uom_key = 'LB')
THEN = A.in_tran_qty / G.pm_shop_d_FinYld * 100
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 15:03:30

Thank you so much...I'll keep right on trucking now that I "think" I've got it figured out. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources