SP in IN clause
in [Programming SQL]
|
From: DavidC on 15 Apr 2010 10:17 Is it possible to use a stored procedure inside an IN clause? For example below, the stored proc returns a single column of TimeID values. SELECT TimeID FROM dbo.Timesheets WHERE (TimeID IN(EXEC mc_selDuplicateTimesheets)) Or is there another workaround? Thanks. -- David
From: Plamen Ratchev on 15 Apr 2010 10:28 You can use a table to store the SP output and then use the table in the query: CREATE TABLE #Dups (TimeId INT); INSERT INTO #Dups (TimeId) EXEC mc_selDuplicateTimesheets; SELECT TimeID FROM dbo.Timesheets WHERE TimeID IN (SELECT TimeId FROM #Dups); -- Plamen Ratchev http://www.SQLStudio.com
From: Kalen Delaney on 15 Apr 2010 10:35 Hi David No, a stored procedure cannot be used in an expression. Its invocation is always a stand-alone command. Why won't a subquery work here to generate the values you need? If the logic to compute the return values is absolutely too complex to generate in a single query, you could consider a table-valued function. But you might want to supply more details here of how the return values are determined, and someone here might be able to help you simplify thins. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "DavidC" <dlchase(a)lifetimeinc.com> wrote in message news:5A5A0201-AB15-44D4-B8CF-CD4DAFFE9DE8(a)microsoft.com... > Is it possible to use a stored procedure inside an IN clause? For example > below, the stored proc returns a single column of TimeID values. > > SELECT TimeID > FROM dbo.Timesheets > WHERE (TimeID IN(EXEC mc_selDuplicateTimesheets)) > > Or is there another workaround? Thanks. > -- > David
From: Sylvain Lafontaine on 15 Apr 2010 10:38 A SP returns a ResultSet; which is a special object designed to be transferred over a connection toward a client such as an ODBC, OLEDB (ADO) or ADO.NET client. While you can call a SP from another SP, they have not been designed to be called from a query and doing so is usually a waste of performance. The fastest way would be to create a table and perform an Insert ... Exec call; from there, you can select the result from the table into your IN clause. A second but slower method would be to use OPENROWSET to call your SP through a linked server. This is often the method used by people to get directly the output of SP such as sp_who or sp_lock without having to create a (temporary) table. However, while it's useful for debugging and analysing purposes, going through a linked server will considerably slow down the overall performance. In my opinion, if you need to call a SP from a query, you should change your SP into a table valued user function; especially if performance is a concern. For examples of using OPENROWSET, search the internet for something like � OpenRowSet sp_lock �. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "DavidC" <dlchase(a)lifetimeinc.com> wrote in message news:5A5A0201-AB15-44D4-B8CF-CD4DAFFE9DE8(a)microsoft.com... > Is it possible to use a stored procedure inside an IN clause? For example > below, the stored proc returns a single column of TimeID values. > > SELECT TimeID > FROM dbo.Timesheets > WHERE (TimeID IN(EXEC mc_selDuplicateTimesheets)) > > Or is there another workaround? Thanks. > -- > David
From: --CELKO-- on 15 Apr 2010 12:22
>> Is it possible to use a stored procedure inside an IN clause? << The IN() predicate expects a table expression or a table constructor (i.e. list of values). A stored procedure does not return a table. But you might try a table-valued function and see if you can make that work. Of course the best way is to learn to write declarative code (in this case, a subquery for the IN) and get your mindset out of procedural code altogether. |