April 17, 2002 at 10:39 am
This gets hairy, so bear with me 🙂
I have a table called TABLE_LIST that has 1 column which holds a string that corresponds to a name of a table in my database.
I want to generate a stored proc that
1) loops thru each value in this table,
2) selects a column (always the same column) out of the table
whose name I just selected in step 1
3) updates a column (always the same column) in a 3rd table with the info from step 2.
the first part is an easy cursor:
DECLARE Report_Cursor CURSOR FOR
SELECT ReportID, ReportTable
FROM Table_List
FETCH NEXT FROM REPORT_CURSOR
INTO @l_intReportID, @l_strReportTable
the next 2 parts i'm at a loss for.
the following code does NOT work
SET @l_strSQL = 'SELECT @l_dtRunDate=MAX(ReportDate) FROM ' + @l_strReportTable
EXEC @l_strSQL
UPDATE FOOTNOTES
SET LongDesc = 'Data run on ' + @l_dtRunDate
WHERE ReportID = @l_intReportID
AND ShortDesc = 'RUN DATE'
any help is greatly appreciated!
Matthew Mamet
April 17, 2002 at 10:56 am
Try EXEC (@l_strSQL)
I think, that without parenthesis you can execute stored proc
April 17, 2002 at 11:14 am
Yes () are required to EXEC a string as code.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply