SQLServerCentral / Discuss Content Posted by Robert Marda / Article Discussions / Article Discussions by Author / How to Build Dynamic Stored Procedures / Latest Posts

RE: How to Build Dynamic Stored Procedures

markybse — Wed, 09 Jan 2008 08:16:05 GMT

I believe that the second portion, "trying to concactinate NULL values" could be a good start in finding out why it is not working. Thanks again.

RE: How to Build Dynamic Stored Procedures

mark hutchinson — Tue, 08 Jan 2008 16:27:37 GMT

[quote][b]markybse (1/8/2008)[/b][hr]... the first exec line fails and the second exec line works. I am at a loss as to why.DECLARE @SELECT varchar(100)DECLARE @FROM varchar(100)DECLARE @DynamicSQL1 varchar(8000)DECLARE @DynamicSQL2 varchar(8000)DECLARE @DynamicSQL3 varchar(8000)SET @SELECT = 'SELECT 'SET @FROM = 'FROM DATA_SOURCE'EXEC (@SELECT + @DynamicSQL1 + @DynamicSQL2 + @DynamicSQL3 + @FROM)EXEC (@SELECT +''+ @DynamicSQL1 +''+ @DynamicSQL2 +''+ @DynamicSQL3 +''+ @FROM)[/quote][hr]I'm not sure of the exact problem, but I'd guess it stems from one of two likely sources.1. The @DynamicSQL# variables need an interceding space in order for their concatenation to result in syntactically correct SQL.Try:EXEC (@SELECT + ' ' + @DynamicSQL1 + ' ' + @DynamicSQL2 + ' ' + @DynamicSQL3 + ' ' + @FROM)2. You are trying to concatenate Null values. The solution would be to recast the @DynamicSQL# as a space if the value is Null.

RE: How to Build Dynamic Stored Procedures

Michael Valentine Jones — Tue, 08 Jan 2008 15:31:02 GMT

I made extensive use of stored procedures that created temporary stored procedures for reporting in a data mart application. This reason I did this was because there could be literally hundreds of different variations of the same basic report summarized along different dimensions and levels. The input parameters to the stored procedure specified the grouping/sorting levels that were required, and the stored procedure used that to generate the code.It was easier to write one fairly complex procedure than it was to create hundreds of almost identical, simpler procedures for the different variations in the reports. The biggest advantage was that if there was a bug, I only had to fix it in one procedure instead of hundreds.

RE: How to Build Dynamic Stored Procedures

markybse — Tue, 08 Jan 2008 15:00:19 GMT

Thank you for the quick reply and yes, I tested your code and it works fine on my system so, in theory, I should have a syntax error.Taking my example below, I mad a change...all the way down, the first exec line fails and the second exec line works. I am at a loss as to why.DECLARE @SELECT varchar(100)DECLARE @FROM varchar(100)DECLARE @DynamicSQL1 varchar(8000)DECLARE @DynamicSQL2 varchar(8000)DECLARE @DynamicSQL3 varchar(8000)SET @SELECT = 'SELECT 'SET @FROM = 'FROM DATA_SOURCE'EXEC (@SELECT + @DynamicSQL1 + @DynamicSQL2 + @DynamicSQL3 + @FROM)EXEC (@SELECT +''+ @DynamicSQL1 +''+ @DynamicSQL2 +''+ @DynamicSQL3 +''+ @FROM)

RE: How to Build Dynamic Stored Procedures

Scott Coleman — Tue, 08 Jan 2008 13:10:07 GMT

Seems to work for me in both SQL 2000 and SQL 2005. This code creates a 16,000+ character command that returns 16 996-char fields and one 3-char field as results.DECLARE @v1 VARCHAR(8000), @v2 VARCHAR(8000)SET @v1 = '''' + LEFT(REPLICATE('1234567890', 100), 996) + ''', 'SET @v2 = REPLICATE(@v1, 8)SET @v1 = @v2EXEC ('SELECT ' + @v1 + @v2 + '''xyz''')Maybe there's a syntax error in your command.

RE: How to Build Dynamic Stored Procedures

markybse — Tue, 08 Jan 2008 11:38:04 GMT

Does anyone actually test this code or is most of this stuff just rumors?According to most of the information I should be able to create multiple variables...DECLARE @SELECT varchar(100)DECLARE @FROM varchar(100)DECLARE @DynamicSQL1 varchar(8000)DECLARE @DynamicSQL2 varchar(8000)DECLARE @DynamicSQL3 varchar(8000)SET @SELECT = 'SELECT 'SET @FROM = 'FROM DATA_SOURCE'EXEC (@SELECT + @DynamicSQL1 + @DynamicSQL2 + @DynamicSQL3 + @FROM)The exec statement only accesses the first 8000 characters and then shuts down.What am I missing/

RE: How to Build Dynamic Stored Procedures

Robert Davis — Mon, 18 Dec 2006 19:37:00 GMT

On a related note, we have a project in development for our internal account managers and tech support team to be able to run any select query they want against our databases to help them resolve issues. The devloper asked me how she could go about making sure they didn't inadvertantly perform some query other than a select. My reply was to create a user account that had all privelages denied except for select and execute the procedure as that user account. Bear in mind that the AM's and tech support will be using a web app that is using an account whose only rights is to execute stored procedures.

This is the real code that was finally used, but is the initial test code I sent to the developer for testing how it would work. Bear in mind that this is SQL 2005 code.

Use master

-- Create server login for test

Create Login DBDataReaderOnly With Password = 'PeasPorridgeCold'

Use DemoDatabase

-- Create database user for login

Create User DBDataReader For Login DBDataReaderOnly With Default_Schema = dbo

-- Create databse role for easy assignment of permissions

Create Role db_DataReaderOnly

-- Grant select rights

Grant Select To db_DataReaderOnly

-- Deny all other permissions

Deny Execute, Insert, Update, Delete, References, Alter, Take Ownership, View Definition To db_DataReaderOnly

-- Assign user to the database role

Exec sp_addrolemember 'db_DataReaderOnly', 'DBDataReader'

-- Create a test procedure

Create Procedure dbo.USP_DynamicDataTest

@SQL nvarchar(max)

With Execute As 'DBDataReader'

Exec sp_executesql @SQL

-- Test new account, run each command individually

Exec dbo.USP_DynamicDataTest @SQL = 'Select * From TestTable with(nolock)'

Exec dbo.USP_DynamicDataTest @SQL = 'Delete Top (1) From TestTable'

Exec dbo.USP_DynamicDataTest @SQL = 'Drop Table TestTable'

RE: How to Build Dynamic Stored Procedures

WILLIAM MITCHELL — Mon, 18 Dec 2006 18:34:00 GMT

The "best" solution is not to use dynamic sql unless there is absolutely no other way to accomplish the desired result.

Dynamic sql is a total pain to maintain. I've slogged through other people's dynamic code and many times found a better way.

If you must write dynamic sql, you should embed a sample of the resulting sql statement in the sp, to help illustrate what's going on. A very simple example of that would be:

-- use the next invoice # as the seed, might look like:-- ALTER TABLE tblFM_MPI ADD InvoiceNumber int IDENTITY (45969, 1)SET @sql = N'ALTER TABLE tblFM_MPI DROP COLUMN InvoiceNumber'EXEC ( @sql )SET @sql = N'ALTER TABLE tblFM_MPI ADD InvoiceNumber int IDENTITY (' + CAST(@NextInv AS varchar(6)) + ', 1)'EXEC ( @sql )

RE: How to Build Dynamic Stored Procedures

Robert Davis — Mon, 18 Dec 2006 13:23:00 GMT

>> A complex statement is difficult to read when it is pieced together with a bunch of CHAR, CAST, and CONVERT fuctions.

I disagree. I think it is easier to read considering I don't have to go to another part of the procedure and find the Replace statements to figure out what someone is trying to do with a particular portion of the dynamic statement.

Of course, the best solution to the nvarchar 4000 character limit is to use nvarchar(max) in SQL Server 2005.

RE: How to Build Dynamic Stored Procedures

Scott Coleman — Thu, 22 Dec 2005 09:29:00 GMT

NOLOCK aka READ UNCOMITTED aka "dirty read"

Allows reading a table in spite of any uncommitted transactions that may be in progress on other connections.

Pro: It saves time by not creating locks, and by not waiting for or blocking other transactions. It only requires a schema lock on the table, rather than creating locks for all the rows/pages/extents that are read. It ignores exclusive locks owned by other connections instead of waiting for those transactions to complete.

Cons: You can read data from partially completed transactions, then those transactions may be rolled back and the data disappears. For instance, you have a query WITH(NOLOCK) to sum the sales for each salesman to calculate commission payments. Just before you run this, someone begins a transaction showing they sold the Brooklyn Bridge for $2 billion. After your comission query runs and calculates a huge bonus for this guy, the transaction is rolled back and there is no trace of the data to explain what happened to the accountants.

It is very useful for reporting queries on production databases, if you know that either the data you're reading is static or you don't mind having counts and totals being a little off. A history table that sees INSERTs but no UPDATEs for example, or maybe a query summarizing last week's data when only today's data might be volatile. An inventory query that is checking whether there are less than 5000 #8 wood screws on hand to decide when to reorder is not going to notice the difference between 3875 and 3750.

It obviously is not recommended for accounting, or any application where exact numbers are required. An inventory query that is a little fuzzy about whether there should be 2 or 3 Ferraris on the showroom floor is a bad idea.

RE: How to Build Dynamic Stored Procedures

Mr Construction — Wed, 21 Dec 2005 16:30:00 GMT

will someone please help and o'll wondering soul out by giving me a human version of (NOLOCK)??

THANKS!

ERIK

RE: How to Build Dynamic Stored Procedures

Mr Construction — Wed, 21 Dec 2005 16:30:00 GMT

will someone please help and o'll wondering soul out by giving me a human version of (NOLOCK)??

THANKS!

ERIK

RE: How to Build Dynamic Stored Procedures

Scott Coleman — Wed, 21 Dec 2005 16:23:00 GMT

One more time, SQL Server is happy to let you use multi-line strings with embedded tabs. The end-of-line is just white space to the SQL syntax analyzer, you don't have to close the string on each line and start the next with "+".

Instead of using all that concatenation, just use:

set @query = 'select column1, column2from table1 (nolock)'

I can't enter a tab in this text box so there are four spaces before 'column2' instead of a tab, but you can use the tab character in Query Analyzer.

RE: How to Build Dynamic Stored Procedures

Stuart Anderson-198975 — Wed, 21 Dec 2005 07:05:00 GMT

Thanks for the article. I am currently re-writing a chunk of my code because, in this case, it's going to give me a huge performance gain. The format I have settled on is:

DECLARE @query nvarchar(4000), -- // nvarchar using sp_executesql

@LN char(1),

@TB char(1),

@LT char(2)

set @LN = char(10) -- // Line Feed

set @TB = char(9) -- // Tab

set @LT = char(10) + char(9) -- // Line feed + Tab

set @query = ''

set @query = @query

+ 'select column1,' + @LT

+ 'column2' + @LN

+ 'from table1 (nolock)'

It is a little neater than having char() in lots of places.

RE: How to Build Dynamic Stored Procedures

Mr Construction — Tue, 20 Dec 2005 12:29:00 GMT

I really enjoyed the article.

There is huge benefit for dynamic stored procedures in when vb.net and sql2000 work togeather;.

thanks!

erik

RE: How to Build Dynamic Stored Procedures

Scott Coleman — Mon, 19 Dec 2005 15:38:00 GMT

Presumably you're getting the index info from sysindexes? Join to sysobjects to get the owner. It might look something like this:

select 'DBCC DBREINDEX (''' + quotename(user_name(so.uid)) + '.' + quotename(so.name) + ''',''' + rtrim(si.name) + '''[, fillfactor]'from sysindexes siinner join sysobjects so on so.id = si.idwhere xtype = 'U' and indexproperty(si.id, si.name, 'IsStatistics')=0 and indexproperty(si.id, si.name, 'IsHypothetical')=0

RE: How to Build Dynamic Stored Procedures

Matt Klein-228552 — Mon, 19 Dec 2005 12:51:00 GMT

In a tangential topic, I've built a process using dynamic sql which reads through all the databases on my servers to determine which indexes need reorganizing. Works great on all our servers, except for the ones with outside apps, where there the table owners are not dbo. I'm attempting to adapt the process to append the database owner name to the tablename -- building a variable ahead of time -- however I get a compile error whenever I have a variable in the "from" clause.

Is there a proper way to handle this that I'm missing?

RE: How to Build Dynamic Stored Procedures

Scott Coleman — Mon, 19 Dec 2005 10:00:00 GMT

I have learned to detest code that builds dynamic SQL by repeated concatenation, especially with char functions for line breaks and tabs. A complex statement is difficult to read when it is pieced together with a bunch of CHAR, CAST, and CONVERT fuctions.

SQL is perfectly happy to interpret multiline strings with all line breaks and tabs included. I create a template of the complete SQL statement with tags for all variable parts, then use REPLACE functions to handle the modifications. This is especially useful when one variation requires changes in the field list, tables, and where clause. Another advantage of REPLACE is it can do implicit conversions to string of integers and avoid '...' + CAST(x AS VARCHAR) + '...'.

-- Basic templateSET @sql = 'SELECT fld1, fld2<fields> FROM tbl1 <joins> WHERE tbl1.xyz=0 <filters>'

-- One logical test controls additional fields, joins, and filter clauses-- All replaceable tokens are duplicated in the replacement strings to allow further actionsIF modification1 = 1 SET @sql = REPLACE(REPLACE(REPLACE(@sql, '<fields>', ', fld3, fld4<fields>'), '<joins>', 'INNER JOIN tbl2 ON tbl1.fld1 = tbl2.fld1 <joins>'), '<filters>', ' AND tbl2.pqr IS NOT NULL <filters>')-- Further modifications using the same logic-- Strip out any remaining tokens SET @sql = REPLACE(REPLACE(REPLACE(@sql, '<fields>', ''), '<joins>', ''), '<filters>', '')

RE: How to Build Dynamic Stored Procedures

pkmccarthy — Sat, 17 Dec 2005 17:38:00 GMT

all, you can concat in sp_executesql so you can go beyond the 4k limit...

RE: How to Build Dynamic Stored Procedures

Carl Federl — Fri, 16 Dec 2005 17:31:00 GMT

FYI, see the article "The Curse and Blessings of Dynamic SQL" by Erland Sommarskog, SQL Server MVP at http://www.sommarskog.se/dynamic_sql.htmlTo workaround the the size limitation, one approach is to use views. As the dynamic SQL uses the view, it is often simplier, easier to debug and is often shorter than the 4000 character length restriction of sp_executesql

RE: How to Build Dynamic Stored Procedures

Calvin Lawson — Fri, 16 Dec 2005 13:39:00 GMT

I like the formatting hints in your article. I would have saved some trial and error time if I'd read this a few years ago.Personally, I've used dynamic SQL everywhere, due to the unique requirements of supporting a vendors db (each client in it's own self similar database).Dynamic SQL that gets concatenated and executed at run time always proves to be hard to debug (and tends to have bugs), and testing all possible permutations is hard to accomplish. It's especially hard to figure out where data issues stopped the process.I've been doing something similar to JIT (Just in time compile), by dynamically creating permanent stored procedures, then executing those procedures the next time the same query is run. It becomes a little more difficult when dealing with a large number of parameters, but as long as you keep data and metadata separate you are golden (IE: one procedure for searching with first and last name only, but not one procedure for searching for firstname = calvin and lastname = lawson).Maybe I should submit an article on that. It answers most of the complaints about dynamic SQL.cl

RE: How to Build Dynamic Stored Procedures

guenter strubinsky — Fri, 16 Dec 2005 12:48:00 GMT

See also:http://jtds.sourceforge.net/apiCursors.html

RE: How to Build Dynamic Stored Procedures

K. Brian Kelley — Fri, 16 Dec 2005 09:55:00 GMT

Yes, input validation. You trap for the known mechanisms for SQL Injection. For instance look for single quotes coming in and double 'em. Watch for the --. Most of all, do validation on variable type where possible. For instance, if you know the field is supposed to be an integer, test it. Things of that sort. Basically the same sort of validation tests you code into a web app you could put in UDFs and test the parameters coming in.

RE: How to Build Dynamic Stored Procedures

Peter DeBetta — Fri, 16 Dec 2005 09:52:00 GMT

Actually, both are part of formatting the output (when printing the SQL) that do not affect the actual executed dynamic SQL.

However, I usually do this a little differently. First of all, I use CRLF and not just LF: CHAR(13) + CHAR(10)

Also, I assign these to a variable early in the procedure to prevent mistakes and from having to type them repeatedly, as shown here:

DECLARE @CRLF char(2), @TAB char(1)SET @CRLF = CHAR(13) + CHAR(10)SET @TAB = CHAR(9)

Now you not only have a nicer, self-documenting variable, but also, if you mistype the variable, the compiler catches it, but if you mistype the CHAR function values (9, 10, or 13) that won't get caught at compile time.

--Peter

RE: How to Build Dynamic Stored Procedures

Pavel Lstiburek — Fri, 16 Dec 2005 09:10:00 GMT

Is there any "simple" method how to protect (how to test) the dynamic SP against SQL injection ? We stoped using dynamic SP because we was afraid of this problem.Pavel Lstiburek

RE: How to Build Dynamic Stored Procedures

Phil.Nicholas — Fri, 16 Dec 2005 08:23:00 GMT

Its just a formating thing - char(10) = newline , char(9) = tab, the article mentions using char(10)'s for formatting

RE: How to Build Dynamic Stored Procedures

Scott D. Smith — Fri, 16 Dec 2005 08:14:00 GMT

Excuse my ignorance, but what are those CHAR(10) things doing? How does that work? Please explain.

RE: How to Build Dynamic Stored Procedures

Phil.Nicholas — Fri, 16 Dec 2005 08:01:00 GMT

Heres a stored procedure I wrote sometime ago to generate get, add or update and delete stored procedures so you get the performance benefit of sps. As the naming convention is common you can generate class code etc. against them. I also use an sp to generate c# or vb class code to interface with the stored procedures. Doing data access in this way is very quick and tends to be bug free first time which is nice, possibly could be extended with your application to insert code specific to tables so that more table specific logic could be included. nb it also needs a stored procedure to retrieve records based on foreign keys.

Phil Nicholas

if exists(select * from sysobjects where name = 'sp_createStoredProcedures')drop procedure sp_createStoredProceduresGOcreate procedure sp_createStoredProcedures(@tablename varchar(255), @Encryption bit = 1)AS

BEGIN

DECLARE @SQL varchar(8000)

---------------------------select sp SET NOCOUNT ON

create table #temp(id int not null identity (1,1), txt varchar(8000))

insert #temp (txt) select '--stored procedure to get an individual ' + @tablename + ' record' + @tablename

insert #temp (txt) select 'if exists(select * from sysobjects where name = ''spGet_' + @tablename + ''')'

insert #temp (txt) select 'drop procedure spGet_' + @tablename

insert #temp (txt) select 'GO'

insert #temp (txt) select 'create procedure spGet_' + @tablename

insert #temp (txt) select char(9) + '@' + replace(c.name,' ', '_') + ' ' + t.name + ',' from sysindexes i join sysobjects o on o.ID = i.id join sysindexkeys ik on ik.indid = i.indid and ik.id = i.id join syscolumns c on c.id = ik.id and ik.colid = c.colid join systypes t on c.xtype = t.xtype where o.name = @tablename AND (I.STATUS & 2048) = 2048 order by ik.keyno asc

if @@rowcount > 0 update #temp set txt = substring(txt,1,len(txt)-1) where [id] = @@identity

if @Encryption = 1 insert #temp (txt) select 'WITH ENCRYPTION'

insert #temp (txt) select 'AS'

insert #temp (txt) select 'SELECT '

insert #temp (txt) select char(9) + '[' + c.name + '],' from sysobjects o join syscolumns c on o.id = c.id where o.name = @tablename order by colid asc

if @@rowcount > 0 update #temp set txt = substring(txt,1,len(txt)-1) where [id] = @@identity

insert #temp (txt) select 'FROM '

insert #temp (txt) select CHAR(9) + @tablename

insert #temp (txt) select 'where '

insert #temp (txt) select char(9) + '[' + c.name + '] = @' + replace(c.name,' ', '_') + ' and' from sysindexes i join sysobjects o on o.ID = i.id join sysindexkeys ik on ik.indid = i.indid and ik.id = i.id join syscolumns c on c.id = ik.id and ik.colid = c.colid join systypes t on c.xtype = t.xtype where o.name = @tablename AND (I.STATUS & 2048) = 2048 order by ik.keyno asc

if @@rowcount > 0 update #temp set txt = substring(txt,1,len(txt)-4) where [id] = @@identity

insert #temp (txt) select 'GO'

insert #temp (txt) values ('')

---------------------------delete sp insert #temp (txt) select '--stored procedure to delete an individual ' + @tablename + ' record' + @tablename

insert #temp (txt) select 'if exists(select * from sysobjects where name = ''spDelete_' + @tablename + ''')'

insert #temp (txt) select 'drop procedure spDelete_' + @tablename

insert #temp (txt) select 'GO'

insert #temp (txt) select 'create procedure spDelete_' + @tablename

if @@rowcount > 0 update #temp set txt = substring(txt,1,len(txt)-1) where [id] = @@identity

if @Encryption = 1 insert #temp (txt) select 'WITH ENCRYPTION'

insert #temp (txt) select 'AS'

insert #temp (txt) select 'DELETE '

insert #temp (txt) select 'FROM '

insert #temp (txt) select CHAR(9) + @tablename

insert #temp (txt) select 'WHERE '

insert #temp (txt) select char(9) + '[' + c.name + '] = @' + replace(c.name,' ', '_') + ' AND' from sysindexes i join sysobjects o on o.ID = i.id join sysindexkeys ik on ik.indid = i.indid and ik.id = i.id join syscolumns c on c.id = ik.id and ik.colid = c.colid join systypes t on c.xtype = t.xtype where o.name = @tablename AND (I.STATUS & 2048) = 2048 order by ik.keyno asc

if @@rowcount > 0 update #temp set txt = substring(txt,1,len(txt)-4) where [id] = @@identity

insert #temp (txt) select 'GO'

insert #temp (txt) values ('')

--sp_help tblpupil_key_indicators---------------------------update/insert sp insert #temp (txt) select '--stored procedure to insert/add an individual ' + @tablename + ' record' + @tablename

insert #temp (txt) select 'if exists(select * from sysobjects where name = ''spAddUpdate_' + @tablename + ''')'

insert #temp (txt) select 'drop procedure spAddUpdate_' + @tablename

insert #temp (txt) select 'GO'

