create sproc from data stored in table

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

SQL Server Development (2000)

create sproc from data stored in table

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2004-10-15 : 18:15:28

Hi,
I'm in the process of creating a dynamic stored procedure generator. The stored procedure is generated based on metaData stored in the database. Unfortunately the procedure is long and will not fit in an 8000 character string. I have decided to insert each line of the procedure into a temp table. After generating all the lines, I want to read the values out of the temp table and generate the procedure (so the temp table contains all the t-sql code to generate the procedure). The problem is I'm not sure how to read the data out into a string (or combination of strings) so that in the end I can call EXE(@sqlText + n)

I need to retrieve all the data and concatenate it so it will generate a stored procedure. I realize this procedure itself will be slow but the end result is a static (system generated) procedure which should be fairly efficient.

ps. Is there a maximum length that a stored procedure can be?


CREATE PROCEDURE AdminCreateFieldAuditProcedure
AS
Declare 
@lineEnd varchar(10)
,@txt varchar(8000)
SET @lineEnd = CHAR(13) + CHAR(10)

-- create temp table (used to generate sproc)
CREATE TABLE #AuditTemp
(
	id int identity (1,1)
	,sqlText varchar(8000)
) 

SELECT @spName = 'GetHomeAppFieldAudit'

-- create drop sp statement
SELECT @txt = 'IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='''+ @spName + ''')' + @lineEnd
SELECT @txt = @txt + 'DROP PROCEDURE ' + @spName + @lineEnd 
INSERT INTO #AuditTemp VALUES (@txt)

-- create sp statement
SELECT @txt = 'CREATE PROCEDURE ' + @spName + '' + @lineEnd
SELECT @txt = @txt + '(@QuoteID int) AS' + @lineEnd
SELECT @txt = @txt + 'SET NOCOUNT ON' + @lineEnd
INSERT INTO #AuditTemp VALUES (@txt)

-- cut out lots of the code to make it more readable
-- do stuff that will create more lines for the sproc
SELECT @txt = ' SELECT ' + @fieldName + '  FROM ' + @tableName +  ' WHERE QuoteID = @QuoteID ' + @lineEnd
INSERT INTO #AuditTemp VALUES (@txt)

-- finish sp
SELECT @txt = 'SET NOCOUNT OFF' + @lineEnd
INSERT INTO #AuditTemp VALUES (@txt)

SELECT SqlText FROM #AuditTemp ORDER BY ID
-- need to read all the data out and concatenate it to create a sproc.
--EXEC (SELECT SqlText FROM #AuditTemp ORDER BY ID)
DROP TABLE #AuditTemp
GO

Nic

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-16 : 01:11:26

Why not build it in a text datatype.
Then when you need to exec it you can split it into 8000 char strings in dynamic sql.
Or you could bcp out to a file and use osql to execute.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 01:20:37

You could have a number of varchar(8000) variables, one of each section of the script, and then do an
EXEC(@txt1 + @txt2 ...)

(BoL says the limit for each @txtN is 4000 characters, but I think that is only the case if you use nvarchar)

Obviosuly no individual "section" of SQL script can be more than 8000 characters, so in the long run you might be better to move to using a TEXT datatype

Kristen

nic
Posting Yak Master

209 Posts

Posted - 2004-10-18 : 14:55:16

Thanks for your help. I tried moving the data to a text datatype but got errors in the sproc saying can't use text datatype as a parameter. Anyway, I also was running into problems on how to parse the text into 8000 character chunks. I saved each line from the sproc in a temp table and have been trying to use dynamic sql to execute it.

I when "PRINT" the output instead of executing it, the output works fine. When I try to execute it directly, I am getting errors. Not sure how to get around it. I would prefer to just execute the sproc and have it create the new procedure (instead of executing it, getting output and then executing the output.) The problem code below starts at the DECLARE statement. If you run this code you get error messages saying need to declare the variables. If I comment out the "print" code and comment the "exec" code, the output is correct and running it in query anlyzer works fine. I tried to simplfy the code to make it clearer.

-- create temp table (used to generate sproc)
CREATE TABLE #AuditTemp
(
id int identity (1,1)
,sqlText varchar(8000)
)

INSERT INTO #AuditTemp VALUES ('CREATE PROCEDURE GetPersAutoAppFieldAudit
(@QuoteID int) AS
SET NOCOUNT ON
')
INSERT INTO #AuditTemp VALUES ('SELECT ''Residence.General.EffectiveDate'' AS fTitle, ''dt'' AS fType, (SELECT EffectiveDate FROM Quotes WHERE QuoteID = @QuoteID) as fVal
')

-- here is where I am running into problems
DECLARE
@i int
,@maxID int
,@exeTemp varchar(8000)
,@decTemp varchar(8000)
,@varTemp varchar(8000)
,@sqlTemp varchar(8000)

SELECT @maxID = MAX(ID) FROM #AuditTemp
SET @i = 1
SET @exeTemp = 'EXEC('
WHILE @i <> @maxID + 1
BEGIN
SELECT @decTemp = 'DECLARE @var' + CAST(@i AS Varchar) + ' varchar(8000) '
EXEC(@decTemp)
--PRINT(@decTemp)

SELECT @varTemp = REPLACE(sqlText,'''','''''') FROM #AuditTemp WHERE id = @i
SELECT @sqlTemp = 'SELECT @var' + CAST(@i AS Varchar) + ' = ''' + @varTemp + ''''
EXEC(@sqlTemp)
--PRINT(@sqlTemp)

SELECT @exeTemp = @exeTemp + '@var' + CAST(@i AS Varchar) + '+'
SELECT @i = @i + 1
END
SELECT @exeTemp = LEFT(@exeTemp,LEN(@exeTemp)-1) + ')'
EXEC(@exeTemp)
--PRINT(@exeTemp)

Nic

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 01:21:55

Each will only be in scope for the duration of its own EXEC, so you need to do:
EXEC(@decTemp + @sqlTemp + @exeTemp)

(Make sure each one ends with a space!)

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-19 : 08:41:45

>> I tried moving the data to a text datatype but got errors in the sproc saying can't use text datatype as a parameter.
Use a single row temp table with a text column.

>> I also was running into problems on how to parse the text into 8000 character chunks
Use substring.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources