Nesting Stored Procedures
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/nestingstoredprocedures.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Also another reason for nesting stored procs is to avoid potential recompilation.
Eg, you might have a stored proc with 5 lines of code that can often cause recompilation...
It is better to have SQL server recompile a 20 line "child" SP (which contains those 5 lines) then to have SQL server compile a single 100 line SP repeatedly.
I like the other reason in the article though - one point of call for very similar functions 🙂
-
I use nested stored procedures for the same reasons.
I quite often have
IF .....
INSERT #Tbl (Fields)
EXEC usp_SelectProc1 @Arg1, Arg2...Argn
ELSE
INSERT #Tbl (Fields)
EXEC usp_SelectProc2 @Arg1, Arg2...Argn
type routines.
The problem I have is that sometimes I want the usp_SelectProc stored procedures to use the INSERT/Exec method but you cannot nest this sort of functionality.
The comments I would make are that if you are going to nest stored procedures
- You need good documentation/comments otherwise the dependencies can be hard to keep track of.
- Always use field lists in your INSERT statements just incase the exec usp_SelectProc is altered to returb additional fields.
-
I'm thinking of implementing centralised validation of input at a database level for protecting against sql injection. I do not trust this only be done at application level.
Nested stored procedures might be a way to do this.
Any thoughts on this?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I take it that you are talking about a web application?
I have found that tasks that are too small to affect the performance of normal apps become massive overheads on web apps simply because of the amount of traffic a big site can have.
To give an example, my company stress tested one of our sites with varying numbers of simultaneous connections. The site was vastly more successful than was anticipated and we had to strip out some of the error reporting from our stored procedures in order to maintain performance. Under normal conditions this would have had no measurable affect, but with the sheer volumes of users it became an issue.
I would tend to put input validation in the client front end and in a middle tier on the web server probably enclosed within a DLL.
Validation of parameters (for web apps), once they have reached your database server should be kept as simple and as light weight as possible.
-
quote:
I take it that you are talking about a web application?[{quote]
GUI is displayed in Browser
quote:
I have found that tasks that are too small to affect the performance of normal apps become massive overheads on web apps simply because of the amount of traffic a big site can have.No issue, because at the moment it is an internal app with a VERY limited number of user. It might be opened to affiliated companies in the future, but even then there are only about <50 regular users.
quote:
To give an example, my company stress tested one of our sites with varying numbers of simultaneous connections. The site was vastly more successful than was anticipated and we had to strip out some of the error reporting from our stored procedures in order to maintain performance. Under normal conditions this would have had no measurable affect, but with the sheer volumes of users it became an issue.Just out of interest. About how many users do you speak?
I would tend to put input validation in the client front end and in a middle tier on the web server probably enclosed within a DLL.
Validation of parameters (for web apps), once they have reached your database server should be kept as simple and as light weight as possible.
I hear you, but in case it is opened to affiliated companies it could happen that they develop their own GUI to access my db. And I don't know what comes then. Therefore my reflections.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
How many users? In excess of 30,000.
What you could do is write your GUI so that your main user interface is in a bald HTML file and that file is called in the context of your page furniture.
If someone else wants to write their own GUI they can do it but they are limited to specifying the stylesheet and page furniture.
Your server DLL and ASP/PHP/PL or whatever code still does all the work.
-
quote:
What you could do is write your GUI so that your main user interface is in a bald HTML file and that file is called in the context of your page furniture.If someone else wants to write their own GUI they can do it but they are limited to specifying the stylesheet and page furniture.
Hmm,...that sounds like something really worth trying. Do you have any recommendations, useful links, examples.. Or keywords that I can google?
TIA
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
David.Poole:
Can you provide me an example of what you want to do with INSERT/EXEC but can't with nesting stored procedures?
We don't often use INSERT/EXEC and so the focus of my article was not on that.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
ianyates:
Good point. Placing the code that forces recompilation in its own SP should reduce the time spent to recompile.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Frank (A.K.A. a5xo3z1):
The major danger I am aware of from sql injection attacks via data entered by a user and used in a stored procedure is when you dynamically build your queries and then execute them. The best way I know to completely eliminate this type of injection is to use the following code on your character datatypes: SET @LastName = REPLACE (@LastName, '''', '''''')
That code ensures that all characters they enter will remain in the query to be compared against a column and can not execute as its own query.
Other than that I agree that all other forms of validation will be best handled before the input reaches the stored procedures.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Robert,
I don't have the specific example to hand as its in an archived project library.
From what I remember I wrote code to produce a paginated list of records from a database
Basically I had a stored procedure called usp_Paginator which accepted a page number, page length and recordset identifier.
This stored procedure called another stored procedure depending on the recordset identifier which returned a recordset which I used to populate a temporary table.
I came across and instance where I wanted to say
INSERT MyTable
exec usp_Proc @arg
The problem was that usp_Proc contained code of this type itself.
If you try and do this then SQL returns an error message warning you that you cannot nest these sorts of statements.
As with all things SQL my INSERT/Exec method was a case of AN answer rather than THE answer.
-
Hi Robert,
quote:
The major danger I am aware of from sql injection attacks via data entered by a user and used in a stored procedure is when you dynamically build your queries and then execute them. The best way I know to completely eliminate this type of injection is to use the following code on your character datatypes: SET @LastName = REPLACE (@LastName, '''', '''''')That code ensures that all characters they enter will remain in the query to be compared against a column and can not execute as its own query.
Other than that I agree that all other forms of validation will be best handled before the input reaches the stored procedures.
Agreed, I'm trying to avoid dynamic SQL for other than administrative tasks and to validate as much as possible at app level. But there is still a small percentage of uncertainty to deal with.
You have to do more than just REPLACE (@LastName, '''', '''''')
I'm sure you know this articles from http://www.appsecinc.com named Manipulating Microsoft SQL Server Using SQL Injection. Or Advanced SQL Injections in SQL Server Applications by http://www.nssoftware.com.
But we're moving off-topic...
One thing that can be stated is that you can use nested procs for this reason, or? If you do so, that's another question
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Correction,
the second link should be called http://www.ngssoftware.com.
Sorry!
Cheers,
Frank
Edited by - a5xo3z1 on 05/22/2003 09:26:40 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
innovative thinking is not at all there
just try this real nesting
create proc nest_sp_dec
@kount int
as
begin
select @kount
select @kount=@kount-1
if @kount >0
exec nest_sp_dec @kount
end
and execute it with
nest_sp_dec 10
How is it dude
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply