Calling Stored Procedures
Calling Stored Procedures; Passing and Returning Parameters To and From Stored Procedures
Stored procedures are called using the EXEC statement, or, if the call to the stored procedure is the first statement in a batch, just by using the stored procedure name . For the system stored procedure sp_who , for example: sp_who --valid, first line of a batch go sp_who --still valid, first line of a batch after a GO go exec sp_who --valid, the exec is optional go print 'Going to run sp_who now' sp_who --not valid, bad syntax, exec required here go Yes, the preceding code is kind of strange and perhaps a bit confusing. It's a shortcut for administrators who tend to spend a lot of time running one stored procedure in a batch, and it saves them from typing a lot. That's a pretty simple stored procedure call; what about calling a stored procedure with a return value? That's fairly simple also. It goes something like this: DECLARE @Status int EXEC @Status=TableList3 'spt' print @Status This calls the stored procedure that was written earlier in this chapter, and shows you the names of all of the tables that contain 'spt' . The return code, in this case, is just a status number. So, how about output parameters? DECLARE @Status int, @LocalRowCount int EXEC @Status=TableList4 'spt', @LocalRowCount OUTPUT print @Status print @LocalRowCount Notice the keyword OUTPUT in the EXEC line. If you don't put that keyword in there, the batch runs without error, but it also doesn't put a value into @LocalRowCount , which remains NULL in this case. That's a very common error that takes a long time to track down. Now, how about that returned cursor? DECLARE @Status int, @LocalCursor cursor, @LocalCount int, @TableName sysname EXEC @Status = TableList5 null, @LocalCount OUTPUT, @LocalCursor OUTPUT PRINT @LocalCount PRINT @Status SELECT CURSOR_STATUS('variable', '@LocalCursor') FETCH NEXT FROM @LocalCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT @TableName FETCH NEXT FROM @LocalCursor INTO @TableName END CLOSE @LocalCursor DEALLOCATE @LocalCursor Notice the SELECT CURSOR_STATUS line actually just prints the status. It would be more effective to make that statement an IF statement that would avoid error messages if something happened and the cursor didn't return properly. There's another way to use the EXEC statement. So far, when there have been multiple parameters to pass, you've just passed the first parameter first, the second parameter second, and so on, like this: EXEC @Status = TableList5 null, @LocalCount OUTPUT, @LocalCursor OUTPUT That is called using positional parameter passing , because the first parameter in the EXEC matches up with the first parameter in the CREATE PROCEDURE , and the second matches up with the second, and so on. There's another way to call stored procedures: using named parameters, which looks like this: EXEC @Status = TableList5 @NamePattern=null, @RowCT = @LocalCount OUTPUT, @OutputCursor = @LocalCursor OUTPUT You don't have to put them in the same order, either: EXEC @Status = TableList5 @RowCT = @LocalCount OUTPUT, @OutputCursor = @LocalCursor OUTPUT, @NamePattern=null, There are some rules about how this all works, of course. The main rule is that if you start with named parameters, you have to use named parameters all the way through. If you start with positional parameters, you can use positional parameters until the first named parameter, then you have to use named parameters for all the rest of the parameters. For example: [View full width]
Notice that in the first statement, the @NamePattern variable was completely left out, which will cause it to default to null . This is one of the strengths of using named parameters: it lets you leave out the optional parameters you don't use, and the default values are used in their place. EXAM TIP Parameters There's almost guaranteed to be an exam question that relies on your understanding of positional parameters, named parameters, and defaults. Just remember the rules: If you get a named parameter in a list, every parameter after that has to be named. So, how does EXEC know from which database to pull the stored procedure? That's a very good question. Stored Procedure ScopingSystem stored procedures are stored in the Master database. When you execute a stored procedure, such as sp_who in a database other than Master, how does SQL Server know where to look for the stored procedure? The set of rules that determine where stored procedures will be executed are called the scoping rules. For example: use pubs go sp_who --why does this work? go One of the scoping rules is being used in that code block. The sp_who stored procedure is not in the Pubs database, but it can be executed from any database because it is stored in the Master database, and it's name starts with " sp_ ". So, if you need to write a stored procedure that can be accessed from any database, you can follow this convention and you'll be in good shape. When you execute a stored procedure that resides in Master, where does it look to find tables and other objects that it is looking for? For example, if you run something like this: use pubs go sp_help sysobjects Which of the sysobjects tables will be examined and have its column list returned: the one in Master or the one in Pubs ? Obviously, it's the one in Pubs . But what happens if you run this: use pubs go master..sp_help sysobjects The preceding code returns the column list in the Master database. Confused yet? If you specify a database name (such as master..sp_ help ) in the exec statement, then it's like performing a "use" statement to move over to the Master database before executing the stored procedure, and then another "use" statement to move back to the original database after the stored procedure is done. The stored procedure runs in the context of the exec statement, not the current database. If you do not specify a database name, then SQL Server runs the stored procedure in the current database. Here's another example: USE master go CREATE TABLE ScopeTest ( i int) go sp_help scopetest go USE pubs go sp_help ScopeTest go master..sp_help ScopeTest go So, what happens if you create a procedure in a local database with the same name as a stored procedure named sp_ in Master? The stored procedure that's local is ignored. It cannot be run, even by specifying the database name. If you name a stored procedure sp_something in a database other than Master , and there is a procedure with that name already in Master , then your non- Master stored procedure cannot be run until you give it a new name. Here's an example: USE pubs go CREATE PROCEDURE sp_who as begin print 'the fake sp_who is here' end go sp_who --runs the real sp_who go pubs..sp_who --once again, the real sp_who Stored Procedures and Temporary TablesTemporary tables are tables created in Tempdb that exist only inside a given connection. They are created with the Create Table statement, but they are all named with a single pound sign (#) at the beginning. Typically, they're given names such #tmp or something similar, but this is just convention ”and not a very good one because it doesn't give you any information on what the temporary table is used for. A temporary table lasts the duration of its connection, but across several batches. So, if you create a temporary table inside a stored procedure, the temporary table goes away at the end of the stored procedure, just like the variables do. However, if you create a temporary table in one batch and call a stored procedure inside a later batch, the stored procedure has access to the temporary table. For example: create table #tmp(id int) go create proc GetIDs as insert into #tmp select id from sysobjects go If you run the stored procedure, you end up with a temporary table full of ID numbers . Now, what happens if you drop the #tmp table and attempt to run the stored procedure? You get a message something like this: Invalid object name '#tmp' . The problem here is that when you create a stored procedure, all the objects that the stored procedure needs to access must be present when the stored procedure is created. In addition, if the stored procedure creates objects, including temporary tables, then the temporary tables can't exist when you create the stored procedure. When you create a temporary table inside a stored procedure, the stored procedure automatically recompiles itself. This can cause performance problems, but there are things you can do to avoid serious slowdowns. First, avoid the use of temporary tables when you can; use local variables of type table as appropriate. Second, put all your CREATE TABLE statements at the beginning of the stored procedure. If you need to put data into a temporary table and then share the data with other sessions, you can create a global temporary table by naming the table with two pound signs (##) rather than one. A global temporary table can be accessed by all connections on the server, but is destroyed when the connection that created it ends. If you need a bit more persistence than that, you can actually directly create tables in Tempdb . These tables persist until the server is restarted. The Tempdb database, the database that holds all temporary tables, is rebuilt every time the server starts, so the table is destroyed when the server starts, along with the rest of the objects in Tempdb .
You can now create stored procedures. You have a pretty good handle on how to run them and where they are going to run from, but how do you change or drop a stored procedure? Dropping Stored ProceduresTo drop a stored procedure, use the DROP PROCEDURE statement, as follows : DROP PROCEDURE tablelist3 Surprisingly, this has nowhere near the rules and obligations as creating a stored procedure. You can drop stored procedures in only the current database, so something like the following is not valid: DROP PROCEDURE master..tablelist3 You can put as many DROP PROCEDURE statements in a batch as you'd like. If the stored procedure is in use when you issue the DROP PROCEDURE command, the connection running the stored procedure continues to execute the stored procedure, but it won't exist after the stored procedure finishes executing. Altering a Stored ProcedureTo alter a stored procedure, use the ALTER PROCEDURE command. It looks nearly identical to the CREATE PROCEDURE command, but it has the word ALTER in it rather than CREATE . If the stored procedure you are trying to alter does not exist, you get an error. Typically, there are two ways to change a stored procedure. The way SQL Server's scripting engine does it is to drop the original stored procedure and then create the new one. The problem with this technique is that it removes all the security settings from the stored procedure. If you use ALTER , then the stored procedure changes, but the security doesn't. Stored Procedure TextGetting stored procedure text from within Enterprise Manager is very straightforward. Basically, you just hunt through the tree view, right-click on the stored procedure, choose All Tasks and Generate Script from the context menus , click the Preview button, and that's it. Getting them from within Query Analyzer is a nice shortcut, and not much more complex. Use the sp_helptext command, like this: USE master go sp_helptext sp_who This displays all the text for the stored procedure that is passed in as the parameter, in the CREATE PROCEDURE format. So, all you have to do to make a change is a little copy-and-paste , change the CREATE to ALTER , and make your functional changes. Nested Stored ProceduresStored procedures can call other stored procedures. One restriction, however, should be mentioned. If the called stored procedure uses an INSERT with an EXEC statement, the calling stored procedure cannot use the output of the called stored procedure as an INSERT with an EXEC . For example: CREATE PROCEDURE CalledProc as CREATE TABLE #tmp ( spid int, ecid int, status varchar(30), loginname sysname, hostname sysname, blk int, dbname sysname, cmd varchar(30) ) INSERT INTO #tmp EXEC sp_who go CREATE TABLE #localtmp ( spid int, ecid int, status varchar(30), loginname sysname, hostname sysname, blk int, dbname sysname, cmd varchar(30) ) INSERT INTO #localtmp EXEC calledProc This looks like a fairly innocent bit of code, but it results in an error: An INSERT EXEC statement cannot be nested. So, if you call one stored procedure from another stored procedure, you can use a cursor to pass back the results, but don't use a temporary table like this one.  |
OUTPUT --valid EXEC @Status = TableList5 null, @RowCT OUTPUT, @OutputCursor = @LocalCursor OUTPUT --valid EXEC @Status = TableList5 @RowCT = @LocalCount OUTPUT, @LocalCursor OUTPUT --NOT valid 
EAN: N/A
Pages: 228