?
Solved

Variable array or list?

Posted on 2011-02-24
4
Medium Priority
?
317 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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

800 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