Generate the Dynamically Insert / Update T-SQL statement.

There is big problem when no of tables which need to insert / update the records dynamically. User only passes table name , column list with comma seperated char and last one value list with comma seperated char. Here is single sp can create to use for get INSERT / UPDATE (T-SQL Statement) for executting as a SQL Query. SQL developer do not warry about to create larg no of stored procedures for table wise. Here a simple solution given, only single SP can solve the problem. Below SP is return the Insert / Update T-SQL statement for farther process.

Create Spilt Table Function for User in Stored Procedure

CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT ‘Id’ = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
‘Data’ = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
CREATE PROCEDURE [dbo].[ReturnQuery]
— Add the parameters for the stored procedure here
@TableName VARCHAR(100)
,@Columns VARCHAR(100)
,@Values VARCHAR(100)
,@OUTPUT NVARCHAR(500) OUTPUT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @num INT
DECLARE @num1 INT
DECLARE @totalColsPassed INT
DECLARE @totalCols INT
DECLARE @fCol NVARCHAR(100)
DECLARE @fVal NVARCHAR(100)

SET @num = 2

SELECT @num1 = COUNT(*)
FROM dbo.Split(@Columns, ‘,’) tbl

SET @totalColsPassed = (SELECT COUNT(*)
FROM dbo.Split(@Columns, ‘,’) tbl)

SET @totalCols = (SELECT Count(col.NAME)
FROM sys.columns col
INNER JOIN sys.tables table1 ON col.object_id = table1.object_id
WHERE table1.NAME = @tableName)

DECLARE @UpdateSetPart VARCHAR(500) = ‘, ‘

SET @fCol = (
SELECT TOP 1 DATA
FROM dbo.Split(@Columns, ‘,’) TBL
WHERE id = 1
)
SET @fVal = (
SELECT TOP 1 Data
FROM dbo.Split(@Values, ‘,’) TBLVal
WHERE ID = 1
)

IF @fVal <> 0 AND (@totalCols = @totalColsPassed)
BEGIN
WHILE (@num <= @num1)
BEGIN
IF @num > 0
BEGIN
DECLARE @fCol1 NVARCHAR(100)
DECLARE @fVal1 NVARCHAR(100)

SET @fCol1 = (
SELECT TOP 1 DATA
FROM dbo.Split(@Columns, ‘,’) TBL
WHERE ID = @num
)
SET @fVal1 = (
SELECT TOP 1 Data
FROM dbo.Split(@Values, ‘,’) TBLVal
WHERE ID = @num
)

IF @UpdateSetPart = ‘, ‘
BEGIN
SET @UpdateSetPart = @fCol1 + ‘ = ”’ + @fVal1 + ””;
END
ELSE
BEGIN
SET @UpdateSetPart += ‘, ‘ + @fCol1 + ‘ = ”’ + @fVal1 + ””;
END
END

SET @num = @num + 1
END

SET @UpdateSetPart += ‘ WHERE ‘ + @fCol + ‘ = ‘ + @fVal
SET @UpdateSetPart = ‘UPDATE ‘ + @tableName + ‘ SET ‘ + @UpdateSetPart
SET @OUTPUT = Upper(@UpdateSetPart)

SELECT @OUTPUT
END
ELSE
BEGIN

SET @UpdateSetPart = ‘Insert into ‘ + @tableName + ‘(‘ + @Columns + ‘) values (‘ + @Values + ‘)’
SET @OUTPUT = @UpdateSetPart

SELECT @OUTPUT
END
END

 

WAITING FOR FEEDBACK.

I hope you will get some tips while programming with SQL Server. I would like to you, to leave a comment on this post. Your valuable feedback, question, or comments for this article are always welcome.