Home Resources Articles Stored Procedures Tips

Here is some practice tips which you can remember and apply when writing SPs

  • Use SET NOCOUNT ON, this will reduce unnecessary return values as by default stored procedure by default will return single value.
  • Don’t Prefix Stored Procedure names with “sp_” as it is specifically reserved for Master database. When ever you make a call to the stored procedure when it finds “sp_” it first scans in the MASTER Database before checking them in the other databases.
  • When you need to execute a dynamically constructed SQL Statement through a varchar / string variable, you should use the sp_executesql instead of the EXECUTE statement.
  • If you ever want to calculate the Total Time that your procedure actually executes you can do that by the below given method.
DECLARE @starttime datetime
SELECT @starttime = GETDATE()

… Your code here …

SELECT DATEDIFF(ms, @starttime, GETDATE()) AS 'Total Time (ms)'
  • If you want to use a result set of a stored procedure in another procedure you can easily do it by the below method.
CREATE PROC usp_subTable as
select * from pubs.authors
Create PROC usp_MainTable as
select * into #NewTable From usp_subTable
  • If you are passing more then 4000 characters in a Varchar Datatype, it wont accept and it truncates value. You should pass them in multiple variables.
  • If you want to clear all of the Cached Execution Plans of the Stored Procedure, you can use “DBCC FREEPROCCACHE”. If you want to clear procedure cache for your database alone you need to use “DBCC FLUSHPROCINDB”. (Microsoft doesn’t recommends these commands for use)
  • Stored procedures have their best advantage when it operates on compile once and execute many times. So make sure not to use RE COMPILE Option or use it when absolutely needed.
  • When you stored procedure is using any DDL Statements (eg. Create / Drop Tables etc..,) you should use it before any of the DML Statement (eg. Select, Update, Delete etc..,), this is because after every DDL Statement the Procedure Re Compiles in order to take in to account of the changes made to the objects which may be referred in your DML Statements.
  • Avoiding Nesting Stored Procedures can boost SQL Server’s internal memory performance.
  • Its good practice to call stored procedures with the format.

<<dbname>>.<<username>>.<<storedproceduresname>> as it makes the SQL server’s scanning job easier.

  • Output Parameters are best suited while returning single values from a procedure.
  • While using Dynamic SQL Statements, Please ensure you are taking care of SQL Injection attacks. To get more insight on Dynamic SQL you can verify http://www.sommarskog.se/dynamic_sql.html

Some Frequently used System / Inbuilt Stored Procedures

sp_server_info : Gives complete information about the current SQL Server that you are connected to.
sp_databases, sp_helpdb – Lists all the databases, with this two sp_helpdb provides more precise and clear information on the size, status created date etc..,
sp_attach_db, sp_detach_db – These stored procedures are usefull when you are porting a database from one server to another server. This is one of the best methods to change the database without any problems. This is just like copying a file from one place to another place. The Operation is to Detach from the Source and Attach to the Destination. After this you may need to use sp_change_users_login to update the logins and users link.
sp_helpuser – Lists all the users in the system.
sp_who, sp_who2 – Gives the list of logged in users and their complete details about the execution status.
sp_msforeachtable – This is one of the undocumented procedures, which you can used to find the Total Physical Space Occupied by each tables in the database.
Eg:sp_msforeachtable ‘sp_spaceused “?”‘
sp_password – Used to change password for an SQL Server Login.
sp_tables – Lists all the tables and virtual tables (Views) along with the system tables, with information’s including table owner.
sp_stored_procedures – Lists all the stored procedures as like the table list with custom stored procedures in name; 1 format. (Please note it doesn’t mean that you can execute all the procedures that are listed, it depends on the permissions that you have)
sp_helptablename – Lists all the information pertaining to the given tablename with details about every column, constraints and the file table is located.
sp_helptext – Used to view the contents of a view, stored procedure, user defined functions.
sp_changeobjectowner – Changing Table / Stored Procedure Owner
If you ever want to change the owner of the object to another user this procedure will be helpful.
sp_change_users_login – This is a very useful procedure when you are porting the database using sp_attach_db and where you need to keep your old logins and users as same.
sp_change_users_login ‘Auto_Fix | Report | Update_One ’, ‘username’, ‘login_name’
The Auto_Fix is the best option in most of the cases where it automatically creates and links the login name to the given username. But it works only if the database user and the login are identical.
The Report option just gives information about the usernames and logins that the database has permission.
The Update_One option is used to specifically update the login of a database user.

Creating HTML Output from SQL Server

Creating HTML Output is an option available in SQL Server 6.5 or above. This option helps us to create HTML Outputs for a given query. This option is useful if every want to create periodical reports. I would say this is the starting point for Microsoft to develop reporting services. There are three build in stored procedures available to create such tasks.
sp_makewebtask, sp_runwebtask, sp_dropwebtask each does that job as their name implies. More information on how to create and deploy web tasks can be found here.
http://msdn2.microsoft.com/en-us/library/ms186284.aspx
Calling stored procedures from VB.NET