T-SQL Script to perform DML Operations (Select, Insert, Update and Delete) based on DB Name

T-SQL script that will Perform DML Operations (Select, Insert, Update and Delete) based on DB Name

Requirement-We were working with Different customers DB where different customers were having Different DB Names but the table name and Structure of Tables were same.

So our requirement was through same web application we need to perform DML Operations (Select, Insert, Update and Delete) based on DB Name passed from front end (i.e. web application).

For Eg

DB-Customer-X

Tables-Employee, Products, Product_Description, Customers……….

DB-Customer-Y

Tables-Employee, Products, Product_Description, Customers……….

DB-Customer-Z

Tables-Employee, Products, Product_Description, Customers……….

So below I created a Stored Procedure Script that used to perform DML in different db based on DB Name passed from Front end

So Below is the Script with two test DB and one Employee Table

--Creating two test DB

Create database Test1

Create Database Test2

--Run Table Script in both the Database

CREATE TABLE [dbo].[Employee1](

     [Empid] [int] IDENTITY(1,1) NOT NULL,

     [EmpName] [nvarchar](50) NULL,

     [EmpSalary] [float] NULL,

 CONSTRAINT [PK_Employee1] PRIMARY KEY CLUSTERED

(

     [Empid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Stored Procedure Script that will will Perform DML Operations (Select, Insert, Update and Delete) based on DB Name Passed.

 --Script Code

--Run this Procedure in DB with which your web application is connected

Create PROCEDURE [dbo].[proc_db_test]

 (

 --Passing DB Name

@dbname varchar(100)=null,

--For Passing Operation insert update delete select

@Operation varchar(100)=null,

--Passing table values

@EmpId int =null,

@EmpName nvarchar(200)=null,

@EmpSalary float=null

 )

 AS

 BEGIN

IF db_id(@dbname) IS NULL

 BEGIN

RAISERROR ('Database %s does not exists!', 16,1, @dbname)

RETURN

END

if @Operation='INSERT'

begin

  EXEC('INSERT INTO '+@dbname+' .dbo.Employee1(EmpName,EmpSalary)

        VALUES('''+@EmpName+''','''+@EmpSalary+''')')

END

else if @Operation='UPDATE'

begin

 exec('update ' +@dbname+'.dbo. Employee1

set EmpName= '''+@EmpName+'''

,EmpSalary= '''+@EmpSalary+''' where EmpId='''+@EmpId+'''')

END

else if  @Operation='DELETE' 

begin

exec('Delete  from  ' +@dbname+'.dbo.Employee1  where EmpId='''+@EmpId+'''')

end

else  if @Operation='SELECTDETAILS'

begin

exec('select * from   ' +@dbname+'.dbo.Employee1  where EmpId='''+@EmpId+'''')

end

else if @Operation='SELECTALL'

begin

exec('select * from ' +@dbname+'.dbo.Employee1 order by Empid')

end

End

Test the Script

Insert Operation in both DB

--Procedure will have following inputs

--Proj_DB_Test DBName OPeration,EmpID,EMPName,EmpSalary

Exec  [dbo].[proc_db_test] 'Test1','Insert',1,'RK','15000'

--Test1 DB Test

Select * from test1.dbo.Employee1

Output

Empid  EmpName       EmpSalary

1          RK        15000

--Procedure will have following inputs

--Proj_DB_Test DBName OPeration,EmpID,EMPName,EmpSalary

Exec  [dbo].[proc_db_test] 'Test2','Insert',1,'RK1','20000'

--Test2 DB Test

Select * from test2.dbo.Employee1

Output

Empid EmpName    EmpSalary

1    RK1  20000

Test Update Operation in both DB

--Updating Name in Test1 DB

Exec  [dbo].[proc_db_test] 'Test1','UPdate',1,'RK_new','15000'

Output

Empid EmpName    EmpSalary

1    RK_new     15000

Test Delete Operation in both DB

--For Testing Delete Operation we will insert one more record in any of DB

--Insert

Exec  [dbo].[proc_db_test] 'Test1','Insert',2,'RK_1','10000'

Output

Empid EmpName    EmpSalary

1    RK_new     15000

2    RK_1 10000

--Deleting above newely added Record

Exec  [dbo].[proc_db_test] 'Test1','Delete',2

Output

Empid EmpName    EmpSalary

1    RK_new     15000

Test Select Operations in both DB

--Test Specific Records of Both DB Employee Table by passing Employee ID

Exec  [dbo].[proc_db_test] 'Test1','SELECTDETAILS',2

Output

Empid EmpName    EmpSalary

1    RK_new     15000

Exec  [dbo].[proc_db_test] 'Test2','SELECTDETAILS',1

Output 

Empid EmpName    EmpSalary

1    RK1  20000

Test Select all Operation in both DB

--Test all Records of Both DB Employee Table

Exec  [dbo].[proc_db_test] 'Test1','SELECTALL'

Output

Empid EmpName    EmpSalary

1    RK_new     15000

Exec  [dbo].[proc_db_test] 'Test2','SELECTALL'

Output

Empid EmpName    EmpSalary

1    RK1  20000

Testing by Passing DB Name that doesn't exist in server-Will Raise the below error

Exec  [dbo].[proc_db_test] 'Test3','SELECTALL'

Msg 50000, Level 16, State 1, Procedure proc_db_test, Line 16

Database Test3 does not exists!

NOTE:The best use of this script when you have multiple customers having different Databases having same table Names and Same table Structure.