Solved

Variable array or list?

Posted on 2011-02-24
4
313 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
ID: 34973127
You could return them as output parameters

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

Expert Comment

by:Sharath
ID: 34973278
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
ID: 34973286
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
ID: 34974079
Thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get the latest status 8 32
what are the unique tables in SQL master database 5 62
RLDC Reporting in Visual studio 11 16
SQL - Use results of SELECT DISTINCT in a JOIN 4 21
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 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