must declare scalar variable @where

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 2008 Forums

SSIS and Import/Export (2008)

must declare scalar variable @where

Author

Topic

Dzyner
Starting Member

1 Post

Posted - 2012-06-29 : 10:12:12

Hi all,
I'm trying to export data to a fixed width file through import export wizard and saving it to a SSIS package. I'm getting "must declare scalar variable @where" and must declare scalar variable @what. The code is as follows:

ALTER PROCEDURE [teppermans].[rpt_DMS_CUSTORDS]

@Where Varchar(5) = 'A'

as

DECLARE @What TABLE (ID VARCHAR(50) NOT NULL)

IF @Where = 'F' BEGIN
INSERT INTO @What (ID)
VALUES ('BK'),('BR'),('CH'),('DE'),('DR'),('LA'),('LE'),('MO'),('SB'),('TA'),('UP')
END ELSE IF @Where = 'A' BEGIN
INSERT INTO @What (ID)
VALUES ('AP'),('AV'),('AX'),('BE'),('DW'),('DY'),('FI'),('FR'),('MW'),('PP'),('RA'),('RE'),('ST'),('TV'),('VC'),('WA'),('AC')
END ELSE BEGIN
INSERT INTO @What (ID)
VALUES ('AP'),('AV'),('AX'),('BE'),('DW'),('DY'),('FI'),('FR'),('MW'),('PP'),('RA'),('RE'),('ST'),('TV'),('VC'),('WA'),('BK'),('BR'),('CH'),('DE'),('DR'),('LA'),('LE'),('MO'),('SB'),('TA'),('UP'),('AC')
END
SELECT
tbl1.ProductID MODEL
,tbl1.ShipLocnID LOCN
,tbl1.QtyOrdered QTY
,tbl1.OrderID
,tbl2.ShipToName CUSTOMER
,tbl1.DlvyStatus EstSch
,tbl2.OrderDate
FROM storis.OrderItem tbl1
JOIN (SELECT OrderID,ShipToName,OrderDate FROM storis.Orders) tbl2
ON tbl1.OrderID = tbl2.OrderID
WHERE
tbl1.ProductTypeID IN ('1','4','6')
AND
tbl1.TransCodeID IN ('00','60','63')
AND
tbl1.OrderItemType <> '3'
AND
tbl1.RecStatus <> 'D'
AND
tbl1.SourceID = '01'
and
tbl1.CategoryID IN (SELECT ID FROM @What)

any help please???

Kevin P. Mayea I Assistant Project Manager I Tepperman's I 2595 Ouellette Avenue I Windsor, Ontario N8X 4V8 I 519-969-3063 ext 1480 I 519-982-7269 | kmayea@teppermans.com I www.teppermans.com

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-29 : 15:54:28

ALTER PROCEDURE [teppermans].[rpt_DMS_CUSTORDS]
(
@Where Varchar(5) = 'A'
)
as

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources