SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Build Dynamic Stored Procedures


How to Build Dynamic Stored Procedures

Author
Message
Daniela-267581
Daniela-267581
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 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
Phil.Nicholas
Phil.Nicholas
SSC Eights!
SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)

Group: General Forum Members
Points: 801 Visits: 10

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
Scott D. Smith
Scott D. Smith
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 18
Excuse my ignorance, but what are those CHAR(10) things doing? How does that work? Please explain.
Phil.Nicholas
Phil.Nicholas
SSC Eights!
SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)

Group: General Forum Members
Points: 801 Visits: 10
Its just a formating thing - char(10) = newline , char(9) = tab, the article mentions using char(10)'s for formatting


Phil Nicholas
Pavel Lstiburek
Pavel Lstiburek
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 21
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
Peter DeBetta
Peter DeBetta
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 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


K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (43K reputation)

Group: Moderators
Points: 43624 Visits: 1917
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
@‌kbriankelley
guenter strubinsky
guenter strubinsky
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 82
See also:

http://jtds.sourceforge.net/apiCursors.html
Calvin Lawson
Calvin Lawson
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3556 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
Carl Federl
Carl Federl
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11580 Visits: 4354
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search