Populate table from Stored Procedure resultset in SQL Server

I was answering post in MSDN website today and found one question which was asking “How to populate table from stored procedure in SQL Server?”. This is quite a common question and I have answered it in Experts-Exchange.com forum so many times but today I felt to share it with my blog reader.

Actually there is no direct way to populate result of stored procedure into SQL Server table but yes, you can use OPENROWSET to achieve this task. Let us look at it practically.

[sourcecode language=”sql”]
USE AdventureWOrks2012
GO

CREATE PROC uspGetSaleOrderHeader
AS
SELECT
SalesOrderNumber,
RevisionNumber,
OrderDate,
ShipDate,
AccountNumber,
CustomerID,
TotalDue
FROM
Sales.SalesOrderHeader
GO

IF OBJECT_ID(‘tempdb..#TempSalesTable’) IS NOT NULL
DROP TABLE #TempSalesTable

SELECT * INTO #TempSalesTable FROM OPENROWSET(‘SQLNCLI’, ‘Server=WIN-9H6QATRKY81\SQL2K12DEV;UID=sa;PWD=upgrade1;’,’EXEC AdventureWOrks2012..uspGetSaleOrderHeader’)

SELECT * FROM #TempSalesTable
[/sourcecode]

If “Ad Hoc Distributed Queries” is disabled in your SQL Server, you will not be able to use OPENROWSET. You can enable “Ad Hoc Distributed Queries” with small TSQL Script, click here, for more details about this.

BTW, I have few more article where I have used OPENROWSET, click on the link to see more examples of OPENROWSET.

http://blog.extreme-advice.com/2011/07/05/openrowset-and-openquery-in-sql-server-20052008/

http://blog.extreme-advice.com/2011/07/15/filestream-in-sql-server-2008/

http://blog.extreme-advice.com/2011/07/06/msg-7301-cannot-obtain-the-required-interface-iid_idbschemarowset-from-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-linktoaceess/

http://blog.extreme-advice.com/2009/08/10/insert-and-update-image-field-in-sql-server-20082005/

http://blog.extreme-advice.com/2009/04/25/load-relational-xml-data-in-sql-server-2005/

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

Some facts about Stored Procedure Compilation & Recompilation in SQL Server

I generally ask one question in interview “If we have one complex query which JOIN five table, what would work best from .NET application, Call of Query or Stored Procedure?” 80% of candidate used to say “SP works fast”. My next question always would be “WHY?” than out of those 80%, 60% would say “Because SP is a compiled code”. After that I fire my follow up question, I can see option of “Compile page or project in .NET but I never see that kind of option in SSMS, how do you compile your SP?”, once I EXECUTE this question, 90% candidate of those 60% candidate would like to keep silence or divert the talk.
Anyway, intention of this article is to let you know some facts about SP compilation & Recompilation. Since it is big topic and wouldn’t cover under on article, I may come up with some more articles on this topic but right now, let me explain some basic facts only.
First of all, let me tell you that you don’t need to compile Stored Procedure manually, when you execute it, SQL Server will compile your Stored Procedure for you and save the execution plan for future use so that it doesn’t need to compile again and again, this is generic understanding, it doesn’t work all time as few facts are there which cause recompilation many time or every time. If you want to recompile your Stored Procedure manually, you should use “SP_Recompile” Stored Procedure given by SQL Server.
Now, you think if recompilation is that bad than why Microsoft has given facility to recompile? Well, let me have an opportunity to break the ice, recompilation of stored procedure is not always bad. It may be beneficial or may be harmful, it is totally depends on the situation.
Actually compilation of Stored Procedure stores the execution plan first time you execute your Stored Procedure and every follow up call would use the same execution plan but recompilation of SP would be helpful if you have new statistics or new index on the table. BTW, in SQL Server 2008+ there is in-built functionality to recompile at statement level rather than recompiling whole stored procedure which is less resource centric.
Following is the list of basic cause which forces Stored Procedure to recompile.
·        
  • Change in SET option within Stored Procedure
  • Execution plan is very old
  • Schema change in table, index, view or temp tables which are used in Stored Procedure
  •  “Deferred object resolution”, means object was not available while compiling Stored Procedure  but you have created later on, may be some temp table you have created in Stored Procedure.
  •  Call of “SP_Recompile” Stored Procedure.
  • Call of RECOMPILE clause in Stored Procedure.
  • Statistics are old
How to avoid Stored Procedure recompilations?
  • Avoid using temp table or other DDL statements as long as possible.
  • Use table variable in Stored Procedure if needed
  • Avoid changing SET option in Stored Procedure like ARITHABORT, Quoted_Identifier, ANSI_NULLS, ANSI_WARNINGS etc.
  • Avoiding recompilation by statistics change by using “KEEPFIXEDPLAN” hint.
  • Disable Auto Update statistics for your database.
Well, these are very basic understanding and each point of this article may consume separate dedicated article and I may come up with series on this very soon.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Dynamic SELECT statement with SP by querying information_schema.columns in SQL Server 2008/2005

Some days from now, I am getting new requests everyday to solve and I love to solve problems as much as I can and help community more and more.  Situation was like, there is a table which has few fields like Name, Address, Age ,_NO etc. but reader don’t want to return columns starts with “_”.  Very simple thing, right. Suppose name of Table is “DemoTable” you can simply write down
SELECT Name, Address, Age from DemoTable
But requirement doesn’t ends here,  it needs to be dynamic and should work with any table without even specifying the column name. Here we have to start thinking something different, let me show you how I did that? Have a look at below script and enjoy SQL Programming!!!!
–Demo table
create table DemoTable
(
Name varchar(10),
Address varchar(10),
Age int,
_No int
)

 
–SP which will accept tablename and character of the column name
–which needs to be eliminated from the list of results.
create proc DynamicSelect
@TableName varchar(20),
@FilterChar char(1)
AS
BEGIN
SET NOCOUNT ON
      DECLARE @Cols NVARCHAR(500)
      SET @Cols=
      –gathering column list in @Cols variable by querying Information_Scehma.columns table
      SELECT @Cols=@Cols+ s.column_name + ‘, ‘ FROM
      (select COLUMN_NAME from  information_schema.columns where TABLE_NAME=‘TableA’ and charindex(@FilterChar,COLUMN_NAME)<=0) AS s
      SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
      set @cols=‘SELECT ‘ + @Cols + ‘ From ‘ + @TableName
      EXECUTE sp_executeSQL @Cols
END


–check SP whether it actually works!!!!
Exec DynamicSelect ‘DemoTable’,‘_’

 
Actually above task could be done by CURSOR also but as long as possible I always would like to avoid the cursor.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Use of SQL Server Stored Procedure in VB.NET for Client/Server application.

After booming of internet, people have divert concentration from client/server application but client/server windows application is not died so far and Microsoft have taken the steps to remove syntax and logic difference between web as well as windows application. They have not achieved it 100% even this step is really appreciable. Today I would like to show you demonstration about how you can get record set from SQL Stored procedure and use its value in VB.NET windows application to write employee name in windows form on dynamic label.

We will have two steps for this demo.

1.) SQL Server coding

