CODEWITHSUNDEEP
sql-servervariablesstored-procedures
Steve
Steve

Reputation: 3

Multiple variables in SQL Server stored procedure

I'm stuck on assigned variable values in this stored procedure.

@EntityID int,
@Title1 varchar(10) OUTPUT,
@Title2 varchar(10) OUTPUT
AS
  Declare @T1 varchar(10)
  Declare @T2 varchar(10)
BEGIN

    SELECT     
       dbo.Entity.EntityID, dbo.Types.TypeName AS Title1, Types_1.TypeName AS Title2
    FROM         
       dbo.Entity 
    LEFT OUTER JOIN
       dbo.Types AS Types_1 ON dbo.Entity.Title2 = Types_1.TypeID 
                            AND dbo.Entity.Title2 = Types_1.TypeID 
    LEFT OUTER JOIN
       dbo.Types ON dbo.Entity.Title1 = dbo.Types.TypeID
    WHERE     
       (dbo.Entity.EntityID = @EntityID)
END

I'm trying to return the values of Title1 and Title2. The query works, and returns values, but I need to use them elsewhere.

Upvotes: 0

Views: 17965

Answers (2)

Tobias J
Tobias J

Reputation: 22813

You need to do two things:

First, set the values of those parameters somewhere in the PROC, using SELECT or SET. As @Andomar mentioned this can be done by changing your SELECT statement like this (in your existing code you are returning the values in a column with the same name but that won't actually set the parameters):

SELECT @Title1 = dbo.Types.TypeName, @Title2 = Types_1.TypeName

Then you need to capture those values in your calling program. I'm not sure whether you're calling this stored proc from another bit of SQL code or from code such as ADO.NET. If from SQL, you'll need to first declare the output parameters and then call the proc like this:

DECLARE @Title1 VARCHAR(10), @Title2 VARCHAR(10)
EXEC MyProc @Title1=@Title1 OUTPUT, @Title2=@Title2 OUTPUT

If you're calling from ADO.NET, you need to set the ParameterDirection as Output or InputOutput, call the proc with the parameter, and then read the value of the parameter afterwards.

Upvotes: 1

Andomar
Andomar

Reputation: 238048

SELECT     @Title1 = dbo.Types.TypeName
,          @Title2 = Types_1.TypeName
...

Upvotes: 1

Related Questions