CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
techspider
techspider

Reputation: 3410

Execute stored procedures in SELECT

I have a table tblDashboard with this structure:

CREATE TABLE [dbo].[tblDashboard]
(
    [DashboardID] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Description] [nvarchar](2047) NOT NULL,
    [StoredProcedureName] [nvarchar](511) NULL,

    CONSTRAINT [PK_Dashboard] 
        PRIMARY KEY CLUSTERED ([DashboardID] ASC)
            WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                  ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

For each dashboard item, I have a stored procedure attached to it. I would have to execute the corresponding stored procedure set against the dashboard item.

The output I need basically is in the below format

SELECT 
    DashboardID, Name, 
    <stored procedure output> 
FROM 
    tblDashboard

I know it is not possible to have EXEC statement inside SELECT query but looking for advice / alternate ways to achieve this output. Thanks

All stored procedures return a nvarchar(MAX) type value. Should I change each of the stored procedures to functions and call inside SELECT query?

I have changed my stored procedures to functions and added sqlfiddle to help execute my functions.

Instead of Function Name in the last column, I need that function output. Is this possible with dynamic query?

Upvotes: 0

Views: 453

Answers (1)

Quassnoi
Quassnoi

Reputation: 425371

The best solution would be rewrite the stored procedure as a TVF. This way you would be able to do:

SELECT  dashboardId, name, tvf.*
FROM    dashboard
CROSS APPLY
        my_tvf(dashboardId) tvf

If you can't do that and if your stored procedure only returns one resultset, you need to declare a table variable or a temporary table (same layout as the procedure's resultset), then declare a cursor for dashboard entries and append to the table in a loop:

DECLARE cr_dashboard CURSOR FAST_FORWARD FORWARD_ONLY
FOR
SELECT  *
FROM    dashboard

DECLARE @id INT

OPEN    cr_dashboard

WHILE 1 = 1
BEGIN
        FETCH   cr_dashboard
        INTO    @id

        IF @@FETCH_STATUS <> 0
                BREAK

        INSERT
        INTO    @tv
        EXEC    my_sp @id
END

CLOSE   cr_dashboard

DEALLOCATE
        cr_dashboard

Then you need to join your @tv with dashboard (provided that the stored proc returns the dashboard id in the resultset somewhere).

Upvotes: 4

Related Questions