SQLServerCentral / Discuss Content Posted by Robert Marda / Article Discussions / Article Discussions by Author / Nesting Stored Procedures / Latest Posts

RE: Nesting Stored Procedures

ntaylor-739763 — Thu, 12 Jun 2008 15:02:51 GMT

I understand that I can use stored procedures from another stored procedure quite happily and merrily and have done so many times in the last 2 months.What I'm really struggling with, though, is how to get a handle on the results from said stored procedure within the calling procedure.A stored procedure can return a results table, a scalar value or nothing and VB handles those result sets perfectly well.But when I try to use those exact same procedures (and let's face it; isn't that the main point of a stored procedure?) in another stored procedure I find it very, VERY difficult to access any part of the result except the return code. So far the only stored procedures I've been able to use really properly are "action only" procedures with NO returned results. If I want to see the result of the SP the only option I've found for doing so is to do an insert into a table variable; which has to be defined with every field coming back from the procedure because there's no means to select the fields you want before inserting them. Not only is that an incredible pain in the rear but it's also a huge violation of best practices for any RDBMS because it means you can't add fields to the result table without amending every procedure that calls it.Isn't there some way; even if it means adding an intervening object, that will allow me to use the results of a stored procedure just like the results of a SELECT statement the way VB does?

RE: Nesting Stored Procedures

Robert W Marda — Mon, 04 Jun 2007 11:42:00 GMT

I am glad you have liked my article. Please forgive me for being slow to reply to your comments. I have been busy with many personal matters but hope to be able to write more articles soon. Thank you for your ideas, I will keep them in mind.

RE: Nesting Stored Procedures

einman33 — Fri, 18 May 2007 19:49:00 GMT

Can someone talk about transactions a little? If transaction control resides in the app tier, would this approach increase transactional locking that may be uneeded? Like the article, thanks.

RE: Nesting Stored Procedures

David Rueter — Fri, 18 May 2007 17:29:00 GMT

Robert,

It might be interesting in a follow-up article to consider the implications and pitfalls of explicit transactions in nested stored procedures.

David Rueterdrueter@assyst.com

RE: Nesting Stored Procedures

Calvin Lawson — Fri, 18 May 2007 14:01:00 GMT

I like the way you code, Robert, looking forward to seeing more articles.

RE: Nesting Stored Procedures

dsully — Fri, 18 May 2007 13:34:00 GMT

Just finished reading the article on nested SPs, and it raised a question about a practice I have been following for a few years now.

I write my SPs for one Web application in a common SP with the CommonSP;1, CommonSP;2 etc... and this helps me organize my work by application.

I am wondering if Robert Marda, or others have any thoughts about the wisdom, or not, of this practice.

David

RE: Nesting Stored Procedures

JJ B — Fri, 18 May 2007 08:42:00 GMT

Jeaux wrote: "My question is why couldn't you rewrite the example..." I have a different answer to this question. I once had a stored procedure with a similar structure to the one you gave as an example. The problem was that (if I understand correctly) SQL Server comes up with an optimized plan for the first query it runs the first time it compiles. For example: Let's say SQL Server first compiles and optimizes based on the first query. Later a user runs the proc and the IF takes runs the second query. Now it is possible that the second query runs like a turtle-sooo sloowwwwww.That's the exact problem I had once, and it almost brought my server down. I had a bunch of users running the same proc but hitting one of 5 queries that weren't optimized. I turned it into one master proc calling 6 different baby procs. Voila! Problem solved. It had nothing to do with writing clean code. It was a matter of optimization. Fair disclosure (but any bad explanations are mine): I got this trick off of Kimberly Tripp's site.

RE: Nesting Stored Procedures

Tatsu — Fri, 18 May 2007 07:45:00 GMT

I used nested procedures a lot to reduce server round-trips. If I am loading a .NET DataSet with several tables to be loaded I will call a single procedure that, in turn, EXECUTEs all the other procedures I need to load those tables.

RE: Nesting Stored Procedures

steven powell — Mon, 24 May 2004 08:17:00 GMT

one good trick i know of is related to validation of arguments...

let's say you have some procs which are called by the GUI, so you need to do some validation of the rubbish the users type into the system... These procs call other procs (which are not used by the GUI)... in these internal procs you have a couple of lines like this

if @@nestlevel = 1 begin--  raise an error in your normal way...end

Which means that no-one can call your code from an ODBC connection through excel or access or whatever Strictly speaking you don't even need to grant exec permissions to these "internal" procs because all the tables are owned by dbo (just like the calling procs) and as an added benefit you can totally trust the arguments you receive because they come from other code in the database...

cheers, ste

RE: Nesting Stored Procedures

Robert W Marda — Thu, 22 May 2003 10:24:00 GMT

I don't know that Microsoft has explained why you can only nest to nest level 32. I suspect they wanted to set some limit otherwise you could just have a stored procedure call itself and it would never stop. My guess is that an SP calling itself to infinity would cause other problems that would be worse than simply limiting the nest level to 32 and giving you an error if you exceed it.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Nesting Stored Procedures

johncyriac — Thu, 22 May 2003 10:17:00 GMT

rmardanest level exceeds 32 that only I wanted to point out ,is there an y explanation from ms,I am having a script to convert figures to words using the same concept I will pulish it later ,thak ye rmardawith love john

