Can stored proc be called in a select statement?
Hi
Can an USP be called in a Select statement?
I have an USP which returns two values. I want these values as two column in a select statement.
Like
Select (Exec USP which returns one parameter),(Exec USP which returns another parameter) , Customernumber, so on
From Tab1.
Can i Accomplish that?
December 23rd, 2010 9:47am
This is an SSIS forum.
You did not mention your SQL Server version. As an aside a UDF is more appropriate in this scenario.
Some answers are here
http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/a9a9e027-0b93-4f9e-b0cf-84fa02010166/Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
December 23rd, 2010 9:54am
Thanks for the reply. I am using SQL SERVER 2008 R2.
Ya I initially used a UDF to accomplish my requirement. But due to corrupt data, my UDF Fails sometimes. I have to use a try catch block to return some kind of a value from the function but I cannot use try catch inside a function right? So that is why I
am planning to use a USP in which I can use a Try catch clock and return some flag which says 'Corrupt Date'.
I tried nesting a USP(which has the try catch block and returns value to the UDF) inside a UDF(Which in turn returns value to the calling Code). But it said I couldnot run a USP inside a UDF.
Now myidea is to nest a UDF inside a USP. I will wrap the function in a Try Catch Block like
Create Proc
As
Try
Call UDF
Catch
End
But even I could do that, My usp returns two values. How can I use the USP in a select statement like I mentioned above.
Select (Exec USP which returns one parameter),(Exec USP which returns another parameter) , Customernumber, so on
So any ideas as to how to accomplish it/? Is it possible?
December 23rd, 2010 10:15am