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.
