Microsoft SQL Server 2008 script - How to set local variables to a result from a select statement

I'm a little confused on how (if it's at all possible) allow you to set a local variable from a resu开发者_StackOverflow社区lt of an SQL return statement. I know the SQL statement should only return one value, because currently I'm just testing my database with various inputs and the type it should return is an integer. I know in PL/SQL, I would just essentially do this: select buildID into locvar from loc where...

Any help would be greatly appreciated.

Thanks.

---

If you have a stored procedure with the return statement in it, you can execute the stored procedure like so to get the return code into a local variable

Declare localvariable int

Execute localvariable = [database].[dbo].[storedprocedure]

Whatever the return statement in the sproc returns should populate into the variable. I have never tried it in a statement block, not sure if it is possible. A few minutes of research didn't turn anything up. If nobody else comes in with an answer, I will do more research tomorrow.

Edited to ask, are you just trying to learn how to set a variable? There are many ways. Very similar to the example you gave, you just don't really use return in that instance.

---

Ah, I just figured it out. It turns the way I tried doing previously which was

DECLARE @test int;

SELECT @test=[buildingId] FROM Location WHERE postalCode='M6N 1K5';

actually was the correct syntax, it was my conversion to a varchar that was causing an issue. Anyways, thanks for the help Sean I hope my answer benefits you as it benefited me.