Is it possible to call a Stored Procedure in a SELECT statement?

  • Hi there,

    In the code below the last SELECT statement gets a list of countries, with concatenated strings of cars :

    DECLARE@cars TABLE (countryid INT,model VARCHAR(20) )

    INSERT INTO @cars (countryid,model) VALUES (1,'Ford')

    INSERT INTO @cars (countryid,model) VALUES (1,'Chrystler')

    INSERT INTO @cars (countryid,model) VALUES (2,'Opel')

    INSERT INTO @cars (countryid,model) VALUES (2,'BMW')

    INSERT INTO @cars (countryid,model) VALUES (2,'Volkswagen')

    SELECT * FROM @cars

    DECLARE@countries TABLE (id INT,countryname VARCHAR(20) )

    INSERT INTO @countries (id,countryname) VALUES (1,'USA')

    INSERT INTO @countries (id,countryname) VALUES (2,'Germany')

    SELECT * FROM @countries

    SELECT co.*,(SELECT ca.model+', ' FROM @cars ca WHERE ca.countryid=co.id ORDER BY ca.model FOR XML PATH('') ) AS carlist

    FROM @countries co

    The SELECT statement that I use to get the concatenated string, is one I use in more than one place, so I prefer to put it in a Stored Procedure named spGetCarList.

    Is it possible to call that SP in a SELECT statement? Example in pseudo-code:

    stored procedure spGetCarList:

    CREATE PROCEDURE [dbo].[spGetCarList]

    @countryid INT ,

    @carList VARCHAR(MAX) OUTPUT

    AS

    BEGIN

    SELECT ca.model+', ' FROM @cars ca WHERE ca.countryid=@countryid ORDER BY ca.model FOR XML PATH('')

    END

    query string to get list:

    SELECT co.*, spGetCarList(co.id) AS carlist

    FROM @countries co

    It can be done with a user defined function, but that is no option for me (in case you want to know: my stored procedures run from the master system procedures, so all my databases can use them because they are fired within the context of each database. Calls to function do not run in the context of the database)

    Thanks,

    Raymond

  • [font="Verdana"]Absolutely it is not possible. Instead user User defined function[UDF]. You can call UDF in select statement.

    Mahesh[/font]

    MH-09-AM-8694

  • No, you can't. You can execute the proc and get a result set within another proc:

    EXEC spGetCarList @Parm1 = '42'

    You could insert the output of the procedure into a temp table and then use that within your code. This is an example from Jeff Moden that doesn't require you to create the temp table first:

    SELECT *

    INTO #MyHead

    FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC dbo.sp_Who')

    Do a search on the site. There are other examples.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • [font="Verdana"]Actually, I think if you look at table-valued functions, they would do the trick.

    CREATE FUNCTION [ schema_name. ] function_name

    ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

    [ = default ] }

    [ ,...n ]

    ]

    )

    RETURNS TABLE

    [ WITH [ ,...n ] ]

    [ AS ]

    RETURN [ ( ] select_stmt [ ) ]

    [ ; ]

    [/font]

  • Thanks for all ya guys' input. I'll start trying a few ideas....

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

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