insert #temp (txt) select 'create procedure spAddUpdate_' + @tablename

insert #temp (txt) select char(9) + '@' + replace(c.name,' ', '_') + ' ' + t.name + case when t.name in ('varchar', 'nvarchar', 'char', 'nchar') then ' (' + cast(c.length as varchar(20)) + ')' when t.name in ('decimal') then ' (' + cast(c.prec as varchar(20)) + ',' + cast(c.scale as varchar(20)) + ')' else '' end + ',' from sysobjects o join syscolumns c on c.id = o.id join systypes t on c.xtype = t.xtype where o.name = @tablename and NOT c.name LIKE '%_MOD_USER' and NOT c.name like '%_MOD_DATE' order by c.colid asc

if @@rowcount > 0 update #temp set txt = substring(txt,1,len(txt)-1) where [id] = @@identity

if @Encryption = 1 insert #temp (txt) select 'WITH ENCRYPTION'

insert #temp (txt) select 'AS'

--empty sql string set @SQL = ''

--------do check for keys

--change to autoval key or primary keyif exists(select * from sysobjects o join syscolumns c on c.id = o.id where not autoval is null and o.name = @tablename) select @SQL = @SQL + 'coalesce(@' + replace(c.name,' ', '_') + ',0) = 0 and ' from sysobjects o join syscolumns c on c.id = o.id where not c.autoval is null and o.name = @tablenameelse select @SQL = @SQL + 'coalesce(@' + replace(c.name,' ', '_') + ',0) = 0 and ' from sysindexes i join sysobjects o on o.ID = i.id join sysindexkeys ik on ik.indid = i.indid and ik.id = i.id join syscolumns c on c.id = ik.id and ik.colid = c.colid join systypes t on c.xtype = t.xtype where o.name = @tablename AND (I.STATUS & 2048) = 2048 order by ik.keyno asc

if @@rowcount > 0 begin set @SQL = 'IF ' + substring(@SQL,1,len(@SQL)-4)

insert #temp (txt) select @SQL end

insert #temp (txt) select 'BEGIN'

--empty sql string set @SQL = ''

--do insert col list select @SQL = @SQL + '[' + c.name + '],' from sysobjects o join syscolumns c on c.id = o.id join systypes t on c.xtype = t.xtype where o.name = @tablename and NOT c.name LIKE '%_MOD_USER' and NOT c.name like '%_MOD_DATE' AND COALESCE(AUTOVAL,0)=0 order by c.colid asc

--insert param list if @SQL<>'' INSERT #TEMP SELECT char(9) + 'INSERT ' + @TableName + '(' + substring(@SQL,1, len(@SQL)-1) + ')'

--empty sql string set @SQL = ''

--do insert col list select @SQL = @SQL + '@' + replace(c.name,' ', '_') + ',' from sysobjects o join syscolumns c on c.id = o.id join systypes t on c.xtype = t.xtype where o.name = @TableName and NOT c.name LIKE '%_MOD_USER' and NOT c.name like '%_MOD_DATE' AND COALESCE(AUTOVAL,0)=0 order by c.colid asc

--insert param list if @SQL<>'' INSERT #TEMP (txt) SELECT char(9) + 'VALUES (' + substring(@SQL,1, len(@SQL)-1) + ')'

if EXISTS( select * from sysobjects o join syscolumns c on c.id = o.id where o.name = @tablename AND COALESCE(AUTOVAL,0)>=1) insert #temp select 'SELECT @@IDENTITY'

insert #temp (txt) select 'END'

INSERT #TEMP (txt) SELECT 'ELSE'

INSERT #TEMP (txt) SELECT 'UPDATE ' + @tablename + ' SET '

INSERT #TEMP (txt) select char(9) + '[' + c.name + '] = @' + replace(c.name,' ', '_') + ',' from sysobjects o join syscolumns c on c.id = o.id where o.name = @TableName and NOT c.name LIKE '%_MOD_USER' and NOT c.name like '%_MOD_DATE' and not exists (select * from sysindexes i join sysindexkeys ik on ik.id = i.id and ik.indid = i.indid where (i.status & 2048) = 2048 and ik.colid = c.colid and i.id = o.id) and c.autoval is null order by c.colid asc--select * from sysindexkeys if @@rowcount > 0 if @SQL<>'' update #temp set txt = substring(txt,1,len(txt)-1) where [id] = @@identity

INSERT #TEMP (txt) SELECT 'WHERE'

insert #temp (txt) select char(9) + '[' + c.name + '] = coalesce(@' + replace(c.name,' ', '_') + ',0) and ' from sysindexes i join sysobjects o on o.ID = i.id join sysindexkeys ik on ik.indid = i.indid and ik.id = i.id join syscolumns c on c.id = ik.id and ik.colid = c.colid join systypes t on c.xtype = t.xtype where o.name = @tablename AND (I.STATUS & 2048) = 2048 order by ik.keyno asc

if @@rowcount > 0 update #temp set txt = substring(txt,1,len(txt)-4) where [id] = @@identity

insert #temp (txt) select 'GO'

insert #temp (txt) values ('')

select txt from #temp order by id asc

drop table #temp

SET NOCOUNT OFFENDGOexec sp_createStoredProcedures 'sysobjects'

RE: How to Build Dynamic Stored Procedures

Daniela-267581 — Fri, 16 Dec 2005 07:28:00 GMT

I used a lot dynamic stored procedure.I had to, because they aren't really my first choice- I find it hard to read and to debug.

Anyway it was a good article.

RE: How to Build Dynamic Stored Procedures

JL Dominguez-246595 — Fri, 16 Dec 2005 07:09:00 GMT

I don't see any advantage to using dynamic store procedures. Also, it would be more difficult to tune and troubleshoot dynamic stored procedures if they constantly change. In addition, data validation should be done at the client side and not on the server side to save yourself a round trip.

RE: How to Build Dynamic Stored Procedures

cliffb — Fri, 16 Dec 2005 05:51:00 GMT

When printing out the SQL statements, CHAR(10) does indeed make it easier to read. Maintenance on any sproc that uses dynamic sql however, depends on the complexity of the code.

