Post reply

best practice for sub select in sproc

  • 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

     

  • January 9, 2004 at 9:08 am

    #489396

    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]

  • January 9, 2004 at 9:28 am

    #489408

    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

  • January 9, 2004 at 9:35 am

    #489412

    How about just:

    CREATE PROCEDURE dbo.spProductMaxDetailsGet

    AS

    SELECT TOP 1 WITH TIES *

    FROM tblProduct

    ORDER BY ProductId DESC



    --Jonathan

  • January 9, 2004 at 9:42 am

    #489416

    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]

  • January 9, 2004 at 12:54 pm

    #489450

    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

     

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

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