[dba-SQLServer]SP question

Djabarov, Robert Robert.Djabarov at usaa.com
Wed Apr 16 12:32:51 CDT 2003


IN causes a table scan and very rarely engages indexes.

Robert Djabarov
Senior SQL Server DBA
USAA IT/DBMS
? (210) 913-3148 - phone
? (210) 753-3148 - pager

 -----Original Message-----
From: 	Michael Brösdorf [mailto:michael.broesdorf at web.de] 
Sent:	Wednesday, April 16, 2003 10:47 AM
To:	dba-sqlserver at databaseadvisors.com
Subject:	AW: [dba-SQLServer]SP question

Thanks for the information. What are the pros and cons of using EXISTS
instead of IN?

Michael

-----Ursprüngliche Nachricht-----
Von: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]Im Auftrag von
Djabarov, Robert
Gesendet: Mittwoch, 16. April 2003 17:36
An: dba-sqlserver at databaseadvisors.com
Betreff: RE: [dba-SQLServer]SP question


Try to avoid using IN.  Better replace it with EXISTS.  As per "sproc"...
Suzan nailed it with her suggestion about using UDF instead.
It would look like:

	Select * from MyTable mt where exists (select * from
dbo.fn_myfunction(<your_parameters>) fn where mt.KeyField = fn.KeyField)

Robert Djabarov
Senior SQL Server DBA
USAA IT/DBMS
? (210) 913-3148 - phone
? (210) 753-3148 - pager

 -----Original Message-----
From: 	Michael Brösdorf [mailto:michael.broesdorf at web.de]
Sent:	Wednesday, April 16, 2003 10:21 AM
To:	dba-sqlserver at databaseadvisors.com
Subject:	AW: [dba-SQLServer]SP question

If the sproc is called using the query analyzer, it returns a single column
containing a number of rows.

Michael

-----Ursprüngliche Nachricht-----
Von: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]Im Auftrag von Todd
Buttrey
Gesendet: Mittwoch, 16. April 2003 17:07
An: dba-sqlserver at databaseadvisors.com
Betreff: Re: [dba-SQLServer]SP question


What does the return from spMySP look like?

----Original Message Follows----
From: Michael Brosdorf <michael.broesdorf at web.de>
Reply-To: dba-sqlserver at databaseadvisors.com
To: <dba-sqlserver at databaseadvisors.com>
Subject: [dba-SQLServer]SP question
Date: Wed, 16 Apr 2003 16:47:42 +0200

Dear group,

is it possible to use the result set of a stored procedure in the where
clause of another SQL-Statement?

Something like:

Select * from MyTable where My_ID in (spMySP) (This does not work...)

The SP returns a couple of ID's (only one column)


TIA,

Michael

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list