Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-12-05 : 08:16:04
|
| Darran writes "I've written a script that generates UPDATE and INSERT scripts for every table on the database by examining the INFORMATION_SCHEMA. It creates the scripts beautifully, which work perfectly well if executed OUTSIDE the script via copy & paste.If executed INSIDE the Cursor Loop (that generates each script for each table in turn) I get this error message, 'CREATE PROCEDURE' must be the first statement in a query batch.'because the CREATE PROCEDURE statement is being executed in cursor loop.I don't think I can use a system SP to add my SP as a script because some of the resulting script texts are around 10000 characters in length.I don't really want to copy & paste all my SQL as its generated, so tell me guru - how can I get my script to create each SP on the fly?(The script to generate all the SPs will only ever be ran once on every new database build)" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-06 : 00:50:09
|
| I think you need to use the keyword Go>>how can I get my script to create each SP on the fly?Why do you want to create them on the fly?MadhivananFailing to plan is Planning to fail |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-06 : 04:37:55
|
| "how can I get my script to create each SP on the fly?"Insert each "statement" that your SProc generates into a table.Have something loop round the table executing the items one-at-a-time(This might include concatenating adjacent lines until you get to a "separator" row - a bit like having GO in a script)There is a system Sproc that will do a sort of "for-each" on rows in a table, so you might be able to use that to execute them, rather than having to create a CURSOR etc.Kristen |
|
|
|
|
|
|
Creating an SP from inside an SP