Temp variable table data type update 1

[Shal2]

Hi All,

How do I update a temp variable of table data type in SQL stored procedure. What is the syntax for that.

Thank you,
Shal

 

[TheJon]

You treat it exactly like a table:
[tt]
declare @myTable table(col1 varchar(2), col2 varchar(2))
insert into @myTable values ('12', '23')
insert into @myTable values ('45', '67')
select * from @myTable
update @myTable set col2 = '34' where col1 = '12'
select * from @myTable
delete from @myTable where col1 = '12'
select * from @myTable
etc..
[/tt]

|_ |_  _  | _  _
|, | )(/, |(_)| )
          '

 

[Shal2]

Thankyou TheJon,

I am trying to convert our stored procedures to use temp variables instead of temp tables (since MS reporting services does not support temp tables I guess).

Here is my first attempt and I am getting the error must declare @Summary, even though I have declared it, this is only after update statement, if I just try the code until marked line, it does not give any syntax error.

Please take a look at this.

--------------------------------------
CREATE PROCEDURE MSRS_OnHand
@Season varchar(35)

AS

declare @summary TABLE
( prodgroup varchar(35),
barcode varchar(15),
style varchar(15),
description varchar(35),
colour varchar(20),
siz varchar(15),
qty int,
Cost money,
retail money,
margin float,
retailvalue money,
costvalue money
)

insert into @summary
select
g.prodgroup,
q.Barcode,
g.Style,
g.Description,
b.Colour,
b.siz as Size,
SUM(q.quantity),
ISNULL(p.LastCost,ISNULL(p.OriginalCost,0)) as Cost,
p.Retail1 ,
0 as Margin,
0 as TotRetailValue,
0 as TotCostvalue
from
quantity q inner join branch br on
q.branch=br.name inner join barcode b on
q.barcode = b.barcode inner join garment g on
b.style = g.style inner join pricing p on
g.style = p.style and
b.pricepoint = p.pricepoint
where
p.region = 'New Zealand' and
br.retailbranch='Y' and
q.branch <> 'CENTRAL DISTRIBUTION CENTRE' and
q.quantity > 0 and
g.season = @Season
GROUP BY g.prodgroup,
q.Barcode,
g.Style,
g.Description,
b.Colour,
b.siz ,
p.retail1,
p.Lastcost,
p.OriginalCost
--------------------------------------error after this

UPDATE @summary
SET qty = @summary.qty+cdc.OnHandCDC
FROM
(SELECT
Barcode,
Sum(Quantity) AS OnHandCDC
FROM
QUANTITY
WHERE
Branch = 'CENTRAL DISTRIBUTION CENTRE'
GROUP BY Barcode) as cdc
WHERE @summary.barcode = cdc.barcode

UPDATE @summary
SET qty = @summary.qty+it.InTransit
FROM
(SELECT
td.Barcode,
Sum(IsNull(td.Quantity,0)) InTransit
FROM
TranHeader th INNER JOIN TranDetail td on
td.Trandate=th.Trandate and
td.Documentnum=th.Documentnum and
td.Type=th.Type
WHERE
th.Type='TFO' and
th.TransferReceived is null
GROUP BY
td.Barcode) as it
WHERE @summary.barcode = it.barcode

select * from @summary
order by prodgroup,barcode

GO
---------------------------------

 

[donutman]

It would sure make it easier to answer, if you told us what the syntax error message said. Is it really failing on the word "Update"?
-Karl

 

[Shal2]

Hello Karl,

The error message is " must declare variable @summary" and it is not giving the error line, but my guess is the SET of the update statement or the where clause of the update statment.

In the code below :
I changed the:
------------------------------------------
SET @summary.qty = @summary.qty+cdc.OnHandCDC
to
SET qty = @summary.qty+cdc.OnHandCDC
----------------------------------------
because I was getting error near '.', so if I change that everywhere I won't get syntax error, but in the where clause it will give ambiguity column name, so I will have to use

WHERE @summary.barcode = cdc.barcode

which will give the error "must declare @summary varible" even though I have declared it.
-------------------------------------

UPDATE @summary
SET qty = @summary.qty+cdc.OnHandCDC
FROM
(SELECT
Barcode,
Sum(Quantity) AS OnHandCDC
FROM
QUANTITY
WHERE
Branch = 'CENTRAL DISTRIBUTION CENTRE'
GROUP BY Barcode) as cdc
WHERE @summary.barcode = cdc.barcode
-----------------------------------------------

Thank you
Shal

 

[NathanGriffiths]

Try this, uses JOIN and table alias, might resolve you problem;

UPDATE s
  SET s.qty = s.qty + cdc.OnHandCDC
FROM
  @summary s
  JOIN 
  (SELECT
            Barcode,
            Sum(Quantity) AS OnHandCDC
        FROM
            QUANTITY
        WHERE
            Branch = 'CENTRAL DISTRIBUTION CENTRE'
        GROUP BY Barcode) as cdc
  ON s.barcode = cdc.barcode

Nathan


Want to get a good response to your question? Read this FAQ! -> faq183-874

 

[Shal2]

Thank you Nathan,
Your it worked.

Thanks to all of you who replied.

Regards,
Shal