–create table

USE [adventureworks]

GO

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO

–insert records

INSERT INTO emps

SELECT ‘rITESH’,‘MIS’,‘ECHEM’ UNION ALL

SELECT ‘Rajan’,‘MIS’,‘mar’

–creating stored procedure which return emp name

CREATE PROC [dbo].[getEmpList]

AS

SELECT Name FROM emps

–checking SP

EXEC getemplist

2.) VB.NET code for windows application to utilize SP of SQL Server 2005.

‘draw one button name ‘Button1’ in your windows form and use below event for it

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection

conn.ConnectionString = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”

conn.Open()

Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand

cmd.CommandType = System.Data.CommandType.StoredProcedure

cmd.Connection = conn

cmd.CommandText = “[getEmpList]”

Dim reader As System.Data.SqlClient.SqlDataReader

reader = cmd.ExecuteReader

Dim XPos As Integer = 0

Dim YPos As Integer = 0

Do While reader.Read()

XPos = 9

YPos = YPos + 35

Dim lblfieldname As System.Windows.Forms.Label = New System.Windows.Forms.Label

lblfieldname.Text = reader(“Name”).ToString()

lblfieldname.Location = New System.Drawing.Point(XPos, YPos)

lblfieldname.Size = New System.Drawing.Size(120, 23)

Me.Controls.Add(lblfieldname)

XPos = XPos + 20

YPos = YPos + 35

Loop

End Sub

Happy Programming!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Stored Procedure return value handle and record set handle in C# ASP.NET

If you are SQL Server geek, you must be aware with Stored Procedure. While question comes to implement stored procedure in front end language, many developers makes mistake or feel confusion as per my recent observation so I am tempted to write one article which can show how to handle stored procedure in C# when it return some value and when it return some record sets.

–CREATE TABLE

CREATE TABLE empDemo

(

ID INT NOT NULL,

NAME VARCHAR(50)

)

GO

–INSERT DATA

INSERT INTO empDemo

SELECT 1,‘Ritesh’ UNION ALL

SELECT 2,‘Rajan’

–create SP which will return list of employee

–NOTE: this is just basic SP, you can create

–your own for your custom need.

CREATE PROC ListEmp (@id INT)

AS

BEGIN

SELECT * FROM empDemo WHERE ID=@id

END

–check SP whether it works.

EXEC ListEmp 2

Now, we will see C# code (in asp.net code behind) to return the list of employee:

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

string strConn = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = “ListEmp”;

command.Parameters.AddWithValue(“@id”, “2”);

//create one data adapter which will execute the command and fill the data into data set

System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();

recordAdp.SelectCommand = command;

System.Data.DataSet recordSet = new System.Data.DataSet();

recordAdp.Fill(recordSet);

command.Dispose();

conn.Close();

//bind gridview1 with our dataset

GridView1.DataSource = recordSet.Tables[0].DefaultView;

GridView1.DataBind();

}

This seems basic but really useful for newbie, now what if we have some other DML command and we want to return value from SP and handle it in our C# ASP.NET.

–create SP to insert record and retun value

–we will handle return value in C# code behind

CREATE PROC InsertEmp(@ID INT, @Name VARCHAR(20))

AS

BEGIN

BEGIN TRY

INSERT INTO empDemo(ID,Name) VALUES(@ID,@Name)

return 1

END TRY

BEGIN CATCH

return 0

END CATCH

END

–check SP works or not

EXEC InsertEmp 3,‘Alka’

Now let us move to ASP.NET C# code behind.

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

string strConn = “Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = “InsertEmp”;

command.Parameters.Add(“@ReturnValue”,System.Data.SqlDbType.Int);

command.Parameters[“@ReturnValue”].Direction = System.Data.ParameterDirection.ReturnValue;

command.Parameters.AddWithValue(“@id”, “4”);

command.Parameters.AddWithValue(“@Name”, “Bihag”);

command.ExecuteScalar();

int i = Convert.ToInt32(command.Parameters[“@ReturnValue”].Value);

if (i == 1)

{

Response.Write(“Successfull!!!!”);

}

else

{

Response.Write(“Not Successfull!!!!”);

}

}

Hope you have enjoyed it!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Power of Stored procedure as an abstract layer in Microsoft SQL Server 2005

In the way of being better developer, I have started these practice couple years back. Abstract layer is nothing than the entry point of the database. No orphan DML statement allowed. Everything should be done with SPs. By this way, you can have more secure way of dealing with database along with very good performance. It increases encapsulation as well.

One more benefit is by keeping your data access contract as it is; you can improve your database schema and server side code. In one of my past company, I have started working in one application which was already developed with one database, I found many correction could be done in database side but I wasn’t able to do so, just because they have directly used SELECT, INSERT and UPDATE command all over the place so any single change in table structure or in database schema could make application running stop. If they would have used abstract layer and would have done everything with SPs, I would have made necessary change without affecting application.

Data abstract layer will need for every operation we could done with database, like INSERT, UPDATE, DELETE, Fetching record sets or single row etc.

You might think that if we would have to create data abstract layer for everything than why shouldn’t we create INSERT, UPDATE, DELETE and SELECT SPs for every table while creating it, I would not suggest it.

Let us see one sample example how can we do this?

I will explain how to create data abstract layer for INSERT data.

–table for demo

CREATE TABLE AbstractExample

