Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

How to Build Dynamic Stored Procedures Expand / Collapse
Author
Message
Posted Friday, December 16, 2005 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2008 1:43 PM
Points: 165, Visits: 19

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.

 






Daniela
Post #244838
Posted Friday, December 16, 2005 8:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 8, 2013 5:22 AM
Points: 119, Visits: 9

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_createStoredProcedures
GO
create 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

 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 '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 key
if 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 = @tablename
else
 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 OFF
END
GO
exec sp_createStoredProcedures 'sysobjects'


 




Phil Nicholas
Post #244859
Posted Friday, December 16, 2005 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 6:02 AM
Points: 8, Visits: 18
Excuse my ignorance, but what are those CHAR(10) things doing? How does that work? Please explain.
Post #244865
Posted Friday, December 16, 2005 8:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 8, 2013 5:22 AM
Points: 119, Visits: 9
Its just a formating thing - char(10) = newline , char(9) = tab, the article mentions using char(10)'s for formatting 


Phil Nicholas
Post #244871
Posted Friday, December 16, 2005 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:20 AM
Points: 9, Visits: 17
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
Post #244888
Posted Friday, December 16, 2005 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 4, 2014 7:59 AM
Points: 40, Visits: 49

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

Post #244905
Posted Friday, December 16, 2005 9:55 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #244907
Posted Friday, December 16, 2005 12:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:32 AM
Points: 4, Visits: 82
See also:

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

Post #244953
Posted Friday, December 16, 2005 1:39 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102
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




Signature is NULL
Post #244967
Posted Friday, December 16, 2005 5:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 2,281, Visits: 4,241
FYI, see the article "The Curse and Blessings of Dynamic SQL" by Erland Sommarskog, SQL Server MVP at
http://www.sommarskog.se/dynamic_sql.html

To 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


SQL = Scarcely Qualifies as a Language
Post #245009
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse