Declaring A Variable

Question

I am using the SSMS to  write a query which will combine the tfield from two tables. One table may not have an associated record. Using the Query Builder in SSMS my query looks like this:

SELECT

 

tblBooks.ID, tblBooks.Collection, tblBooks.Title, tblBooks.Authors, tblBooks.Series, tblBooks.Issued, tblBooks.Price, tblBooks.ISBN, tblBooks.Category,

tblBooks

.Condition, tblBooks.Type, tblBooks.Signed, tblBooks.BookLoanStatus, tblBooks.Comments, tblOnLoan.LoanStatus, tblOnLoan.CollectionID,

tblOnLoan

.Collection AS Expr1, tblOnLoan.LoanID, tblOnLoan.Title AS Expr2, tblOnLoan.LoanDate, tblOnLoan.LoanTo, tblOnLoan.PhoneNumber,

tblOnLoan

.ReturnDate

FROM

 

tblBooks INNER JOIN

tblOnLoan

ON tblBooks.ID = tblOnLoan.CollectionID

WHERE

 

(tblBooks.ID = @ID)

When I try to execute the query in the Query Builder  I get the error:

Msg 137, Level 15, State 2, Line 7

Must declare the scalar variable "@ID".

I understand that I have to declare the variable @ID but I don't know how.  Can anyone help?

---

sirmilt

Answer 1

Hello sirmilt,

Simply with a DECLARE command at the beginning of your statement:

DECLARE @ID int;
SET @ID = 1;

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

Better to use SELECT than SET

DECLARE @ID int;
SELECT @ID = 1;

http://vyaskn.tripod.com/differences_between_set_and_select.htm

---

Ali Hamdar (alihamdar.com - www.ids.com.lb)