Executing Stored Procedure via sp_executesql

Question

Good morning,

I am attempting to execute a Stored Procedure within another stored procedure. The catch is that the stored procedure name is dynamically built within the first procedure. Here is an example of what I am trying to do ...

CREATE PROCEDURE SPINSVALUE_12345
    @guid uniqueidentifier
AS
    DECLARE @returnValue bit
    DECLARE @spToExec NVARCHAR(255)
    SET @returnValue = 0
    WHILE (@returnValue=0)
    BEGIN
         SET @spToExec = 'SPINSVALUE_' + REPLACE(@guid, '-', '_')
         ... DO OTHER STUFF ...
         EXEC sp_executeSQL @spToExec, N'@returnValue BIT OUTPUT', @returnValue OUTPUT
    END
END

I can't seem to get the sp_executeSQL to work. Is it possible to execute a stored procedure this way and get a value from the OUTPUT parameter?

Thank you in advance for any assistance,

Scott Vercuski

Answer 1

does the proc have a return value or an output value? here is an example

create proc prBlatest
as
return 5
go


DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)

SELECT @chvTableName = 'prBlatest'
SELECT @chvSQL = N'exec @intTableCount = ' + @chvTableName

EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount OUTPUT

SELECT @intTableCount
GO

BTW I think that this is a bad idea to have many procs that do similar thing, maybe you need to refactor

Answer 2

try this:

SET @spToExec = 'EXEC SPINSVALUE' + REPLACE(@guid, '-', '_') + ' @returnValue OUT'        
EXEC sp_executeSQL @spToExec, N'@returnValue int OUTPUT', @returnValue OUTPUT

Answer 3

I would like to add to SQLMenace's answer. His answer helped me move in the right direction. In the case where parameters are required for the procedure, they HAVE to be declared in the statement parameter.

create proc prBlatest @in int
as
return 5 + @in
go

DECLARE @chvTableName VARCHAR(100),
    @intTableCount INT,
    @chvSQL NVARCHAR(100)

SELECT @chvTableName = 'prBlatest'
SELECT @chvSQL = N'exec @intTableCount = ' + @chvTableName + ' @inputParam' 
        --NOTICE the @in parameter is declared in the statement parameter

EXEC sp_executesql @chvSQL, N'@inputParam int, @intTableCount INT OUTPUT'
    , @inputParam = 5
    , @intTableCount = @intTableCount OUTPUT

SELECT @intTableCount
GO

The value of 10 is returned in this case.

Parameters can be set using their names (uses the names from the @params list ) to avoid confusion when there are many.