How to return more than 1 value from a store procedure
we have a store procedure that returns 1 of 2 values, 1=true, 0=not true bases on the script:
Question:
How can we incorporate the above condition within the sp script (can we embed or nest SELECT maybe?)
Also, in can we include in the sp two 'outputs':
That way @Param2 will return 1 or 2 and @Param3 return 3 or 4.
Thank you in advance
SELECT CAST(CASE WHEN EXISTS(select * from table where column1='value' and @variable=1)
THEN 1 ELSE 0 END AS BIT)Question:
How can we incorporate the above condition within the sp script (can we embed or nest SELECT maybe?)
Also, in can we include in the sp two 'outputs':
@Param2 date OUTPUT,
@Param3 date OUTPUTThat way @Param2 will return 1 or 2 and @Param3 return 3 or 4.
Thank you in advance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Vitor Montalvão
Why the need of having TOP 1 in a EXISTS clause?
ASKER
Wouldn't that return just one value?
Either 1,2,3 or 4?
Also, what "select top 1 1" means? What does it do?
Either 1,2,3 or 4?
Also, what "select top 1 1" means? What does it do?
Wouldn't that return just one value?Yes, but if you see your requirement you should only need one, right? What '2' means?
Either 1,2,3 or 4?
ASKER
Actually, the SP needs to return 2 values:
@Param2 will return 1 or 2
@Param3 return 3 or 4
Can this be done?
@Param2 will return 1 or 2
@Param3 return 3 or 4
Can this be done?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
NOTE: I leaved a 0 only for cases that neither option is valid: column1<>'value' and @variable<>1
ASKER
Nice. Yes, makes sense your script and all in one also. We are working with it right now.
I also think it makes more sense having it all in one variable since you're not repeating values. You know exactly what 1,2,3 and 4 means and they should be exclusively, right?
ASKER
1 & 2 means true or not true for data stored in the table (where column1='value' and @variable=1)
3 & 4 means true or not true for data stored in the variable (where @variable=1)
3 & 4 means true or not true for data stored in the variable (where @variable=1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What exactly you do use these variables values ?
ASKER
Vitco,
Rawan,
@Param2 - is to identify is the row exist and the value sent to sp for @variable is 1
@Param3 - used to evaluate the value sent to sp for @variable is either 1 or 2
So @Param2 will return 1 or 2, and @Param3 will return 3 or 4.
That's an even better script with same results, Thanx!
Rawan,
@Param2 - is to identify is the row exist and the value sent to sp for @variable is 1
@Param3 - used to evaluate the value sent to sp for @variable is either 1 or 2
So @Param2 will return 1 or 2, and @Param3 will return 3 or 4.
That's an even better script with same results, Thanx!Yes, less access to the database :)
If you have this issue solved please close this question by accepting the comment that has the solution.
Cheers
ASKER
Right! (forgot about that)
ASKER
No, wait, the last of the question,
Can we declare outputs in a store procedure as many as we can? Any considerations to this?
That is:
@Param1 date OUTPUT,
@Param2 date OUTPUT,
@Param3 date OUTPUT,
@Param4 date OUTPUT
etc.
Can we declare outputs in a store procedure as many as we can? Any considerations to this?
That is:
@Param1 date OUTPUT,
@Param2 date OUTPUT,
@Param3 date OUTPUT,
@Param4 date OUTPUT
etc.
Yes. The limit is 2100 parameters so I guess you won't have any problems with that :)
ASKER
Thanx! one last thing, to declared more than one output, the below is the correct format?
... just add a comma and keep adding OUTPUTs?
CREATE PROC spname
(@var1 VARCHAR(100),
@DocType INT,
@DocDate date,
@Param1 VARCHAR(100) OUTPUT,
@Param2 date OUTPUT)
AS select col1, col1 from tablename where @DocType=1 and col1=@var1 and @DocDate<=mxDate... just add a comma and keep adding OUTPUTs?
Correct.
You can see all those kind of info and examples from MSDN article.
You can see all those kind of info and examples from MSDN article.
ASKER
Like always, thanx for all your help!!