CREATE FUNCTION [dbo].[FN_CreateSPforInsertData]
(
-- You need to pass two parameter, firts is table name, and second is procedurate which name you want to give it.
@TableName NVARCHAR(257),
@ProcedureName NVARCHAR(257)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @PRMNAME NVARCHAR(1000)
DECLARE @PrmA1 NVARCHAR(1000) = '' --It will contain parameters ex @PramA1 = '@Id,@EmployeeName,....'
DECLARE @PROC_Name NVARCHAR(100) = @ProcedureName --Procedure name
DECLARE @RESULT NVARCHAR(MAX) = '' -- Final procedure script
DECLARE @CurrentCLMNAME NVARCHAR(100) -- It will be used in loop for storing the current column name
DECLARE @CLMNAMES NVARCHAR(MAX) = '' -- It will contain the column name ex @CLMNAMES = 'Id, EmployeeName'
DECLARE @VALUES NVARCHAR(MAX) = '' --It will contain values ex @VALUES = '@Id,@EmployeeName,....'
DECLARE @i INT = 0
DECLARE @j INT = 0
DECLARE @IdentityColumn NVARCHAR(50) = ''
--LOOP FOR GET TABLE FILD NAMES AND CREATE FORMAT AS @FildName1,@FildName2...
DECLARE CUR_LOOP CURSOR
FOR SELECT '@'+ CASE WHEN (data_type = 'nvarchar' OR data_type = 'varchar') then (column_name + ' '+ data_type +
'('+
(CASE WHEN (CONVERT(NVARCHAR(5),CHARacter_maximum_length) = -1 )
THEN 'MAX'
ELSE CONVERT(NVARCHAR(5),CHARacter_maximum_length)
END
)+')') else (column_name + ' '+ data_type) END AS [ClmType]
FROM information_schema.columns
WHERE table_name = @TableName
OPEN CUR_LOOP
FETCH NEXT FROM CUR_LOOP INTO @PRMNAME
WHILE @@FETCH_STATUS=0
BEGIN
IF(@j<>0)
BEGIN
SET @PrmA1 = @PrmA1 + @PRMNAME+','+CHAR(13)+' '
END
SET @j = @j + 1
FETCH NEXT FROM CUR_LOOP INTO @PRMNAME
END
CLOSE CUR_LOOP
DEALLOCATE CUR_LOOP
--END
--LOOP FOR GET TABLE FILD NAMES AND CREATE FORMAT AS SET FildName1 = @FildName1,FildName2 = @FildName2...
DECLARE CUR_LOOP CURSOR
FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@TableName
OPEN CUR_LOOP
FETCH NEXT FROM CUR_LOOP INTO @CurrentCLMNAME
WHILE @@FETCH_STATUS=0
BEGIN
IF(@i<>0)
BEGIN
SET @VALUES = @VALUES +'@'+@CurrentCLMNAME+','+CHAR(13)
SET @CLMNAMES = @CLMNAMES + @CurrentCLMNAME+','+CHAR(13)
SET @RESULT = @RESULT + @CurrentCLMNAME + ' = @'+ @CurrentCLMNAME + ','+CHAR(13)+' '
END
ELSE
BEGIN
SET @IdentityColumn = @CurrentCLMNAME
END
SET @i = @i + 1
FETCH NEXT FROM CUR_LOOP INTO @CurrentCLMNAME
END
CLOSE CUR_LOOP
DEALLOCATE CUR_LOOP
--END
--CREATE THE FORMAT OF PROCEDURE WITH THE HELP OF @PrmA1 AND Result... variables...
-- +CHAR(13)+ is for new line
SET @RESULT = 'CREATE PROC '+@PROC_Name
+CHAR(13)+'('
+CHAR(13)+' '+ SUBSTRING(@PrmA1,0,(LEN(@PrmA1)-1))
+CHAR(13)+')'
+CHAR(13)+'AS'
+CHAR(13)+'BEGIN'
+CHAR(13)+' INSERT INTO '+@TableName
+CHAR(13)+'('
+CHAR(13)+ SUBSTRING(@CLMNAMES,0,(LEN(@CLMNAMES)-1))
+CHAR(13)+')'
+CHAR(13)+'VALUES('
+CHAR(13)+ SUBSTRING(@VALUES,0,(LEN(@VALUES)-1))
+CHAR(13)+')'
+CHAR(13)+'END'
RETURN @RESULT
END
How to run the Function?
select [dbo].[FN_CreateSPforInsertData] ('YourStateTableName','Ups_ProcedureName')
(
-- You need to pass two parameter, firts is table name, and second is procedurate which name you want to give it.
@TableName NVARCHAR(257),
@ProcedureName NVARCHAR(257)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @PRMNAME NVARCHAR(1000)
DECLARE @PrmA1 NVARCHAR(1000) = '' --It will contain parameters ex @PramA1 = '@Id,@EmployeeName,....'
DECLARE @PROC_Name NVARCHAR(100) = @ProcedureName --Procedure name
DECLARE @RESULT NVARCHAR(MAX) = '' -- Final procedure script
DECLARE @CurrentCLMNAME NVARCHAR(100) -- It will be used in loop for storing the current column name
DECLARE @CLMNAMES NVARCHAR(MAX) = '' -- It will contain the column name ex @CLMNAMES = 'Id, EmployeeName'
DECLARE @VALUES NVARCHAR(MAX) = '' --It will contain values ex @VALUES = '@Id,@EmployeeName,....'
DECLARE @i INT = 0
DECLARE @j INT = 0
DECLARE @IdentityColumn NVARCHAR(50) = ''
--LOOP FOR GET TABLE FILD NAMES AND CREATE FORMAT AS @FildName1,@FildName2...
DECLARE CUR_LOOP CURSOR
FOR SELECT '@'+ CASE WHEN (data_type = 'nvarchar' OR data_type = 'varchar') then (column_name + ' '+ data_type +
'('+
(CASE WHEN (CONVERT(NVARCHAR(5),CHARacter_maximum_length) = -1 )
THEN 'MAX'
ELSE CONVERT(NVARCHAR(5),CHARacter_maximum_length)
END
)+')') else (column_name + ' '+ data_type) END AS [ClmType]
FROM information_schema.columns
WHERE table_name = @TableName
OPEN CUR_LOOP
FETCH NEXT FROM CUR_LOOP INTO @PRMNAME
WHILE @@FETCH_STATUS=0
BEGIN
IF(@j<>0)
BEGIN
SET @PrmA1 = @PrmA1 + @PRMNAME+','+CHAR(13)+' '
END
SET @j = @j + 1
FETCH NEXT FROM CUR_LOOP INTO @PRMNAME
END
CLOSE CUR_LOOP
DEALLOCATE CUR_LOOP
--END
--LOOP FOR GET TABLE FILD NAMES AND CREATE FORMAT AS SET FildName1 = @FildName1,FildName2 = @FildName2...
DECLARE CUR_LOOP CURSOR
FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@TableName
OPEN CUR_LOOP
FETCH NEXT FROM CUR_LOOP INTO @CurrentCLMNAME
WHILE @@FETCH_STATUS=0
BEGIN
IF(@i<>0)
BEGIN
SET @VALUES = @VALUES +'@'+@CurrentCLMNAME+','+CHAR(13)
SET @CLMNAMES = @CLMNAMES + @CurrentCLMNAME+','+CHAR(13)
SET @RESULT = @RESULT + @CurrentCLMNAME + ' = @'+ @CurrentCLMNAME + ','+CHAR(13)+' '
END
ELSE
BEGIN
SET @IdentityColumn = @CurrentCLMNAME
END
SET @i = @i + 1
FETCH NEXT FROM CUR_LOOP INTO @CurrentCLMNAME
END
CLOSE CUR_LOOP
DEALLOCATE CUR_LOOP
--END
--CREATE THE FORMAT OF PROCEDURE WITH THE HELP OF @PrmA1 AND Result... variables...
-- +CHAR(13)+ is for new line
SET @RESULT = 'CREATE PROC '+@PROC_Name
+CHAR(13)+'('
+CHAR(13)+' '+ SUBSTRING(@PrmA1,0,(LEN(@PrmA1)-1))
+CHAR(13)+')'
+CHAR(13)+'AS'
+CHAR(13)+'BEGIN'
+CHAR(13)+' INSERT INTO '+@TableName
+CHAR(13)+'('
+CHAR(13)+ SUBSTRING(@CLMNAMES,0,(LEN(@CLMNAMES)-1))
+CHAR(13)+')'
+CHAR(13)+'VALUES('
+CHAR(13)+ SUBSTRING(@VALUES,0,(LEN(@VALUES)-1))
+CHAR(13)+')'
+CHAR(13)+'END'
RETURN @RESULT
END
How to run the Function?
select [dbo].[FN_CreateSPforInsertData] ('YourStateTableName','Ups_ProcedureName')