Tempoary Variables in Stored Procedures

[MS Forum Newgroup User]

Hi all,

I'm trying to create a procedure that contains a temp variable that I can
use further in the procedure, so that I can use the result from the first
query in the second query:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[GetDetails]
@ID int,
@IDName varchar(100),
@tempvar varchar(100)

as
begin
Select @IDName AS tempvar from tbl_main where tbl_main.id = @ID

SET @tempvar = tempvar

Select isnull(username,'NA') AS UName, isnull(useremail,'NA') AS UEmail
from tbl_login
where tbl_login.designation = isnull(@tempvar,'NA')

end

[Erland Sommarskog]

First there is no need for a variable. Write the query as:

Select l.username AS UName, isnull(l.useremail,'NA') AS UEmail
from tbl_login l
join tbl_main m ON m.column_name = l.designation
where tbl_login.designation = @ID

I here used m.column_name, as it is not clear to me what is the name of
the column tbl_main. I also dropped the isnull around username, because
of the login does not have a username, something is seriously broken.
That column should not be nullable. (Which probably is true about
useremail as well.)

To assign a variable in a query just do:

SELECT @var = col FROM tbl WHERE keycol = @val

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx