CODEWITHSUNDEEP
sql-servert-sqlselectstored-proceduresinsert
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10297

How can I use Select in a Declared variable within a TSQL Stored Procedure?

I've got this code in my TSQL Stored Procedure:

CREATE Procedure [dbo].[priceVarianceTest]
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime 
AS 

DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);
DECLARE @MemberNo VarChar(25) = SELECT MEMBERNO FROM INVOICEDETAIL WHERE UNIT = @Unit;
DECLARE @MemberItemCode VarChar(50) = SELECT ITEMCODE FROM INVOICEDETAIL WHERE UNIT = @Unit;

...but it won't execute, saying:

Msg 156, Level 15, State 1, Procedure priceVarianceTest, Line 9
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure priceVarianceTest, Line 10
Incorrect syntax near the keyword 'SELECT'.

I wanted to then insert these vals like so:

INSERT INTO #temp1 (Unit, MemberNo, MemberItemCode) VALUES (@Unit, MemberNo, MemberItemCode);

...so I also tried this:

INSERT INTO #temp1 (Unit, MemberNo, MemberItemCode) VALUES (@Unit, SELECT MEMBERNO FROM INVOICEDETAIL WHERE UNIT = @Unit, MemberItemCode);

...but this was no more welcome than my previous attempt.

How can I, then, get these values. Is something wrong with my syntax, or my approach?

Upvotes: 0

Views: 45

Answers (1)

gmiley
gmiley

Reputation: 6604

Try SELECT @variable = column:

DECLARE @MemberNo VarChar(25); 
SELECT @MemberNo = MEMBERNO FROM INVOICEDETAIL WHERE UNIT = @Unit;
DECLARE @MemberItemCode VarChar(50); 
SELECT @MemberItemCode = ITEMCODE FROM INVOICEDETAIL WHERE UNIT = @Unit;

Or more simply:

DECLARE @MemberNo VarChar(25); 
DECLARE @MemberItemCode VarChar(50); 
SELECT @MemberNo = MEMBERNO, 
       @MemberItemCode = ITEMCODE 
FROM INVOICEDETAIL WHERE UNIT = @Unit;

Upvotes: 2

Related Questions