Select * FROM mytable WHERE mycol IN (EXEC sp_myprocedure) ???
-
I know this syntax is not correct but what is the syntax for what I'm trying to do.
-
Create a temp table that matches the sproc's resultset columns:
Create Table #Output (
Column1 datatype1,
etc
)
Insert Into #Output
exec sp_myprocedure
Select *
From MyTable
Where MyCol IN (Select SomeCol From #output)
And of course, in real life, your procedure name won't begin with "sp_" right ?
-
If your SP is a single query you may consider replacing it with a view or a table function.
_____________
Code for TallyGenerator -
My goal was to simplify things and most importantly increase the speed of my querys. Currently I'm not using stored procedures. My statements have become complex with table JOINs. And once I concatenate all my WHERE clause arguments then I have a huge SELECT statement. I thought I could make a stored procedure for each of my conditions and it might help matters. Something like this:
SELECT * FROM orders
WHERE order_id IN (EXEC spr_orders_approved)
AND
order_id IN (EXEC spr_orders_by_region @region_id = 1)
AND
order_id IN (EXEC spr_orders_by_manager @employee = 1)
AND
order_id IN (EXEC spr_orders_by_year @whatyear = '2006')
Any help would be appreciated.
-
So, why not views?
SELECT O.* FROM orders O
INNER JOIN dbo.orders_approved A ON O.order_id = A.order_id
INNER JOIN dbo.orders_by_region R ON O.order_id = R.order_id AND R.region_id
INNER JOIN dbo.orders_by_manager M ON O.order_id = M.order_id AND M.employee = 1
INNER JOIN dbo.orders_by_year Y ON O.order_id = Y.order_id Y.whatyear = '2006'
_____________
Code for TallyGenerator -
I just thought stored procedures where the way to go. I'm all the time hearing about how they are supposed to improve performance but everytime I go to try and implement using them it seems like its not possible. Are stored procedures not all they are cracked up to be?
Anyone a fan of stored procedures? Feel free to chime in
Thanks for the first example though. I think I can work with that and see if perfomance improves.
-
Stored procedures typically encapsulate some SQL operation - they way you're using them to 'dynamically' add to the WHERE is not their intended use and will lead to horrible performance.
It sounds like you need a stored proc for returning 'Approved' orders, with 3 variable parameters:
CREATE PROCEDURE GetApprovedOrders
@Region_id As int,
@Employee As int,
@WhatYear As smallint
AS
BEGIN
[Your SQL Here]
Where Order.Status = 'Approved'
And Order.Region_id = @Region_id
etc
etc
END
-
You could create a functions that returns a tables instead.
These functions you can use like a table.
Regards,Anders Dæmroen
epsilon.no -
Procedure is just procedure. Do you know the meaning of this word?
It's not a table, not any other type of object.
What would you say about developer who's trying to use a method as an object?
That's exactly what you are trying to do.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply