Post reply

Creating a Stored Procedure Dynamically

  • March 31, 2006 at 9:46 am

    #111606

    In SQL2K, having generated a stored proc from a template by applying parameters within a generator proc, we now need to actually CREATE it. Unfortunately the total length of the generated proc is greater than 8000. Not being able to use Text datatype as a local variable, we tried

    execute(@SQL1  + @SQL2 + @SQL3)

    in which the three variables are varchar(8000). It executed OK, but the cataloged procedure only has the first 8000 bytes. What is the best way to generate a stored proc within another stored proc?

  • March 31, 2006 at 11:45 am

    #630208

    My apologies. The execute was OK; due to a logic error within the generator proc, @SQL1 was not populating properly, @SQL2 was not populating at all, and there was a 'GO' in the template. After correcting those defects, the procedure cataloged correctly.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply