:::: MENU ::::

Posts Categorized / User Defined Stored Procedures

  • Dec 20 / 2012
  • 0
Cursors, dbDigger, T-SQL Scripts, User Defined Stored Procedures

Drop specific tables through a stored procedure by using Cursor

Suppose we have to create a procedure which would drop all the tables having a given naming convention. Procedure would utilize a cursor and dynamic SQL. Table names would be pulled from SYS.Objects catalog. Here is the commented procedure for demo

/*   
 Demo would drop the required tables  
 by using a cursor in following steps  
 1. Create tables for drop demo  
 2. Verify the created tables  
 3. Create USP  
 4. Create cursor in USP to pull and execute the drop commands  
 5. Execute the procedure  
 6. Verify that tables do not exist now  
 */  
 -- Create Tables  
 create table rep1 (id smallint)  
 GO  
 create table rep2 (id smallint)  
 GO  
 create table rep3 (id smallint)  
 GO  
 create table rep4 (id smallint)  
 GO  
 -- verify the created tables   
 SELECT name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 GO  

 -------------------------------------------------------------  
 -- Create USP and a cursor in USP  
 If exists (select name from sys.objects   
 where type = 'P' and name = 'USP_DropRepTables')  
 DROP PROCEDURE USP_DropRepTables  
 GO  
 CREATE PROCEDURE USP_DropRepTables  
 AS  
 -- Declare variable for SQL command  
 DECLARE @dropQuery NVARCHAR(400)  
 -- Declare and populate cursor with required tables  
 DECLARE DropLoopCursor CURSOR FOR  
 SELECT 'DROP Table '+name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 -- Open cursor  
 OPEN DropLoopCursor  
 -- Fetch record from cursor  
 FETCH NEXT FROM DropLoopCursor INTO @dropQuery  
 -- Configure while loop in cursor  
 WHILE (@@FETCH_STATUS <> -1)  
 BEGIN  
 -- Execute Dynamical SQL  
 execute (@dropQuery)  
 -- Fetch next recod  
 FETCH NEXT FROM DropLoopCursor INTO @dropQuery  
 END  
 -- Close and deallocate the cursor  
 CLOSE DropLoopCursor  
 DEALLOCATE DropLoopCursor  
 ------------------------------------------------------------  
 -- End the procedure  
 GO  

 -- Execute the procedure  
 EXECUTE USP_DropRepTables  
 GO  

 -- Verify that tables do not exist now  
 SELECT name FROM SYS.OBJECTS  
 WHERE TYPE = 'U'  
 and name like '%ConventionHere%'  
 GO  

You may copy and paste the whole code in SSMS query pane for a better view of comments. Also it is required to take care that given naming convention should not be used by any such table that is not required to drop.

  • Jan 07 / 2010
  • 0
dbDigger, User Defined Stored Procedures

SQL Server T-SQL Stored Procedures for beginners

It is important to choose the right choice to achieve an optimal performance. Here I will elaborate some key aspects of Stored procedures in T-SQL fro SQL Server, which would help to choose the right at its place.
A stored procedure is a pre-compiled batch of Transact-SQL statements stored under a name and processed as a unit that you can call from within another Transact-SQL statement or from the client applications. The major difference between batch and stored procedure is a batch is compiled at the time of execution and a stored procedure is already compiled. It can also accept parameters. Parameter name can be up to 30 characters including @. And there can be up to 2100 parameters in a SQL Server 2005 stored procedure. Stored procedures allow many users to use the same code
When a T-SQL statement is executed many things happen on server side in relational engine, like

  • Parsing
  • Syntax checking
  • Object resolution
  • Compilation
  • Query plan determination

All this is required for every query. In case of stored procedures all this is carried out once and only. After that whenever a stored procedure is executed, nothing of above mentioned happens. This is real power of stored procedure.
Users can be given execution permission on a stored procedure that modifies data instead of permission to directly modify the table. It is very help full for security implementation.
Following T-SQL command is used to create stored procedure

 CREATE PROCEDURE proc_name  
 AS  
 /*  
 SQL statements or batch  
 */  
 RETURN  

Created stored procedure will be stored in current database, but it will have access to all other databases. The “create procedure” command can not be combined with any other statement in a single batch. Any T-SQL statement can be included in stored procedure except the following

  • create view
  • create rule
  • create default
  • create procedure
  • create trigger
  • use

To execute the stored procedure use following command

 
EXECUTE proc_name  
 --OR  
 EXEC proc_name  

Stored procedure can be called from

  • Batches
  • Other stored procedures
  • Triggers
  • User defined functions

Stored procedures may or may not return a value. Its output flexibility provides a greater edge over user defined functions. There are three ways to return data from a stored procedure.

  • Result Sets
  • Output variables
  • Return statement

Following is an overview and comparison of these data return approaches.

