Access Stored Procedure Parameters with Dynamic SQL

Question

I have a stored procedure that is called by data collection system.

The procedure has many parameters that for the collected data.

I'm using the INFORMATION_SCHEMA to pull the parameter list into a temp table

    SELECT substring(PARAMETER_NAME , 2 , len(PARAMETER_NAME ) - 1) 'SpParam', PARAMETER_NAME, DATA_TYPE
    INTO #tempParam
    FROM INFORMATION_SCHEMA.PARAMETERS
    WHERE SPECIFIC_NAME='InsertB2ChamberData'   

From there I can insert any missing data tag names in my tag list table

    INSERT INTO ToolTag
    SELECT @ToolID, @ToolTagTypeID, SpParam, 'Default Description', GETDATE()
    FROM #tempParam
    WHERE spParam NOT IN (SELECT ToolTagName FROM ToolTag WHERE ToolID = @ToolID)

So far so good, now I would like to use the ToolTag and the temp table list to insert the data from each parameter. Initial I though some dynamic SQL would do the trick.

    DECLARE tag CURSOR FOR 
    SELECT t.ToolTagID, p.PARAMETER_NAME, p.DATA_TYPE
    FROM ToolTag t
        JOIN #tempParam p
            ON t.ToolTagName = p.SpParam

    OPEN tag

    FETCH NEXT FROM tag INTO @TagID, @Parameter, @DataType

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @Cindex = CHARINDEX('char', @DataType)
        IF @Cindex  0
        begin

            SELECT @sql = 
            N'INSERT INTO ToolTagData VALUES('+convert(varchar(10),@TagID)+', '+convert(varchar(10),@ToolDataEventID)+', '+ @Parameter +', 0)'
        end
        else
        begin
            SELECT @sql = 
            N'INSERT INTO ToolTagData VALUES('+convert(varchar(10),@TagID)+', '+convert(varchar(10),@ToolDataEventID)+', CONVERT(varchar(255),'+@Parameter +'), '+convert(varchar(50),@Parameter)+')'

        end

        EXEC(@sql)

        FETCH NEXT FROM tag INTO @TagID, @Parameter, @DataType
    END

    CLOSE tag
    DEALLOCATE tag

Of course the above doesn't work as the @sql statement ends up with something like this:

INSERT INTO ToolTagData VALUES(315, 50, @ShutterPosition, 0)

As opposed to the @ShutterPosition parameter value. I'm stuck here, I could so some kind of brute force with each name, but I'd like to be able to be abstract and have re-usability for other procedures.

So is there any way out this, or am I barking up the wrong tree with this approach?

EDIT: My Schema looks like this:

The goal is to insert one record into ToolTagData Data for each stored procedure parameter. The key into ToolTag is the name of the stored procedure parameter.

The hope is that tags are added to the ToolTag table simply by adding a new parameter to procedure.

I'm limited by what the third party data acquisition program can do, so this an attempt abstract the process.

Answer 1

Instead of EXEC, use sp_ExecuteSQL

Then you can do something like this:

DECLARE
    @sql nvarchar(max),
    @ParamDef nvarchar(1000)


SET @ParamDef = N'@param1 int,
    @param2 int'

EXECUTE dbo.sp_ExecuteSQL @sql, @ParamDef,
    @param1 = @param1,
    @param2 = @param2