Stored Procedure that generates Stored procedures

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure that generates Stored procedures

Author

Topic

Beachsandintoes
Starting Member

15 Posts

Posted - 2005-05-11 : 17:07:43

Hi,

As some of you know, we've been developing a SQL Server version of a table wrapper generator. Store procedure wrappers are created by passing the name of a table into a stored procedure called tst_TableProcedureGen('tblName') and BAMM! there should be six new stored procedures:
- tst_tblName_get
- tst_tblName_gets
- tst_tblName_insert
- tst_tblName_update
- tst_tblName_delete
- tst_tblName_count

Kudos to Michael Valentine Jones for helping us break thru the varchar(8000) barrier with his tip:
select
@txt_1 = substring(ntext_data, 1, 4000),
@txt_2 = substring(ntext_data, 4001, 4000),
@txt_3 = substring(ntext_data, 8001, 4000)
from #temp_data

This has worked very well. Again, thanks.

The problem that we are running into now is getting EXEC to run a 'CREATE PROCEDURE ' command. For example:

-------------------------------------------------------------------

declare @sqlText as varchar(4000)
set @sqlText = 'CREATE PROCEDURE [TestUser].[tst_tblName_insert] @x AS int AS BEGIN INSERT INTO dbo.TestTable1(x) VALUES(@x) END'

EXEC @sqlText

-------------------------------------------------------------------

We expected that EXEC @sqlText would create the procedure but instead we get:
"The name 'CREATE PROCEDURE <<full text>>' is not a valid identifier."

Any thoughts? Can this be done in a stored procedure? Or, should we write a file and execute the file?

Thank you for your time.

- Beach

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-11 : 17:33:24

You need to enclose the variable name in ().

If you don't, SQL Server assumes that @sqlText is the name of an existing procedure that you want to execute. Check SQL Server Books Online for the syntax of the EXECUTE statement.


declare @sqlText as varchar(4000)
set @sqlText = 
'CREATE PROCEDURE [tst_tblName_insert]
	@x AS int
AS
BEGIN
INSERT INTO dbo.TestTable1(x) VALUES(@x)
END'

EXEC ( @sqlText )

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-11 : 18:26:36

You could just use Codesmith for this:

http://weblogs.sqlteam.com/damianm/archive/2003/09/29/185.aspx

Tara

Beachsandintoes
Starting Member

15 Posts

Posted - 2005-05-11 : 22:22:55

Hi,

Thanks again Michael We tried your suggestion and it worked. Whew, we are now beginning to see light at the end of the tunnel.

Tara, thanks for the Codsmith tip. It looks pretty interesting.

- Beach

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources