Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Writing re-runnable sql server scripts - Part 66

Suggested Videos
Part 63 - Cursors in sql server
Part 64 - Replacing cursors using joins in sql server
Part 65 - List all tables in a sql server database using a query

What is a re-runnable sql script?
A re-runnable script is a script, that, when run more than, once will not throw errors.



Let's understand writing re-runnable sql scripts with an example. To create a table tblEmployee in Sample database, we will write the following CREATE TABLE sql script.
USE [Sample]
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)



When you run this script once, the table tblEmployee gets created without any errors. If you run the script again, you will get an error - There is already an object named 'tblEmployee' in the database.

To make this script re-runnable
1. Check for the existence of the table
2. Create the table if it does not exist
3. Else print a message stating, the table already exists

Use [Sample]
If not exists (select * from information_schema.tables where table_name = 'tblEmployee')
Begin
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)
Print 'Table tblEmployee successfully created'
End
Else
Begin
Print 'Table tblEmployee already exists'
End

The above script is re-runnable, and can be run any number of times. If the table is not already created, the script will create the table, else you will get a message stating - The table already exists. You will never get a sql script error.

Sql server built-in function OBJECT_ID(), can also be used to check for the existence of the table
IF OBJECT_ID('tblEmployee') IS NULL
Begin
   -- Create Table Script
   Print 'Table tblEmployee created'
End
Else
Begin
   Print 'Table tblEmployee already exists'
End

Depending on what we are trying to achieve, sometime we may need to drop (if the table already exists) and re-create it. The sql script below, does exactly the same thing.
Use [Sample]
IF OBJECT_ID('tblEmployee') IS NOT NULL
Begin
Drop Table tblEmployee
End
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)

Let's look at another example. The following sql script adds column "EmailAddress" to table tblEmployee. This script is not re-runnable because, if the column exists we get a script error.
Use [Sample]
ALTER TABLE tblEmployee
ADD EmailAddress nvarchar(50)

To make this script re-runnable, check for the column existence
Use [Sample]
if not exists(Select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='EmailAddress' and TABLE_NAME = 'tblEmployee' and TABLE_SCHEMA='dbo'
Begin
ALTER TABLE tblEmployee
ADD EmailAddress nvarchar(50)
End
Else
BEgin
Print 'Column EmailAddress already exists'
End

Col_length() function can also be used to check for the existence of a column
If col_length('tblEmployee','EmailAddress') is not null
Begin
Print 'Column already exists'
End
Else
Begin
Print 'Column does not exist'
End

5 comments:

  1. Hi Venkat can u please upload video about merge with example.Now a days in many interviewer asking this questions.

    ReplyDelete
    Replies
    1. hai Venkat..

      hoping you are doing good. Could you please post videos on Table value parameters, split function..

      i am working as a SSRS developer. I have written stored procedure but i am unable to pass multiple values to parameter of Varchar datatype..i couldn't find helpful blogs to overcome this.

      Delete
  2. Hi Venkat,

    I am not understanding why you have used Table schema in below sql query.
    Can you please provide more explanation please.

    have a great day!!

    ReplyDelete
  3. he used table schema to retrieve the table name from the list of tables if the table exists it wont create or else we will create

    ReplyDelete
  4. sir i have one question kindly reply me soon please sir kindly..
    my question is how to auto increment varchar by using c# and when the user wants to enter data of i.e add employee already id incremented emp001,emp002 in id text box..

    ReplyDelete

It would be great if you can help share these free resources