(

ID INT IDENTITY(1,1),

Name VARCHAR(50) NOT NULL,

Company VARCHAR(50) NOT NULL

)

–CREATE data abstract interface for INSERT

CREATE PROC Insert_AbsExample(@Name VARCHAR(50),@Company VARCHAR(50))

AS

SET NOCOUNT ON

BEGIN

IF @Name IS NULL

RAISERROR(‘Name should be provided’,15,1)

IF LEN(RTRIM(@Name))<=0

RAISERROR(‘only space is not allowed in Name’,16,1)

IF @Company IS NULL

RAISERROR(‘Company Name should be provided’,15,1)

IF LEN(RTRIM(@Company))<=0

RAISERROR(‘only space is not allowed in Company Name’,16,1)

BEGIN TRY

INSERT INTO AbstractExample (Name,Company) VALUES(@Name,@Company)

END TRY

BEGIN CATCH

RAISERROR(‘Unable to insert the records’,15,1)

END CATCH

END

–let us check functionality

EXEC Insert_AbsExample ‘ ‘,‘SQLHub’

–let us check functionality

EXEC Insert_AbsExample NULL,‘SQLHub’

–let us check functionality

EXEC Insert_AbsExample ‘Ritesh’,‘SQLHub’

–check the table.

SELECT * FROM AbstractExample

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Stored procedure with SUBSTRING in WHILE loop SQL Server 2005

Below given stored procedure will count occurrence of given character in given string.

–stored procedure which will count occurence given character in @CountChar

–from @string variable

CREATE PROC CharCount(@String VARCHAR(50),@CountChar VARCHAR(1))

AS

BEGIN

DECLARE @counter INT

DECLARE @finalCount INT

SET @finalCount=0

SET @counter=LEN(@String)

WHILE @counter>=0

BEGIN

IF @CountChar=SUBSTRING(@String,@counter,1)

BEGIN

SET @finalCount=@finalCount+1

END

SET @counter=@counter1

END

SELECT @finalCount

END

GO

–check the SP

EXEC CharCount ‘ritesh shah from SQLHub.Com’,‘h’

Happy Programming!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Archiving Data in SQL Server 2005

Archiving data is one of the very big topics with lots of challenging. I am trying to give small light on this topic for newbie. In our day to day activity we often don’t need very old data may be few year old data. Question might arise in mind, what should we do for those data? Delete it? Transfer it somewhere else? Deleting wouldn’t be solution in every company as you might need to view those data after few years so better option to move that somewhere in else.

You can archive your data to the same database or else you can create separate database with identical same structure and use it as your archived database. I would prefer second option as it removes the overhead on the same database.

Let us create one simple stored procedure which will archive your data to another table in same database and delete the data from the original table if archiving will done successfully.

–create table for demo

use adventureworks

go

CREATE TABLE ArchiveTest

(

ID INT IDENTITY(1,1) NOT NULL,

NAME VARCHAR(15) NOT NULL,

Deleted BIT NOT NULL DEFAULT 0

)

–CREATE table two for archiving

CREATE TABLE ARCHIVE_ArchiveTest

(

–NOTE: I didn’t define ID column as IDENTITY as we are going

— to archive the records and we can’t enter manual value in IDENTIY column

ID INT NOT NULL,

NAME VARCHAR(15) NOT NULL,

Deleted BIT NOT NULL DEFAULT 0

)

–insert records in above table

INSERT INTO ArchiveTest(Name)VALUES(‘Ritesh’)

INSERT INTO ArchiveTest(Name)VALUES(‘Rajan’)

INSERT INTO ArchiveTest(Name)VALUES(‘Dharmesh’)

–create stored procedure to Archive data

CREATE PROC ArchiveFromTest(@StartCharOfName CHAR(1))

AS

BEGIN

SET NOCOUNT ON

BEGIN TRAN

INSERT ARCHIVE_ArchiveTest

SELECT * FROM ArchiveTest WHERE Name LIKE ‘R%’

IF @@ERROR<>0

BEGIN

ROLLBACK TRAN

RETURN

END

DELETE FROM ArchiveTest WHERE Name LIKE ‘R%’

IF @@ERROR<>0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRAN

