CODEWITHSUNDEEP
sqlsql-server-2008
Richard77
Richard77

Reputation: 21641

what the true nature of @ in Transct-SQL

I reading some old ScottGu's blogs on Linq2SQL. Now I'm doing the SPROC part. I'd like to know what's the exact meaning of @variable.

See this from ScottGu's Blog

ALTER PROCEDURE dbo.GetCustomersDetails
(
  @customerID nchar(5),
  @companyName nvarchar(40) output
)
AS
SELECT @companyName = CompanyName FROM Customers
WHERE CustomerID = @customerID

SELECT *
FROM Orders
WHERE CustomerID = @customerID
ORDER BY OrderID

I'm kind of lost as, so far, I've though of anything preceded by a '@' as a placeholder for user input. But, in the example above, it looks like '@companyName' is used as a regular variable like in C# for instance (SELECT @companyName = ...). But, @companyName is not known yet.

So, what the true nature a something preceded by a '@' like above? a vriable? a simple placeholder to accommodate user entered value?

Thanks for helping

Upvotes: 0

Views: 94

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171431

It is simply a variable.

Remember that stored procedures can have input and output parameters. @companyName in that case is a variable holding the value that will be output when the procedure GetCustomersDetails is called (note the output after the parameter declaration).

This procedure is also returning a result set in addition to the output parameter. You also have the option of setting a return code if you wish, so there are at least three ways of returning data from a stored procedure that can all be used at the same time: output parameters, result sets, and return codes.

Upvotes: 7

KM.
KM.

Reputation: 103607

see the inline comments:

ALTER PROCEDURE dbo.GetCustomersDetails
(
  @customerID nchar(5),            --input parameter to stored procedure
  @companyName nvarchar(40) output --output parameter to stored procedure, can be changed by the procedure and the value retrieved by the caller
)
AS
SELECT @companyName = CompanyName FROM Customers  --set the output parameter as the last row from this query
WHERE CustomerID = @customerID  --use the input parameter to filter the query

SELECT *
FROM Orders
WHERE CustomerID = @customerID  --filter this query on the input parameter 
ORDER BY OrderID

You can declare local variables, they are not just parameters of stored procedures:

DECLARE @localVariable int     --integer local variable
       ,@Another       char(1) --multiple on one DECLARE

usually @@.... are system values, like @@ROWCOUNT and @@SPID, but you can do crazy things like: 

DECLARE @@@@wtf        int   --this is valid, and works

Upvotes: 1

Related Questions