June 3, 2009 at 1:43 am
I need to make a SELECT with a call of a stored procedure in the WHERE clause.
It should be something like that....
SELECT distinct top 10 i.x, d.droit
FROM v_droit d, v_info i
WHERE d.nomdroit='yy'
AND i.id<>2
AND (select val from (exec up_droits(i.x, d.droit)) <>3
But it does not work...
Any idea?
Don't say to replace the stored procedure with a function because is not possible to use the existing code in a function. So the function is not a valid option. I really need to be able to use a stored procedure
It's for SQL Server 2005
June 3, 2009 at 2:10 am
You can use temporary table instead stored procedure into where clause.
eg,
create table #spResult ({columns as result of your sp})
insert into #spResult exec YourSP ({input parameters})
select * from yourtalble
where col in (select col from #spResult)
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 2:17 am
Hi Rox,
It would be better to convert your Stored Procedure into a Function, if possible. Then you can use function in WHERE clause.
If you can't convert it into the function then better to execute the SP and stored the full result in Temp Table or Table Variable. Now you can use This table in WHERE clause SUB QUERY.
June 3, 2009 at 2:38 am
does your SP have an output parameter?
i'm not sure, but i think you could run the SP, assigning the output parameter to a variable and then put that variable in your where clause in place of the SP name .
June 3, 2009 at 6:41 am
Loading the output from a procedure into a temp table and then joining that temp table with your other tables is a good approach. As was noted, if you only need a few fields, no rows, from the procedure, then output parameters are the way to go.
I'd be very cautious about converting the procedure to a UDF and then using that in the WHERE clause. UDF's are useful, but they're also quite notorious as performance bottlenecks. If you do decide to go this route, test very carefully and remember, a UDF frequently has no or little estimated cost in execution plans (due to the lack of statistics, depending on the type of UDF and how it's used), so you can't rely on that cost as an estimate for how well the UDF is working.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 3, 2009 at 7:04 am
roxanadima1 (6/3/2009)
I need to make a SELECT with a call of a stored procedure in the WHERE clause.It should be something like that....
SELECT distinct top 10 i.x, d.droit
FROM v_droit d, v_info i
WHERE d.nomdroit='yy'
AND i.id2
AND (select val from (exec up_droits(i.x, d.droit)) 3
But it does not work...
Any idea?
Don't say to replace the stored procedure with a function because is not possible to use the existing code in a function. So the function is not a valid option. I really need to be able to use a stored procedure
It's for SQL Server 2005
Your code puzzles me. While others have explained to you how to utilize your SP to deliver the functionality you desire... my question is how is this code related to the rest of your query?
The code: Select val from (exec up_droits(i.x, d.droit)) - presupposes the presence of a field named VAL and requires the output of exec up_droits to provide that. Also, Assuming your SP gives as output a series of numbers (such as 1,2,3 etc) , then the next step should pick up any value which is NOT equal to 3... in other words it can pick up multiple values. Then how will your code function?
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply