March 23, 2016 at 8:44 am
How do I assign row values to variables? Let’s say I have a table with one column called Values with six values and I want to assign the six values to six different variables.
Declare @d1 int, @d2 int, @d3 int, @d4 int, @d5 int, @d6 int
Values
1
2
3
4
5
6
How do I or can I assign @d1=1, @d2=2, @d3=3 … with a single select statement?
Thanks,
MC
March 23, 2016 at 8:49 am
You can't. Tables are unordered, so the only way to guarantee order is if there is something in the data that you can sort on.
Your explanation shows only a single column, so there is nothing to sort on. So that means that there is no way to guarantee that the values go into the correct variables.
I also struggle to see why you want this. Perhaps you can take a step back and explain the actual problem you are trying to solve. Who knows, perhaps you don't even need the variables.
March 23, 2016 at 9:08 am
Hi Hugo,
Thanks for the feedback. The values represent six working days (days of the month) and are initially stored in a table. This can be sorted. A report is generated using the values to populate six different columns similar to what is shown below based on the day value plus some other calculations.
D1|D2|D3|D4|D5|D6
07|13|77|30|35|60
This is already being used, but a cursor is being used to loop through the table to populate the variables. I am looking for ways to improve/re-write the existing code to avoid using cursors.
Thanks,
MC
March 23, 2016 at 9:50 am
As long as you can guarantee that you return only 1 row you can use select:
SELECT @d1 = D1, @d2 = D2, @d3 = D3
, @d4 = D4, @d5 = D5 , @d6 = D6
FROM TableName
WHERE ....;
March 23, 2016 at 10:05 am
Sure, you can do it, but if you post the code it's extremely likely we here can re-work the entire method being used and make it much better.
create table #values ( value int )
insert into #values values(1),(2),(3),(4),(5),(6)
go
Declare @d1 int, @d2 int, @d3 int, @d4 int, @d5 int, @d6 int
select
@d1 = MAX(case when row_num = 1 then value end),
@d2 = MAX(case when row_num = 2 then value end),
@d3 = MAX(case when row_num = 3 then value end),
@d4 = MAX(case when row_num = 4 then value end),
@d5 = MAX(case when row_num = 5 then value end),
@d6 = MAX(case when row_num = 6 then value end)
from (
select value, row_number() over(order by value) as row_num
from #values
) as values_ordered
select @d1 AS [@d1], @d2 AS [@d2], @d3 AS [@d3], @d4 AS [@d4], @d5 AS [@d5], @d6 AS [@d6]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 23, 2016 at 10:30 am
mceventphoto (3/23/2016)
Hi Hugo,Thanks for the feedback. The values represent six working days (days of the month) and are initially stored in a table. This can be sorted. A report is generated using the values to populate six different columns similar to what is shown below based on the day value plus some other calculations.
D1|D2|D3|D4|D5|D6
07|13|77|30|35|60
This is already being used, but a cursor is being used to loop through the table to populate the variables. I am looking for ways to improve/re-write the existing code to avoid using cursors.
Thanks,
MC
I always try to avoid cross-tabbing on the server. So I would prefer returning this same data in a straight relational format, like this:
Day | Value
D1 | 7
D2 | 13
D3 | 77
D4 | 30
D5 | 35
D6 |60
And I would then offload the formatting to crosstab format to the presentation tier, which is usually much better equipped for that type of task.
The benefit in your case is that you can rip out the cursor, rip out the variables, and rewrite the query that produces the numbers to use a join to the table with the six dates.
If you cannot offload the crosstabbing, I would probably still favor an approach that first creates the relational results as shown above and then crosstabs that to the format you desire. You can google "SQL Server crosstab" to find some ideas for this. Beware that dynamic crosstab queries, like any code that uses dynamic SQL, always come with the risk of SQL injection. In this case the injection potential would be through the dates, because those will be the column names - make sure you control the process that fills those dates, and choose data types that make injection impossible.
March 23, 2016 at 1:11 pm
Thanks Hugo and ScottPletcher for the feedback and suggestions. I will look into "SQL Server Crosstabs" as well.
Here is a similar code that I have to fix that assigns value from a table to variables and uses two cursors to generate a report.
declare
@D1 int,
@D2 int,
@D3 int,
@D4 int,
@D5 int,
@D6 int
select@D1 = 20160206,
@D2 = 20160208,
@D3 = 20160209,
@D4 = 20160210,
@D5 = 20160211,
@D6 = 20160212
begin
Declare @itemCode char(15),
@intIndex int,
@Date1 Int,
@Date2 Int,
@Date3 Int,
@Date4 Int,
@Date5 Int,
@Date6 Int,
@Date Int
set @intIndex = 1
DECLARE C_TmpDate CURSOR FOR
SELECT Distinct AcrossDays FROM tbl_AcrossDays Order By AcrossDays
OPEN C_TmpDate
FETCH Next FROM C_TmpDate INTO @Date
WHILE @@Fetch_Status = 0
Begin
if @intIndex = 1
Begin
Set @Date1 = @Date
End
if @intIndex = 2
Begin
Set @Date2 = @Date
End
If @intIndex = 3
Begin
Set @Date3 = @Date
End
If @intIndex = 4
Begin
Set @Date4 = @Date
End
If @intIndex = 5
Begin
Set @Date5 = @Date
End
If @intIndex = 6
Begin
Set @Date6 = @Date
End
set @intIndex = @intIndex + 1
FETCH Next FROM C_TmpDate INTO @Date
end
CLOSE C_TmpDate
DEALLOCATE C_TmpDate
DECLARE C_Tmp CURSOR FOR
SELECT Distinct ItemCode FROM rpt_needs Where (Day1+Day2+Day3+Day4+Day5+Day6) <> 0 Order By itemcode -- Type = 'Orders' And
OPEN C_Tmp
FETCH Next FROM C_Tmp INTO @itemCode
WHILE @@Fetch_Status = 0
Begin
insert into rpt_needsreportdata
SELECT a.PROd_CAT AS ProdCat,
e.prod_CAT_DESC,
a.user_def_cd AS FLAVORCODE,
c.[Code Description] AS Flavor,
a.COMMODITY_Cd AS SIZECODE,
d.[Code Description] AS Size,
b.ITEMCODE,b.[DESC], b.TYPE,b.day1,
lbsday1 = CASE b.type WHEN 'orders' THEN 0 else day1*ITEM_WEIGHT
END ,
OrdLbsDay1 = CASE b.type WHEN 'orders' THEN day1*ITEM_WEIGHT else 0
END,
DAY2,lbsday2 = CASE b.type WHEN 'orders' THEN 0 else day2*ITEM_WEIGHT
END ,
OrdLbsDay2 = CASE b.type WHEN 'orders' THEN day2*ITEM_WEIGHT else 0
END,
DAY3,lbsday3 = CASE b.type WHEN 'orders' THEN 0 else day3*ITEM_WEIGHT
END ,
OrdLbsDay3 = CASE b.type WHEN 'orders' THEN day3*ITEM_WEIGHT else 0
END,
DAY4,lbsday4 = CASE b.type WHEN 'orders' THEN 0 else day4*ITEM_WEIGHT
END ,
OrdLbsDay4 = CASE b.type WHEN 'orders' THEN day4*ITEM_WEIGHT else 0
END,
DAY5,lbsday5 = CASE b.type WHEN 'orders' THEN 0 else day5*ITEM_WEIGHT
END ,
OrdLbsDay5 = CASE b.type WHEN 'orders' THEN day5*ITEM_WEIGHT else 0
END,
DAY6,lbsday6 = CASE b.type WHEN 'orders' THEN 0 else day6*ITEM_WEIGHT
END ,
OrdLbsDay6 = CASE b.type WHEN 'orders' THEN day6*ITEM_WEIGHT else 0
END,
TOTALQTYORD = CASE b.type WHEN 'ON HAND' THEN 0 else TOTALQTY
END,
TotalLbs = CASE b.type WHEN 'orders' THEN 0 else TOTALQTY*ITEM_WEIGHT
END,
OnHandNeeds = CASE b.type WHEN 'on hand' THEN totalqty else 0
END,
pur_or_mfg,
@Date1 as Day1Date,
@Date2 as Day2Date,
@Date3 as Day3Date,
@Date4 as Day4Date,
@Date5 as Day5Date ,
@Date6 as Day6Date
from IMITMIDX_sql a inner join rpt_needs b
on b.itemcode = a.item_no left outer join rpt_flavor c
on c.code = a.user_def_cd left outer join rpt_bagsize d
on d.code =a.commodity_cd inner join imcatfil_sql e
on e.prod_cat = a.prod_cat and
b.type <> 'production' ---and b.totalqty<>0
Where b.ITEMCODE = @itemCode AND a.Activity_Cd = 'A'
ORDER BY substring(a.PROd_CAT,1,2), a.user_def_cd,
a.COMMODITY_CD, b.ITEMCODE, b.TYPE,
CASE b.type WHEN 'ON HAND' THEN 0 else TOTALQTY end
FETCH Next FROM C_Tmp INTO @itemCode
End
CLOSE C_Tmp
DEALLOCATE C_Tmp
select * from rpt_needsreportdata
END
March 23, 2016 at 2:53 pm
In reviewing the code I posted, I figured out that the second cursor is not needed. I simply replaced the portion of the where clause.
Where b.ITEMCODE = @itemCode
with
Where b.ITEMCODE in (SELECT Distinct ItemCode FROM rpt_needs_RAMS Where (Day1+Day2+Day3+Day4+Day5+Day6) <> 0)
This is probably not the most efficient way, but it eliminates the use of the cursor.
As for assigning the values to variables from the table, the cursor option seems to be faster than using the method suggested by
ScottPletcher. The code below accomplished the task, but it was slower than the cursor.
select
@Date1 = MAX(case when row_num = 1 then AcrossDays end),
@Date2 = MAX(case when row_num = 2 then AcrossDays end),
@Date3 = MAX(case when row_num = 3 then AcrossDays end),
@Date4 = MAX(case when row_num = 4 then AcrossDays end),
@Date5 = MAX(case when row_num = 5 then AcrossDays end),
@Date6 = MAX(case when row_num = 6 then AcrossDays end)
from (
select Distinct AcrossDays, row_number() over(order by AcrossDays) as row_num
from rpt_AcrossDays_RAMS
) as values_ordered
I will continue to review the codes to see if I can make it better.
Thanks all for your help.
MC
March 23, 2016 at 3:00 pm
mceventphoto (3/23/2016)
In reviewing the code I posted, I figured out that the second cursor is not needed. I simply replaced the portion of the where clause.
Where b.ITEMCODE = @itemCode
with
Where b.ITEMCODE in (SELECT Distinct ItemCode FROM rpt_needs_RAMS Where (Day1+Day2+Day3+Day4+Day5+Day6) <> 0)
This is probably not the most efficient way, but it eliminates the use of the cursor.
As for assigning the values to variables from the table, the cursor option seems to be faster than using the method suggested by
ScottPletcher. The code below accomplished the task, but it was slower than the cursor.
select
@Date1 = MAX(case when row_num = 1 then AcrossDays end),
@Date2 = MAX(case when row_num = 2 then AcrossDays end),
@Date3 = MAX(case when row_num = 3 then AcrossDays end),
@Date4 = MAX(case when row_num = 4 then AcrossDays end),
@Date5 = MAX(case when row_num = 5 then AcrossDays end),
@Date6 = MAX(case when row_num = 6 then AcrossDays end)
from (
select Distinct AcrossDays, row_number() over(order by AcrossDays) as row_num
from rpt_AcrossDays_RAMS
) as values_ordered
I will continue to review the codes to see if I can make it better.
Thanks all for your help.
MC
Fascinating about the performance. The original code only read six values, so that should help the new code as well:
from (
select Distinct top (6) AcrossDays, row_number() over(order by AcrossDays) as row_num
from rpt_AcrossDays_RAMS
) as values_ordered
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply