Today i am sharing a technique that is really a time savior for writing procedures for tables having lots of columns which require an ample of time to make the parameters for each column and then perform required insert update thing on them.
So here goes the technique, this is a simple procedure that takes the name of table and create its Insert update proc on execution.
CREATE procedure [CreateInsertUpdate]
@tablename varchar(800)
AS
declare @vtblcolumns varchar(max)
declare @vcolumns1 varchar(max)
declare @vcolumns2 varchar(max)
declare @vtblDtypes varchar(max)
Declare @vstr varchar(max)
declare @vColumns varchar(max)
Declare @vPKcol varchar(250)
declare @Sid varchar(200)
SELECT @sid = TABLE_NAME FROM information_schema.tables WHERE table_name = @tablename AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') =1
Select @vPKcol = c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @tableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
select @vtblcolumns = COALESCE(@vtblcolumns+',','')+ COLUMN_NAME + '='+'@P'+ COLUMN_NAME + ' 'from information_schema.columns where TABLE_NAME= @tablename AND COLUMN_NAME NOT IN (@vPKcol)
if @sid = null
select @vcolumns1 = COALESCE(@vcolumns1+',','')+ COLUMN_NAME,@vColumns2 = COALESCE(@vColumns2+',','') + '@p'+ Column_NAME from information_schema.columns where TABLE_NAME= @tablename
else
select @vcolumns1 = COALESCE(@vcolumns1+',','')+ COLUMN_NAME,@vColumns2 = COALESCE(@vColumns2+',','') + '@p'+ Column_NAME from information_schema.columns where TABLE_NAME= @tablename and COLUMN_NAME <> @vPKcol
-- Select from information_schema.columns where TABLE_NAME =@tablename
Select @vColumns = COALESCE(@vColumns+',','') +
'@p'+ Column_NAME + ' ' +
Data_Type +
case when Character_Maximum_length > 0 then
'(' + cast(Character_Maximum_length as varchar(10)) + ')'
else ''
end from information_schema.columns where TABLE_NAME =@tablename
Select @vstr =
'
IF Object_ID ( ''UP_'+@tablename+'_InsertUpdate '') IS NOT NULL
DROP Procedure UP_'+@tablename+'_InsertUpdate
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: '+system_user+'
-- Create date: '+convert (varchar (50),getdate(),103)+'
-- Description: This is Auto Generated Code for '+@tablename+' Insert and Update Record for Single Primary Key Table
-- =============================================
CREATE PROCEDURE UP_'+@tablename+'_InsertUpdate
('+@vColumns+ '
)
As
BEGIN
SET NOCOUNT ON;
IF EXISTS (Select '+@vpkcol+' from '+@tablename+ ' where '+@vpkcol+'='+'@p'+@vpkcol+ ')
BEGIN
UPDATE '+@tablename+'
SET '+@vtblcolumns+'
WHERE '+ @vPKcol + '='+'@p'+@vpkcol+ '
END
ELSE
BEGIN
INSERT INTO '+@tablename+ '('+@vcolumns1+')
VALUES ('+@vColumns2+')
SET ' +'@p'+@vPKcol+' = Scope_Identity()
END
RETURN ' +'@p'+@vPKcol+'
End
GO
'
Print @vstr
Suppose we have a table name test_table all we need to do is to pass the name in parameter.
exec CreateInsertUpdate test_table
just copy the text created in messages panel and execute.
Happy coding :)