From the examples given, I see no reason to use dynamic sql. There is no top clause, you do not need to dynamically determine a table name. If your production stored procedurees are setup like this, I would redesign them to take advantage of the sql engine.

RE: How to Build Dynamic Stored Procedures

Oli Wilkinson — Fri, 16 Dec 2005 03:37:00 GMT

As someone who's had to maintain code embedded with char(10)s everywhere I can honestly say that the advice in the first section of this article is absolute tripe. It does NOT make it easier to read, nor does it make it easier to maintain.

RE: How to Build Dynamic Stored Procedures

mark hutchinson — Thu, 15 Dec 2005 22:48:00 GMT

1. There is a redundant condition check in your example. Once we start building the WHERE clause, we've already passed the first check, which would have prevented our getting down to the second check.

IF @LastName <> '' AND @NameID <> 0   ...   RETURNEND...--Begin building WHERE clauseIF @LastName <> '' OR @NameID <> 0...

2. As has already been mentioned, performance of a dynamic SP will be poorer than a static SP. It would make more sense to generate the SP externally and then import them in a SQL stream, or to hook directly into SQL Server and do it programmatically. There's been a lot of work done in the realm of code generation (heck, entire system generation) that could serve as a model for this. As with most things, we trade performance for flexibilty and vice versa.

3. It should be noted that SQLServer2005 removes the 8000 character limit. This alone should be worth the migration.

4. How does the following statement provide SQL injection attack protection?SET @LastName = REPLACE (@LastName, '''', '''''')

RE: How to Build Dynamic Stored Procedures

Robert W Marda — Fri, 25 Apr 2003 10:30:00 GMT

Unless you manage to get an execution plan that can be reused (and usually you don't) then you can say there is no performance benifit.For me, the benefit is that no changes have to be made to the web site or internal applications for most changes in the stored procedure. Particularly useful when I find a quicker way to get the same result set.You could realize a slight performance benifit because you would not have to send thousands of characters from the client to the server. With the dynamic SP you simply send the call and the code is built server side and then immediately executed.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: How to Build Dynamic Stored Procedures

kevkaz — Thu, 24 Apr 2003 09:41:00 GMT

Is there a significant performance benefit to building dynamic SQL in a stored procedure instead of from within code?

RE: How to Build Dynamic Stored Procedures

Rajesh Patavardhan — Thu, 24 Apr 2003 09:38:00 GMT

One more difference between EXEC and sp_executesql is that first one does not allow you to run parameterized queries.sp_executesql allows you to parameterize the sql and inturn helps in caching and re-use of execution plans.The issue related to dynamic sqls, parameterized sqls and memory is discussed in following linkhttp://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=11407

RE: How to Build Dynamic Stored Procedures

Robert W Marda — Thu, 24 Apr 2003 08:39:00 GMT

I don't think we currently use sp_executeSQL and I have used it only a few times. So I am not an expert with its use. We have many dynamic stored procedures in production that have more than 4,000 characters in the query built and some even exceed 8,000 characters. So being limited to 4,000 is not an option. I tried to use the same technique I showed in example 2 of my article but couldn't get it to work so it appears there is no way to extend the max beyond 4,000 characters, where as with the EXEC command I don't know of a limit to how many varchar(8000) variables you can add together at the time you execute the code and so your code is theoretically not limited. The trade off is that you lose benefit of most if not all caching done by SQL Server. For us this hasn't been a problem since most of what we have in production completes within a few seconds of execution with or without caching. It will depend on your individual situation if you can accept this type of performance or not.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: How to Build Dynamic Stored Procedures

bumbler — Thu, 24 Apr 2003 08:14:00 GMT

Just a quick note: when building dynamic SQL in a sproc, SQL Server will not usually cache the query plan unless you use one of the built-in stored procedures to execute the code. So, instead of simply writing this:exec (@strSQL)It is actually better over the long run to write this:exec sp_executeSQL @strSQLThe one drawback here is that the sproc, sp_executeSQL, expects its parameter as nVarchar, so you will be limited to 4000 characters instead of 8000. See more than you need to know in BOL.Thanks,DH.

How to Build Dynamic Stored Procedures

Robert W Marda — Fri, 18 Apr 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/howtobuilddynamicstoredprocedures.asp