RETURN

END

–CHECK whether above stored procedure works

EXEC ArchiveFromTest ‘R’

–finally check your data

SELECT * FROM ArchiveTest

SELECT * FROM aRCHIVE_ArchiveTest

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Difference between Stored Procedure and User Define Function in SQL Server 2005:

I have written many articles on differences and comparison on various topic of Microsoft SQL Server. Here is one more difference between Stored Procedure and User Define Function. Many times I have seen new developer in confusion about usage of functions so after finishing the series of articles in stored procedures and user define functions I thought to write one article for these differences.

Actually before Microsoft SQL Server 2000, there was no concept of UDFs and it has first been revealed in Microsoft SQL Server 2000 to wrap up your complex T-SQL logic but initially people were not started adopt it but I guess they have started focusing in it with Microsoft SQL Server 2005 when UDFs comes up with new features like table valued function and CLR function implementation. Now, the situation is, UDFs become one powerful tool in SQL Server kit.

The main difference I feel between SPs and UDFs is usage in SELECT. One can use UDFs anywhere in JOIN, FROM, WHERE and HAVING clause whereas SPs are not that much flexible.

UDFs are simple to invoke in any T-SQL statement then SPs.

Some DML statements like INSERT, UPDATE and DELETE are not permissible in UDFs whereas you can use it in SPs.

You can not call non-deterministic function of SQL Server inside the UDFs whereas you can call them in SPs. For example GETDATE() function, it is non-deterministic and can’t be called within UDFs.

Stored Procedure can call function but function can’t call stored procedure.

User defined function can have only input parameter whereas SPs can have input as well as output parameter.

You can use Try…Catch in SPs whereas UDF can’t support it.

If you are more interested to learn about SPs and UDFs than you can sear http://www.sqlhub.com for more details as I wrote dozens of article on this topic with so many real life examples.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

BSA (Body Surface Area) calculation in stored procedure with OUTPUT parameter in Microsoft SQL Server 2005:

Herewith, I am giving you one more example of OUTPUT parameter in Stored Procedure for calculating BSA (Body Surface Area). You are in development of medical software than BSA is not a new term for you. If your body surface are is between 1 to 2 than it is normal as per my little knowledge about BSA. I am giving a sample example which will calculate BSA based on the given height and weight. Height and Weight should be either in kg (weight) and cm (height) or in lbs (weight) and inch (height). You can make it more customize by giving more dynamic conversions.

–CREATING stored procedure to return BSA (Body Surface Area)

–The calculation is from the formula of DuBois and DuBois:

–BSA = (W 0.425 x H 0.725) x 0.007184

–where the weight is in kilograms and the height is in centimeters.

–DuBois D, DuBois EF. A formula to estimate the approximate surface area

–if height and weight be known. Arch Intern Medicine. 1916; 17:863-71.

–Wang Y, Moss J, Thisted R. Predictors of body surface area.

CREATE PROC CalcBSA

@option INT,

@weight FLOAT,

@height FLOAT,

@bsa FLOAT OUTPUT

AS

SET NOCOUNT ON

–if weight and height are in kg and cm accordingly

IF @option=1

BEGIN

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

–if weight and height are in lbs and inch accordigly

ELSE

BEGIN

SET @weight=(@weight/2.2046)

SET @height=@height*2.54

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

GO

–once you done with creating stored procedure, let us see whether actually it works!!!!

DECLARE @BSA FLOAT

EXECUTE calcbsa 1,84,180,@BSA OUTPUT

PRINT @BSA

GO

If you are new to stored procedure and wants to study it than do have a look at my following basic articles. Those articles contain from basic definition of stored procedure to different usage of SP.

http://www.sqlhub.com/2009/03/stored-procedure-in-microsoft-sql.html

http://www.sqlhub.com/2009/03/return-data-with-output-parameter-from.html

http://www.sqlhub.com/2009/03/dml-insert-with-multiple-ways-in-sql.html

http://www.sqlhub.com/2009/03/delete-many-multiple-records-in-bunch.html

http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

http://www.sqlhub.com/2009/03/create-your-own-system-stored-procedure.html

Reference: Ritesh Shah
http://www.SQLHub.Com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah