Variables in Stored Procedure

[bboggessswcg]

Here is what I am trying to do:

I want to run a SELECT statement like below that will return only 1 record and that is the CYATTY field:

SELECT CASATY.CYATTY
FROM ATTYMF INNER JOIN CASATY ON ATTYMF.ATCODE = CASATY.CYATTY
WHERE (CASATY.CMID=@CMID) AND (CASATY.CYPLDF='P') AND (ATTYMF.ATNAME=@ATNAME

I want to save this 1 record in a variable and then run a DELETE query to delete a record in a different table based on this CYATTY number I just stored in the variable.

I hope that makes sense, and somebody can help me, I am pretty new with stored procedures.

 

[nigelrivett]

declare @CYATTY int
SELECT @CYATTY = CASATY.CYATTY
FROM ATTYMF INNER JOIN CASATY ON ATTYMF.ATCODE = CASATY.CYATTY
WHERE (CASATY.CMID=@CMID) AND (CASATY.CYPLDF='P') AND (ATTYMF.ATNAME=@ATNAME)

delete tbl2 where CYATTY = @CYATTY

or you could

delete tbl2
FROM tbl2
INNER JOIN CASATY on tbl2.CYATTY = CASATY.CYATTY
inner join ATTYMF ON ATTYMF.ATCODE = CASATY.CYATTY
WHERE (CASATY.CMID=@CMID) AND (CASATY.CYPLDF='P') AND (ATTYMF.ATNAME=@ATNAME)


======================================
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.