[ACCEPTED]-Execute a Stored Procedure in a SELECT statement-select

Construct the EXECUTE statements in your 14 select like this:

SELECT 'EXEC sp_whatever ' + parameter stuff
FROM   your_table

Then run the results! Alternatively, paste 13 your results into a spreadsheet package, and 12 use string concatenation to construct the 11 EXEC statements - just create a formula 10 and paste it down the 1,000 rows. I personally 9 prefer the first approach.

To clarify the 8 "parameter stuff", take the example of a 7 stored procedure that takes two int parameters 6 that you want to take from columns you your_table. You'd 5 then have something like this:

SELECT 'EXEC sp_whatever ' + CAST(field1 AS varchar) + ', ' + CAST(field2 AS varchar)
FROM    your_table

Not the need 4 to be careful with string fields here - you 3 run the risk of inadvertently exposing yourself 2 to your own SQL injection attack, as with 1 any SQL string concatenation.

I am reading your "for an instance" as "this is a one-off task". If this is a task that needs automating, then one of the other answers may be the right approach.

You can do it like this:

declare @execstatementsbatch nvarchar(max)

select @execstatementsbatch = ''

SELECT @execstatementsbatch = @execstatementsbatch   + 'EXEC UpdateQty ' + ItemCode +  ', '  + QtyBO +  '; ' 
FROM ITEMSPO 
INNER JOIN ..... 
<some conditions>

exec(@execstatementsbatch)

0

Disclaimer: I'm not sure if I understand your question correctly.

Assuming you are on SQL Server 2005 upwards, you 2 could create a table-valued user defined 1 function and use the OUTER APPLY operator in your query.

Most RDBMS will let you select rows from 4 stored procedure result sets. Just put your 3 stored procedures in the FROM clause, as 2 you would for common table expressions. For 1 instance:

SELECT sp.ColumnInResultSet, t.BaseTableColumnName
FROM sp_whatever ( Args) sp INNER JOIN BaseTable t ON t.ID = sp.ID;

CREATE PROCEDURE dbo.usp_userwise_columns_value
(
    @userid BIGINT
)
AS 
BEGIN
        DECLARE @maincmd NVARCHAR(max);
        DECLARE @columnlist NVARCHAR(max);
        DECLARE @columnname VARCHAR(150);
        DECLARE @nickname VARCHAR(50);

        SET @maincmd = '';
        SET @columnname = '';
        SET @columnlist = '';
        SET @nickname = '';

        DECLARE CUR_COLUMNLIST CURSOR FAST_FORWARD
        FOR
            SELECT columnname , nickname
            FROM dbo.v_userwise_columns 
            WHERE userid = @userid

        OPEN CUR_COLUMNLIST
        IF @@ERROR <> 0
            BEGIN
                ROLLBACK
                RETURN
            END   

        FETCH NEXT FROM CUR_COLUMNLIST
        INTO @columnname, @nickname

        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @columnlist = @columnlist + @columnname + ','

                FETCH NEXT FROM CUR_COLUMNLIST
                INTO @columnname, @nickname
            END
        CLOSE CUR_COLUMNLIST
        DEALLOCATE CUR_COLUMNLIST  

        IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'v_userwise_columns_value')
            BEGIN
                SET @maincmd = 'CREATE VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , ' 
                            + CHAR(39) + @nickname + CHAR(39) + ' as nickname, ' 
                            + @columnlist + ' compcode FROM dbo.SJOTran '
            END
        ELSE
            BEGIN
                SET @maincmd = 'ALTER VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , ' 
                            + CHAR(39) + @nickname + CHAR(39) + ' as nickname, ' 
                            + @columnlist + ' compcode FROM dbo.SJOTran '
            END

        --PRINT @maincmd
        EXECUTE sp_executesql @maincmd
END

-----------------------------------------------
SELECT * FROM dbo.v_userwise_columns_value

0