Executing a stored procedure through sp_executesql

Question

I am trying to execute a stored procedure in another stored procedure. The catch is that the name of the stored procedure is dynamically created in the first procedure. Here is an example of what I'm 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 get sp_executeSQL to work. Is it possible to execute the stored procedure in this way and get the value from the OUTPUT parameter?

Answer 1

Does 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

By the way, I think it’s a bad idea to have a lot of procs that do this, maybe you need to reorganize

Answer 2

try the following:

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

Answer 3

SQLMenace. . , , .

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

In this case, the value 10 is returned.

Parameters can be set using their names (names from the @params list are used) to avoid confusion when there are a lot of them.

Answer 4

Sorry for the delay: D, the following code works fine (for output and input parameters N) try this ( source )

CREATE PROCEDURE Myproc
@parm varchar(10),

@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT
AS
SELECT @parm1OUT='parm 1' + @parm
SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
@parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc

Answer 5

DECLARE @SQL NVARCHAR(100)
DECLARE @return_value int 
SET @SQL = '[dbo].[SpData] @id = 1' 
EXECUTE sp_executesql @SQL ,N'@return_value INT OUTPUT', @return_value OUTPUT"