If we use data selection statement inside the stored procedure, then data will be retrieved and shown as in normal select statement, but do not forget that you will have all server and network optimization with you in this case. Following code will just do it through pubs database.

 
CREATE PROCEDURE testResultSet  
 AS  
 SELECT * FROM pubs..authors  
 GO  

If we want to return a limited number of (mostly) calculated/aggregate values then the choice is to use output variables. These parameters will provide to handle and transport the values in any data type. Currently I am not sure the maximum number of out put parameters provided in SQL Server 2005. But in rare case one will use the maximum number. Use following code to create a SP which will return a integer value.

 
CREATE PROCEDURE testVars  
 (@firstNameCount INT OUTPUT )  
 AS  
 SELECT @FirstNameCount = COUNT(*)  
 FROM pubs..authors  
 WHERE au_fname like 'A%'  
 GO  

And now to execute the procedure use following lines

 
DECLARE @counting INT  
 EXEC testVars  
 @firstNameCount = @counting OUTPUT  
 SELECT counting = @counting  

The most limited way to get data from a stored procedure is through return statement. It returns a single numeric value and is mostly used to return a status result or error code from inside a stored procedure. Use following code to provide a value to SP and simply get it back when executed.

 
CREATE PROC testReturnValue (@InValue int)  
 AS  
 Return @Invalue  
 GO  
 DECLARE @ReturnValue INT  
 EXEC @ReturnValue = TestReturnValue 3  
 SELECT ReturnValue=@ReturnValue  
 GO  

Recompiling a Stored Procedure
Because the stored procedure execution plan can be outdated, for example when a large amount of data modifications are made to a table referenced by a stored procedure, you may need to recompile the execution plan. SQL Server 2000 automatically recompiles the stored procedure execution plan when one of the following conditions are met:

  • Any schema changes of the objects referenced in the stored procedure were made.
  • An index used by the execution plan of the stored procedure is dropped.
  • A large amount of data modifications are made to a table referenced by a stored procedure.
  • The new distribution statistics were generated.
  • The execution plan was deleted from memory, because the memory is required for other objects.
  • A table has trigger(s) and the number of rows in the inserted or deleted tables grows significantly.

There are three ways to cause SQL Server to recompile the stored procedure execution plan, they are:

  • Including a WITH RECOMPILE clause in a CREATE PROCEDURE statement. When you include a WITH RECOMPILE clause in a CREATE PROCEDURE statement, SQL Server will not cache a plan for this procedure and the procedure will be recompiled every time it will be run. Because the stored procedure execution plan will never been cached, you should use the RECOMPILE option in a CREATE PROCEDURE statement very carefully.
  • Including a WITH RECOMPILE clause in a EXECUTE statement. When you include a WITH RECOMPILE clause in a EXECUTE statement, the stored procedure execution plan will be recompiled when you run this EXECUTE statement. You can use this option if the parameters you are supplying are atypical or if the data has significantly changed.
  • Using the sp_recompile system stored procedure to causes stored procedures to be recompiled the next time they are run. To cause stored procedures to be recompiled the next time they are run, you can use the sp_recompile system stored procedure. You can use the sp_recompile system stored procedure when you want your stored procedure reflects inchanges in indexes or data values.

Because SQL Server 2000 and SQL Server 2005 can recompile stored procedures and execution plans automatically, in most cases it is not necessary to use the sp_recompile system stored procedure or a WITH RECOMPILE clause, and you can rely on SQL Server decisions to recompile execution plan.
All these qualities make a stored procedure a very power full tool for most of optimized T-SQL processes.

  • Aug 12 / 2008
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, T-SQL Tips and Tricks, User Defined Stored Procedures

DBA Best Practices for Queries and Stored Procedures

Queries and stored procedures are close companions of a DBA. So make your life easy by following the baseline given below, while writing queries and stored procedures by tomorrow.

  1. Maintain all code in a source control system.
  2. Keep transactions as short as possible. This reduces locking and increases application concurrency, which helps to boost performance.
  3. Avoid using query hints unless you know exactly what you are doing, and you have verified that the hint actually boosts performance.
  4. Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure.
  5. Use the least restrictive transaction isolation level possible for your user connection, instead of always using the default READ COMMITTED.
  6. SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.
  7. Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them.
  8. For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner. object_name or schema_owner.object_ name.
  9. One way to help ensure that stored procedures query plans are reused from execution to execution of the same stored procedure is to ensure that any user connections information, SET options, database options, or SQL Server configuration options don’t change from execution to execution of the same stored procedure. If they do change, then SQL Server may consider these same stored procedures to be different, and not be able to reuse the current query plan stored in cache.
  10. Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. Stored procedures offer many benefits, including:
  11. Reduced network traffic and latency, boosting application performance.
  12. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
  13. Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.
  14. Stored procedures help promote code reuse. While this does not directly boost an application’s performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
  15. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns). This saves developer time.
  16. Stored procedures provide better security to your data.

A part chosen from SQL Server DBA best practices By Brad M.mcGehee

Consult us to explore the Databases. Contact us