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: alt text

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.

You are barking up the wrong tree. Give us some more information on your functional intent and you'll get some better suggestions. — Tahbaza
– Tahbaza, Commented Oct 22, 2010 at 23:11

Gabriel McAdams · Accepted Answer · 2010-10-22 23:09:31Z

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

answered Oct 22, 2010 at 23:09

Gabriel McAdams

58.5k12 gold badges67 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dverespey · Accepted Answer · 2011-01-11 18:36:35Z

In the end I've decide to use a CLR stored procedure to implement this:

var tags = new
                                           {
                                                   @Tag1,
                                                   @Tag2,
                                                   ...
                                                   @LastTag};

             using (var conn =
                      new SqlConnection("context connection = true"))
             {
                      conn.Open();
                      var cmd = new SqlCommand
                                                  {
                                                           Connection = conn,
                                                           CommandText =
                                                                    "INSERT INTO TagGroupData SELECT TagGroupID , GETDATE() FROM TagGroup WHERE TagGroupName = 'L1Data' SELECT SCOPE_IDENTITY()"
                                                  };

                      var tagGroupDataId = cmd.ExecuteScalar();

                      cmd.CommandText = "";


                      cmd.Parameters.Add("@Data", SqlDbType.Float);
                      cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100);
                      cmd.Parameters.Add("@tg", SqlDbType.Int);

                      cmd.Parameters[2].Value = tagGroupDataId;

                      cmd.CommandText =
                               "INSERT INTO ToolTagData SELECT t.ToolTagID, CONVERT(varchar(255),@Data), @Data, @tg, GETDATE() FROM ToolTag t WHERE t.ToolTagName = @Name";

                      foreach (PropertyInfo pi in tags.GetType().GetProperties())
                      {
                               cmd.Parameters[1].Value = pi.Name;
                               cmd.Parameters[0].Value = pi.GetValue(tags, null);

                               cmd.ExecuteScalar();
                      }

Collectives™ on Stack Overflow

Access Stored Procedure Parameters with Dynamic SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related