Solved

Variable array or list?

Posted on 2011-02-24
4
311 Views
Last Modified: 2012-05-11
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
Comment
Question by:lrbrister
  • 2
4 Comments
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
You could return them as output parameters

exec sp_Lockedget @Var1 OUTPUT, @Var2 OUTPUT, @Var3 OUTPUT
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:lrbrister
Comment Utility
Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now