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