best practice for sub select in sproc

Question

best practice for sub select in sproc

yogiberr

Hall of Fame

Points: 3679
More actions
January 9, 2004 at 6:55 am

#106974

Hi,
I have a sproc that uses a sub-select.
Is the best way to put this sub-select into a separate sproc, so that it can be re-used elsewhere?

CREATE PROCEDURE [dbo].[spProductMaxDetailsGet]
AS

SELECT *
FROM tblProduct
WHERE productId = (SELECT MAX(productId)
FROM tblProduct)
GO
so, should I create a separate sproc, eg
'**********
spProductMaxIdGet
SELECT MAX(productId)
FROM tblProduct
'*************

cheers,
yogi

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

For something that simple, I would say no.

If you re-use the query several times within the same sp, I would set it equal to a variable and just re-use the variable.

If you have a complex query or set of queries that you re-use frequently in multiple sp's, then I would create a user-defined function (assuming you're on 2K or higher) so that you could call it inline.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

Or in addition to what Rawhide wrote,

SELECT .. Into #TempTable FROM ....

then maybe even CREATE [CLUSTERED] INDEX ... on #TempTable ()

Then use #TempTable inline with subsequent queries.

Once you understand the BITs, all the pieces come together

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

How about just:

CREATE PROCEDURE dbo.spProductMaxDetailsGet

AS

SELECT TOP 1 WITH TIES *

FROM tblProduct

ORDER BY ProductId DESC

--Jonathan

Robert Davis One Orange Chip Points: 28027 More actions · Answer 4

In this particular instance, that is the way that I would do it, but I was answering his question about subqueries.

I've found that when someone posts a very simple bit of code like this, 99% of the time, it's not the real code, but a simplified example of what they are doing. I'm guessing that his actual sp is more than that as well as his subquery.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

yogiberr Hall of Fame Points: 3679 More actions · Answer 5

Thanks folks, I can use the WITH TIES for the simple ones and the other approaches for more complex stuff...

It's always good to have several options.

cheers,

yogi