Did you ever have a problem handling SQL Server transactions in nested stored procedures? However, after reviewing your code you still had a feeling that you handled transactions correctly? In this tip we are going to explain the reason for the above error and suggest solutions on how to correctly manage transactions in case of nesting stored procedures to avoid this error. First, we will create a test environment:. To facilitate our example we will use two procedures — an outer procedure and an inner procedure.
Stored procedure uspInsertData inserts data into table TestTable and also calls uspUpdateData to update the value for specific rows. It is not a fatal error, however it should be avoided and there are different approaches. To avoid this error, we can do the following: we can open transactions and commit or roll them back only in the outermost stored procedure, but considering the fact that the inner procedure can be called separately or in other procedures, so we will need to include logic in the inner procedure into the transaction.
To do this, we can check the inner procedure if there is an open transaction from the caller procedure. If yes, we do not open a new one, if no, we open a transaction in the inner procedure and commit it in case of success. As a result our procedure ends with the same count of transactions as it starts, so we will not receive an error. Now execution of the uspInsertData procedure will not generate an error, but will still have the same result — all changes are rolled back and the TestTable is empty.
We can also use another approach allowing the inner procedures to open transactions without checking if there are open transactions. There are different approaches of handling transactions in nested stored procedures, but in all of them a ROLLBACK must be issued in the outermost transaction. Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.
Execute a Stored Procedure
View all my tips. Become a paid author. Recommended Reading. Cursor in SQL Server. Rolling up multiple rows into a single row and column for SQL Server data. Back To Top The real power of stored procedures is the ability to pass parameters and have the stored procedure handle the differing requests that are made.
In this topic we will look at passing parameter values to a stored procedure. Just like you have the ability to use parameters with your SQL code you can also setup your stored procedures to accept one or more parameter values. All examples use the AdventureWorks database. The idea is to create the stored procedure where the City is passed into the stored procedure so it can create dynamic results. This can be done as follows using a variable. If we run the below code it will return just the results for New York.
We could use this approach and keep updating the City variable, but there is a better way to do this by creating a stored procedure. In this example we will query the Person. Address table from the AdventureWorks database, but instead of getting back all records we will limit it to just a particular city. This example assumes there will be an exact match on the City value that is passed.
If you created the stored procedure and you want to recreate the stored procedure with the same name, you can delete it using the following before trying to create it again.
We can also do the same thing, but allow the users to give us a starting point to search the data. In both of the proceeding examples it assumes that a parameter value will always be passed.
So in this example we use the NULL option to allow you to not pass in a parameter value. If we create and run this stored procedure as is it will not return any data, because it is looking for any City values that equal NULL. Setting up multiple parameters is very easy to do. You just need to list each parameter and the data type separated by a comma as shown below.
People just do this so then can identify what the object is based on the name, but other than that there is no real purpose. Did I miss the part where you explain the prupose of the prefix of usp in the Proc name?
What does that mean or stand for? Leave the column blank for the first visit for each patient. The results should be as follows:. Address GO. EXEC dbo. Back To Top It was very helpful. It would be great if the execution is also showed. Hi Will, there is not any real purpose to have a prefix for any of the objects. All the examples above were useful for my stored proc development.This is one of the most asked questions related to stored procedure SP in recent time and the answer is even simpler.
Stored Procedures are very old concepts and every day I see more and more adoption to Stored Procedure over dynamic code. Let us first create one Stored Procedure, which gives us square of the passed parameter. Now let us create second Stored Procedure which gives us area of the circle. You can clearly see that we need to pass the result of the first procedure SquareSP to the second procedure Find Area.
We can do that by using the following method :. You can see that it is extremely simple to pass the result of the first procedure to second procedure. Let me know what you think of this blog post, I personally thought this problem was very simple, but not many people know the solution of the same. If you find it interesting and if you are using this kind of solution in your environment, I encourage you to share the knowledge with everyone by leaving a comment as a blog.
Stored procedure return values are generally used to indicate success 0 or failure other than zero. If you are going to use the style of programming used in the example, it would be better to use a Function rather than a stored procedure. The return value of a function is expected to be the result, not an indicator of success or failure. The example only works for scalar results a single value of type [int].
Stored procedure return values are only allowed to be integers. There are two cases for returning values from a stored procedure that should be addressed: scalar values and tables. There is a third multiple result setsbut those are NOT easily addressed. Because a stored proc can have multiple output parameters, multiple values can be passed from the stored proc.
Now for the hard part: passing the results of a stored proc when that result is a table. For this, you need a user-defined table type. Not so much for the source procedure, but so you can easily pass in the results to the target procedure. First, create a user-defined table-type, then two procs, one that lists the databases and the next that adds a column to the list.This topic describes how to execute a stored procedure in SQL Server There are two different ways to execute a stored procedure.
The first and most common approach is for an application or user to call the procedure. The second approach is to set the procedure to run automatically when an instance of SQL Server starts. Alternatively, the procedure can be called and executed without the keyword if the procedure is the first statement in the Transact-SQL batch.
The calling database collation is used when matching system procedure names. Therefore, always use the exact case of system procedure names in procedure calls. For example, this code will fail if it is executed in the context of a database that has a case-sensitive collation:. To display the exact system procedure names, query the sys. If a user-defined procedure has the same name as a system procedure, the user-defined procedure might not ever execute. Because they logically appear in all user- and system- defined databases, they can be executed from any database without having to fully qualify the procedure name.
However, we recommend schema-qualifying all system procedure names with the sys schema name to prevent name conflicts.
The following example demonstrates the recommended method of calling a system procedure. When executing a user-defined procedure, we recommend qualifying the procedure name with the schema name.
This practice gives a small performance boost because the Database Engine does not have to search multiple schemas. It also prevents executing the wrong procedure if a database has procedures with the same name in multiple schemas.
The following example demonstrates the recommended method to execute a user-defined procedure. Notice that the procedure accepts one input parameter. For information about specifying input and output parameters, see Specify Parameters. If a nonqualified user-defined procedure is specified, the Database Engine searches for the procedure in the following order:. The caller's default schema if it is executed in a batch or in dynamic SQL.
Or, if the nonqualified procedure name appears inside the body of another procedure definition, the schema that contains this other procedure is searched next. Procedures marked for automatic execution are executed every time SQL Server starts and the master database is recovered during that startup process. Setting up procedures to execute automatically can be useful for performing database maintenance operations or for having procedures run continuously as background processes.I have a stored procedure that is located in one database and I would like to have it execute against a different database.
My problem is when I go to execute it, it is still executing against the database it is stored in. Is it possible to tell this stored procedure when it runs to execute its code against this second database?
Code Snippet. Code Snippet use [Database2]. Be careful using this though. It can have adverse affects if someone passes an injection parameter in that has additional code the you are not expecting.
Thanks for your reply. I was hoping to avoid using Dynamic SQL for the reasons you mentioned and I might just have to use it, but didn't know if there was another way to do this or not without using it. Do you know of any other way or is this my only option?
SQL Server Stored Procedure with Parameters
This cannot work without dynamic sql because you are telling the stored procedure to override the current database.
Think about it When you do a select like the one below does it pull from the same database??? No, it does not. You have to create a linked server to the other server, and then any reference to the other server has to be fully qualified. I don't think that this would be the best implementation of this functionality though. It would be better to just execute the procedure remotely over the link, and have procedure exist in MyLinkedServer.
Sign in. United States English. Ask a question. Quick access. Search related threads. Remove From My Forums. Answered by:. Archived Forums. This is the place for advice and discussions 0 0.
Sign in to vote. Thursday, February 14, PM. Thanks, Flea. You can hardcode the name of the other database in your stored procedure Just wanted to see if there was other options! Thanks guys! I have a similar issue, where the SP is located on my local server DB, but i want to run it against a database located on another server.
Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. When calling several stored procedures for one stored procedure is this the right or best way to go about it on SQL Server ?
You are being inconsistent with regard to procedures return mode. EXEC cannot nest. I also have an unrelated commend, which is just an element of style: I find long IF I always found that expressing the same as a DO Again, this is no important as is just a code formatting style, but is a suggestion in case you agree that it results in code that is easier to read.
Rollback will rollback a transaction, but keep going. I'd had some explicit RETURN commands to gurantee and make it obvious when and where you want the code to exit the stored procedure.
IMHO, I don't think calling other stored procs from one stored proc is a very good idea. If there is an error, it would take a lot of man hours to figure out where the error happened.
In some cases another developer may change a stored proc without telling anyone in the team. The number of things that can go wrong is too high. Learn more. Stored Procedure calls multiple stored procedures Ask Question.
Asked 10 years, 11 months ago. Active 10 years, 11 months ago.
Executing SQL Stored Procedure from Another Stored Procedure
Viewed 7k times. Active Oldest Votes. Remus Rusanu Remus Rusanu k 36 36 gold badges silver badges bronze badges. MatthewMartin MatthewMartin It may be best to write all your sql in one big stored proc. Edited: It also depends on how big your stored proc is going to be. But at what cost? Making stored procs stand alone will greatly increase the amount of duplicate code found in stored procedures.
True, I have to agree it depends on how big the stored proc is going to be. I totally disagree. It would be a bad programming practice to repeat code that does the same thing in multiple stored procedures. Stored procedures are specifically used to encapsulate business or data logic so they can be re-used. Although there are some limitations to hierarchically call stored procedures from another, the maintenance benefits largely overweight the risks.
Like any "shared" component in an application like a DLL, or a web service, etccompatibility after a change is an important issue, but its far from being a huge issue Sign up or log in Sign up using Google.
It only takes a minute to sign up. It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for each one except for the name of the procedure. In an OO language I would use an abstraction such as an interface or functor and loop over a bunch of objects.
That does not work in SQL, but I want to find some way to make this code more concise with less copy and paste repetition. Yes, I know that fundamentally SQL is about set operations and does not support what I want to do very well, but if there is a way, it will make the code much more concise. I also need to capture the result of each stored procedure invocation and do something with it which is not relevant to this question. When I run the procedure, SQL Server gives me an error because the result variable that is part of the dynamic SQL is not defined as part of the batch that is contained in the sql variable.
If I modify it like this:. This works fine except for retrieving the return value of the subprocedures. Is there a way to accomplish my stated goal, and if so, how? Part of the code that is not essential to this question involves knowing the iteration number, hence the use of a loop control variable.
When the batch is executed it will stop executing when there is a result not zero and keep that value in the output parameter. If you want to loop over the procedures. Another approach, which still uses dynamic SQL but no ugly cursor scaffolding and allows you to examine the step which failed and the error number generated, without bubbling the error to the caller :.
Not sure if you wanted the "Failed at step" value to be the step number like your loop variable or the actual value of step in the table. You can switch by changing this:. This way you can pass variables in and out to your dynamic query. Sign up to join this community.
The best answers are voted up and rise to the top. Run multiple stored procedures in sequence Ask Question. Asked 5 years, 11 months ago. Active 5 years, 11 months ago. Viewed 50k times. Back to AaronBertrand's original comment, result is used to control whether to continue to the next procedure or to perform cleanup and exit logic.
It contains either 0 meaning success or non-zero containing an error code. Exceptions won't work here due to some custom error-handling logic and the fact that SQL Server will not let us throw standard error codes. It can be a temp table, does not matter really. My understanding is that table variables are easier to clean up, i. Nor do temp tables. So you have custom error-handling logic that relies on the original error being thrown?
And you need to use the error number that comes back separately? The error handling occurs inside the subprocedure and it returns a value to the main procedure, which has to return a code via output parameter I omitted that part for brevity.MSSQL - How to execute stored procedure from inside of another stored procedure
There are some funky requirements to fulfill here.