Solved

Variable array or list?

Posted on 2011-02-24
4
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

734 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