RE: Nesting Stored Procedures

Robert W Marda — Thu, 22 May 2003 10:11:00 GMT

johncyriac:Nice. I added a line to see to what level nesting it did:create proc nest_sp_dec@kount intasbeginselect @kountselect @kount=@kount-1SELECT @@NESTLEVEL AS NESTLEVELif @kount >0 exec nest_sp_dec @kount endand executed it with:nest_sp_dec 10It showed a nest level of 10and then withnest_sp_dec 33which exceeded the max nest level.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Nesting Stored Procedures

Robert W Marda — Thu, 22 May 2003 10:05:00 GMT

Frank:I found an article at the first link you mentioned called Manipulating Microsoft SQL Server Using SQL Injection. It focuses on using OPENROWSET and OPENDATASOURCE to execute unauthorized code on a SQL Server.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Nesting Stored Procedures

Robert W Marda — Thu, 22 May 2003 10:03:00 GMT

jeaux:With so small of queries in each SP there is no advantage I know of to use what I showed in my article over what you just posted.The advantage comes when you have 2,000 or more lines of code for each query using complex SELECT, FROM, WHERE, and ORDER BY clauses and possibly using GROUP BY and HAVING as well. Then it can be easier to maintain to have seperate stored procedures instead of using the technique you used.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Nesting Stored Procedures

jeaux — Thu, 22 May 2003 09:58:00 GMT

My question is why couldn't you rewrite the example in the article as the following:IF @Last <> '' AND @First = ''BEGIN SELECT * FROM authors WHERE au_lname LIKE @Last + '%'END ELSE IF @First <> '' AND @Last = ''BEGIN SELECT * FROM authors WHERE au_fname LIKE @First + '%'END ELSEBEGIN SELECT * FROM authors WHERE au_lname LIKE @Last + '%' AND au_fname LIKE @First + '%'ENDWhat is the advantage here of having 3 separate stored procedures? Forgive my ignorance.

RE: Nesting Stored Procedures

Robert W Marda — Thu, 22 May 2003 09:46:00 GMT

I knew about the second link, however it seems to be only discussing ways to get a password for a user account. Once you have a password for an account with the permissions you need then you can simply by pass the stored procedure and do what you want.My answer was focused at the stored procedure level (I don't always look at the big picture). Once inside a stored procedure I know of no other way to do an injection attack except the one I mentioned. You will forgive my lack of knowledge in this area, if there is another way I would love to know about it so that I can protect against it.With that in mind I don't think that nesting stored procedures would help protect against inection attacks unless you have something other than what I thought of in mind.Can you tell me specifically where to go at the first site to find something about injection attacks?Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Nesting Stored Procedures

johncyriac — Thu, 22 May 2003 09:39:00 GMT

innovative thinking is not at all there just try this real nestingcreate proc nest_sp_dec@kount intasbeginselect @kountselect @kount=@kount-1if @kount >0 exec nest_sp_dec @kount endand execute it withnest_sp_dec 10How is it dude

RE: Nesting Stored Procedures

Frank Kalis — Thu, 22 May 2003 09:26:00 GMT

Correction,the second link should be called http://www.ngssoftware.com.Sorry!Cheers,FrankEdited by - a5xo3z1 on 05/22/2003 09:26:40 AM

RE: Nesting Stored Procedures

Frank Kalis — Thu, 22 May 2003 09:24:00 GMT

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

RE: Nesting Stored Procedures

David.Poole — Thu, 22 May 2003 08:59:00 GMT

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 databaseBasically 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 sayINSERT MyTableexec usp_Proc @ArgThe 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.

RE: Nesting Stored Procedures

Robert W Marda — Thu, 22 May 2003 08:58:00 GMT

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. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Nesting Stored Procedures

Robert W Marda — Thu, 22 May 2003 08:50:00 GMT

ianyates:Good point. Placing the code that forces recompilation in its own SP should reduce the time spent to recompile.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Nesting Stored Procedures

Robert W Marda — Thu, 22 May 2003 08:47:00 GMT

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. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Nesting Stored Procedures

Frank Kalis — Thu, 22 May 2003 03:56:00 GMT

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?TIACheers,Frank

RE: Nesting Stored Procedures

David.Poole — Thu, 22 May 2003 03:50:00 GMT

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.

RE: Nesting Stored Procedures

Frank Kalis — Thu, 22 May 2003 03:33:00 GMT

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?[quote]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

RE: Nesting Stored Procedures

David.Poole — Thu, 22 May 2003 03:11:00 GMT

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.

RE: Nesting Stored Procedures

Frank Kalis — Thu, 22 May 2003 02:56:00 GMT

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

RE: Nesting Stored Procedures

David.Poole — Thu, 22 May 2003 02:07:00 GMT

I use nested stored procedures for the same reasons.I quite often have

 IF .....   INSERT #Tbl (Fields)   EXEC usp_SelectProc1 @Arg1, Arg2...ArgnELSE   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.

RE: Nesting Stored Procedures

Ian Yates — Thu, 22 May 2003 01:49:00 GMT

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 :)

Nesting Stored Procedures

Robert W Marda — Fri, 16 May 2003 00:00:00 GMT

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