• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Variable array or list?

I have a stored procedure (sp_Lockedget) that returns ONE row with 4 values, a dealID and either a 1 0r 0 for the other 3 columns

DealID     afsLocked     afsServiced     afsPaComplete
1524              0                    0                       1

Is there any way to be in a stored procedure
execute  (sp_Lockedget) and then assign variable values to @Var1, @Var2, @Var3 from afsLocked ,afsServiced,afsPaComplete ??

0
Larry Brister
Asked:
Larry Brister
  • 2
1 Solution
 
dwe761Software EngineerCommented:
You could return them as output parameters

exec sp_Lockedget @Var1 OUTPUT, @Var2 OUTPUT, @Var3 OUTPUT
0
 
SharathData EngineerCommented:
If you do not have control over the SP, you can insert the result into a temp table and assign the values to variables.
declare @temp table(DealID int,afsLocked int,afsServiced int,afsPaComplete int)
declare @Var1 int,@Var2 int,@Var3 int
insert @temp
exec (sp_Lockedget)

select @Var1 = afsLocked,@Var2 = afsServiced,@Var3 = afsPaComplete
  from @temp

Open in new window

0
 
dwe761Software EngineerCommented:
As a more complete answer, here's the example one step further:

Create the proc with code below.  Then call it as:

DECLARE       @Var1 bit ,    @Var2 bit ,   @Var3 bit
SET @MyDealID = 1524
EXEC sp_Lockedget @MyDealID, @Var1 OUTPUT, @Var2 OUTPUT, @Var3 OUTPUT
-- Now you can use these variables in future processing...

You could name the input and output parameters the same but I did it this way to show that they can be named differently inside and outside of the stored proc.
CREATE PROC sp_Lockedget(
   @DealID int, 
   @Var1_OUT bit OUTPUT, 
   @Var2_OUT bit OUTPUT,
   @Var3_OUT bit OUTPUT
)
as 
SET NOCOUNT ON

SELECT 
	@Var1_OUT = afsLocked,
	@Var2_OUT = afsServiced,
	@Var3_OUT = afsPaComplete
FROM Mytable
WHERE DealID = @DealID

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now