WHERE EXISTS EXEC myStoredProc? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

WHERE EXISTS EXEC myStoredProc?

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-02-09 : 13:40:58

I have a customer table and a store procedure "procGetPremAcc" that takes the customerID and returns ALL the premium accounts of the customer. Now I'd like to have a store procedure that create a temp table that's the same as my customer table, but with an additional bit column "hasPremAcc" that has a default value of 0 and a value of 1 if the customer has any premium account. But when I have the following line in the stored procedure to update this field:

UPDATE #tempCustomer SET hasPremAcc = 1 WHERE EXISTS EXEC procGetPremAcc customerID;

it complains about "Incorrect syntax near the keyword 'EXEC'". How can I accomplish this using the "procGetPremAcc"?

Thanks,
ywb

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-09 : 13:56:31

You are going to have to add the results of the sproc to a temp table then do an existence check on that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-02-09 : 15:00:10

But the sproc takes the customerID as input. How do I loop through all the customers in the customer table and store each output into the temp table?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-09 : 15:02:22

create table #temp
(
columns that match your stored procedure returned resultset
)

insert into #temp
exec YourStoredProcedure

and then use #temp in your exists

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-09 : 16:07:04

You're going to have to supply more details...does the sproc return more than 1 row of data?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 18:15:00

Why don't you just make a copy of the stored procedure and adjust the code to do what you want?

Peter Larsson
Helsingborg, Sweden

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-02-13 : 16:39:36

Hi,

I should provide some sample data.

Here are 2 tables with customer and account data respectively.

CREATE TABLE tempCustomer(customerID smallint PRIMARY KEY IDENTITY, customerName nvarchar(100));

INSERT INTO tempCustomer VALUES ('Simon');
INSERT INTO tempCustomer VALUES ('Randy');
INSERT INTO tempCustomer VALUES ('Paula');

CREATE TABLE tempCustomerAcc(accountID smallint PRIMARY KEY IDENTITY, customerID smallint, accountType nvarchar(10), isPremiumAcc bit);

INSERT INTO tempCustomerAcc VALUES (1, 'checking', 0);
INSERT INTO tempCustomerAcc VALUES (3, 'savings', 1);
INSERT INTO tempCustomerAcc VALUES (1, 'mortgage', 0);
INSERT INTO tempCustomerAcc VALUES (2, 'savings', 1);
INSERT INTO tempCustomerAcc VALUES (2, 'creditCard', 0);
INSERT INTO tempCustomerAcc VALUES (1, 'savings', 0);
INSERT INTO tempCustomerAcc VALUES (3, 'checking', 1);

There's a stored procedure which takes the customer ID as input and returns all the premium accounts of a customer:

CREATE PROCEDURE procGetPremAcc
@CustomerID smallint
AS
SELECT *
FROM dbo.tempCustomerAcc acc
WHERE (customerID = @CustomerID) AND (isPremiumAcc = 1);
GO

Now I'd like to write a query that would return customer table, with an additional (bit) column that is 1 if the user has premium account(s) and 0 of otherwise. Like Peso suggested, of course I could just make a copy of the stored procedure "procGetPremAcc" in my query. But that stored procedure in real life is much more complex and it contains the definition of "premium account". If I can just re-use it, I won't have to updating my query when the definition of "premium account" is changed in "procGetPremAcc".

Any suggestion?

Thanks,
ywb

This stored procedure of course a much simplified. The

quote:
Originally posted by X002548

You're going to have to supply more details...does the sproc return more than 1 row of data?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2007-02-13 : 23:00:13

Well this is not the way to obtain optimal performance, but this is one way to achieve what you're asking...


IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t (accountID SMALLINT, customerID SMALLINT, accountType NVARCHAR(10), isPremiumAcc BIT)
DECLARE c CURSOR READ_ONLY FOR SELECT customerID FROM tempCustomer
DECLARE @CustomerID SMALLINT
OPEN c

FETCH NEXT FROM c INTO @CustomerID
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
		INSERT #t EXEC procGetPremAcc @CustomerID
	FETCH NEXT FROM c INTO @CustomerID
END

CLOSE c
DEALLOCATE c

SELECT *,
	CASE WHEN EXISTS (SELECT * FROM #t WHERE CustomerId = a.CustomerId)
	THEN 1 ELSE 0 END AS hasPremAccv
FROM tempCustomer a

/*results
customerID customerName hasPremAccv
1          Simon        0
2          Randy        1
3          Paula        1
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-02-14 : 16:12:54

Thanks, Ryan, that's exactly what I was looking for.

quote:

Originally posted by RyanRandall

Well this is not the way to obtain optimal performance, but this is one way to achieve what you're asking...


IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t (accountID SMALLINT, customerID SMALLINT, accountType NVARCHAR(10), isPremiumAcc BIT)
DECLARE c CURSOR READ_ONLY FOR SELECT customerID FROM tempCustomer
DECLARE @CustomerID SMALLINT
OPEN c

FETCH NEXT FROM c INTO @CustomerID
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
		INSERT #t EXEC procGetPremAcc @CustomerID
	FETCH NEXT FROM c INTO @CustomerID
END

CLOSE c
DEALLOCATE c

SELECT *,
	CASE WHEN EXISTS (SELECT * FROM #t WHERE CustomerId = a.CustomerId)
	THEN 1 ELSE 0 END AS hasPremAccv
FROM tempCustomer a

/*results
customerID customerName hasPremAccv
1          Simon        0
2          Randy        1
3          Paula        1
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources