Databases
StackOverflow2013
Postsdbo
POAccess Stored Procedure Parameters with Dynamic SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAccess Stored Procedure Parameters with Dynamic SQL
    primarykey
    data
    text
    <p>I have a stored procedure that is called by data collection <strong>system</strong>.</p> <p>The procedure has many parameters that for the collected data. </p> <p>I'm using the INFORMATION_SCHEMA to pull the parameter list into a temp table </p> <pre> SELECT substring(PARAMETER_NAME , 2 , len(PARAMETER_NAME ) - 1) 'SpParam', PARAMETER_NAME, DATA_TYPE INTO #tempParam FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='InsertB2ChamberData' </pre> <p>From there I can insert any missing data tag names in my tag list table</p> <pre> INSERT INTO ToolTag SELECT @ToolID, @ToolTagTypeID, SpParam, 'Default Description', GETDATE() FROM #tempParam WHERE spParam NOT IN (SELECT ToolTagName FROM ToolTag WHERE ToolID = @ToolID) </pre> <p>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. </p> <pre> 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 </pre> <p>Of course the above doesn't work as the @sql statement ends up with something like this:</p> <p>INSERT INTO ToolTagData VALUES(315, 50, @ShutterPosition, 0)</p> <p>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. </p> <p>So is there any way out this, or am I barking up the wrong tree with this approach?</p> <p><strong>EDIT:</strong> My Schema looks like this: <img src="https://i.stack.imgur.com/yt2dZ.jpg" alt="alt text"></p> <p>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. </p> <p>The hope is that tags are added to the ToolTag table simply by adding a new parameter to procedure.</p> <p>I'm limited by what the third party data acquisition program can do, so this an attempt abstract the process. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdverespey
    1. USdverespey
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAccess Stored Procedure Parameters with Dynamic SQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou are barking up the wrong tree. Give us some more information on your functional intent and you'll get some better suggestions.
      singulars
      1. POAccess Stored Procedure Parameters with Dynamic SQL
      1. USTahbaza
    2. COHere's some more details
      singulars
      1. POAccess Stored Procedure Parameters with Dynamic SQL
      1. USdverespey
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload