CODEWITHSUNDEEP
sqlstored-procedures
Abdullah
Abdullah

Reputation: 249

How to initialize values to two or more variables by calling another procedure using sql server?

say this procedure have to be called

select @className = Name 
from dbo.ClassNames
where id=@classId

select @sectionName = SectionName 
from dbo.ClassSections
where id=@sectionId

select @className as 'Class Name',@sectionName as 'Section Name'

the other procedure is:

declare @className nvarchar(50),
@sectionName nvarchar(50)

EXEC    [dbo].[testAll]
        @regNo=@regNo

so how to assing value to @className and @classSection by calling the above procedure???

Upvotes: 0

Views: 85

Answers (2)

Kim Lage
Kim Lage

Reputation: 117

I think you missed something. You can't actually use the return of the proc like that or assign a variable in another procedure, they are out of context. I would solve this in one of the 2 ways:

  1. Define a View returning the 2 fields you need, that way you could filter the results by @regNo and return its results in another procedure, just like a normal table.
  2. Use the return as a OUTPUT field in the procedure. That could be a table or simple 1 of the fields.

Hope it helps...

Upvotes: 0

Horaciux
Horaciux

Reputation: 6487

create procedures using OUTPUT https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

create procedure spOne (@param1 int OUTPUT, @param2 varchar(20) OUTPUT)

as

select @param1= x from table


create procedure spTwo  (@param1 int, @param2 varchar(20))
as
select x from table where y=@param1

Declare variables

declare @param1 int
declare @param2 varchar(20)

exec procedure using OUTPUT

exec spOne @param1 OUTPUT, @param2 OUTPUT

Now those variables holds values generated inside spOne 

exec spTwo @param1, @param2

Upvotes: 1

Related Questions