July 9, 2008 at 3:37 pm
Hi,
I am trying to call stored procedure-B from stored procedure-A's where clause.
I need to check whether stored procedure-B returns any rows.
Please help me out in the syntax.
Requirement is: Show only the events which has Items.
By passing the EventId to stored procedure-B I will know by its rows how many Items are present(many tables are combined in this stored procedure). & Stored procedure-A will combine other tables & show the required result.
July 9, 2008 at 11:27 pm
Hi Venm,
First of all you need to create a Temporary Table and insert the result of Stored Procedure in that table. Now you can use this table in WHERE clause.
-- Example
Create Proc Procedure2 (@ID int)
AS
BEGIN
CREATE TABLE #T (ID int, NAME varchar(50))
INSERT INTO #T EXEC Procedure1 @parameter(if any)
-- Apply your conditions here using #T table
END
July 9, 2008 at 11:32 pm
Hi
What do you mean by "where clause". If its the where clause of a sql statement then you can use a function instead of a procedure.
"Keep Trying"
July 10, 2008 at 6:31 am
You would have to use a user defined function as Chirag mentions above. Doing that in the where clause is pretty likely to lead to RBAR (row-by-agonizing-row) processing, which kills your performance. You should examine some method of using a derived table in the where clause, or loading data into a temporary table which you can then join against... set based approaches.
"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
July 10, 2008 at 7:49 am
Hi
Thanks for your suggestions. Ghirag I can't use the function because the stored procedure which I need to call already exists.
With Hari's reply I could able to solve my problem.
Thanks once again for all.
venm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply