calling one stored proc from another by executing command

Question

In my stored procedure I have to pass a table and column name which may change everytime. So I build command and execute it. I want the output in another variable @curr_id. This store procedure will be called by second stored procedure by using @curr_id as input.

My problem is populating the @curr_id vriable. It is returned as zero. If I remove the curr_id variable then it works(see commented line)

Can someone pl. tell me - how to get @curr_id populated and returned as OUTPUT? - how to call the first stored proc from a second stored proc?

ALTER PROCEDURE [dbo].[sp_astm_getcurrid]
@ColName as nvarchar(250),
@TblName as nvarchar(250),
@curr_id nvarchar(max) OUTPUT 
AS
BEGIN
DECLARE @cmd nvarchar(max)

SET @cmd =N'SET '+@curr_id+'= SELECT MAX('+@ColName+') FROM '+@TblName;
--SET @cmd =N'SELECT MAX('+@ColName+') FROM '+@TblName;

EXEC (@cmd)

END

Answer 1

Friend Function execSP(ByVal spName As String, Optional ByVal params As Collection = Nothing) As Integer
        Dim cmd As SqlCommand
        Dim param As SqlParameter
        Dim ret As Integer
        Dim iParam As Integer

        cmd = New SqlCommand
        cmd.CommandText = spName
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = _sqlConn
        cmd.CommandTimeout = 0

        If Not params Is Nothing Then
            For iParam = 1 To params.Count
                param = params(iParam)
                cmd.Parameters.Add(param)
            Next
        End If

        If _sqlConn.State <> ConnectionState.Open Then
            _sqlConn.Open()
        End If

        Try
            ret = cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            _sqlConn.Close()
        End Try
        Return ret
    End Function

Answer 2

Because EXEC runs in a different context, it is not aware of your @curr_id variable. Instead, you can place the output of your dynamic SQL into a table variable, and then use that to set @curr_id.

Also, never start a stored procedure name with sp_.

    ALTER PROCEDURE [dbo].[usp_astm_getcurrid]
        @ColName as nvarchar(250),
        @TblName as nvarchar(250),
        @curr_id nvarchar(max) OUTPUT 
    AS
    BEGIN
        DECLARE @cmd nvarchar(max)

        declare @dummy table (
            ReturnColumn nvarchar(max)
        )

        SET @cmd = N'SELECT MAX(' + @ColName + N') FROM ' + @TblName;

        insert into @dummy
            (ReturnColumn)
            exec (@cmd)

        set @curr_id = (select ReturnColumn from @dummy)
    END

Then, an example of calling this procedure from within another could be something like this. The important key is to use the OUTPUT keyword here in the call as well as in the declaration of the procedure above.

    CREATE PROCEDURE CallMyProcedure
    AS
    BEGIN
        declare @curr_id nvarchar(max)
        exec dbo.usp_astm_getcurrid N'YourColumnName', N'YourTableName', @curr_id OUTPUT
        select @curr_id
    END

Answer 3

You can use the OUTPUT parameter in sp_executesql:

ALTER PROCEDURE [dbo].[sp_astm_getcurrid] 
@ColName as nvarchar(250), 
@TblName as nvarchar(250), 
@curr_id nvarchar(max) OUTPUT  
AS 
BEGIN 

DECLARE 
  @cmd nvarchar(max)

SET @cmd =N'SELECT @curr_id_out = MAX('+@ColName+') FROM '+@TblName 

EXEC sp_executesql 
  @cmd, 
  N'@curr_id_out nvarchar(max) OUTPUT', 
  @curr_id_out = @curr_id OUTPUT

END 

I would be remiss not to mention that, in this case, using EXEC(@cmd) or sp_executesql(@cmd) leaves you vulnerable to SQL injection attacks.

I recommend adding something like the following to the beginning of the stored procedure:

SELECT 
  @ColName = REPLACE(
             REPLACE(
             REPLACE(@ColName, ';', ''), '-', ''), '''', ''),
  @TblName = REPLACE(
             REPLACE(
             REPLACE(@TblName, ';', ''), '-', ''), '''', '')