Prasad Bhogadi (1/28/2008)

---

Hello Rod,

Thank you for the comments, I see what you were doing! You tried to generate the stored procedure for a table that has no Identity defined and as you might have noticed in my description about the stored procedure "Script asssumes that it has a primary key with auto identity defined".

If you try generating the stored procedure for a table that has Identity defined for the primary key, you will be able to generate a complete stored procedure script with BEGIN and END.

Thanking you for your suggestions and I would incorporate the logic to handle tables without IDENTITY column.

Thanks
Prasad

CREATE PROCEDURE InsUpd_ChopDates
@Chop_Date date
NULL
INSERT INTO dbo.ChopDates (
Chop_Date
)
VALUES (
@Chop_Date
NULL

UPDATE dbo.ChopDates SET
Chop_Date = @Chop_Date
NULL

/*
----------------------------------------------------------------------------------------------------------------------------------------
Author : Prasad Bhogadi
Name of the Procedure : spGenerateInsUpdateScript
----------------------------------------------------------------------------------------------------------------------------------------
Purpose :This Procedure is used generate Insert Update scripts for a tablet
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------
Input Parameters : Table Name
Expected Output : Generate script for Insert Update Stored procedure for a given table
---------------------------------------------------------------------------------------------------------------------------------------- */

DROP PROC spGenerateInsUpdateScript
GO

CREATE PROCEDURE spGenerateInsUpdateScript
@objname nvarchar(776) --object name we're after

as
SET NOCOUNT ON
DECLARE @shortObjName nVarChar(776) --strip off schema if present
DECLARE @periodPosition tinyint
SET @shortObjName = @objname
SET @periodPosition = CHARINDEX('.',@objname)
If (@periodPosition > 1) -- if there is a schema
BEGIN
SET @shortObjName = SUBSTRING(@objname,@periodPosition + 1,776)
END
DECLARE @objid int
DECLARE @sysobj_type char(2)
SELECT @objid = id, @sysobj_type = xtype
from sysobjects
where id = object_id(@objname)
DECLARE @colname sysname
SELECT @colname = name
from syscolumns
where id = @objid and colstat & 1 = 1

-- DISPLAY COLUMN IF TABLE / VIEW
if @sysobj_type in ('S ','U ','V ','TF','IF')
begin

-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
DECLARE @numtypes nvarchar(80)
DECLARE @avoidlength nvarchar(80)
SELECT @numtypes = N'decimalreal,money,float,numeric,smallmoney'
SELECT @avoidlength = N'int,smallint,datatime,smalldatetime,text,bit'

---- INFO FOR EACH COLUMN
--CREATE TABLE MyProc
--(pkey INT NOT NULL IDENTITY (1, 1),
--ID INT ,
--MyStatement NVARCHAR(4000))
--
-- The "INSERT" routine

INSERT INTO MyProc (MyStatement)
SELECT '/* spInsert' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spInsert' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'
INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spInsert' + @shortobjname + ' '

INSERT INTO MyProc (MyStatement)
SELECT ' @' + name + ' ' +
type_name(xusertype) + ' '
+ case when charindex(type_name(xtype),@avoidlength) > 0
then ''
else
case when charindex(type_name(xtype), @numtypes) <= 0
then '(' + convert(varchar(10), length / 2) + ')' else '(' +
case when charindex(type_name(xtype), @numtypes) > 0
then convert(varchar(5),ColumnProperty(id, name, 'precision'))
else '' end + case when charindex(type_name(xtype), @numtypes) > 0 then ',' else ' ' end +
case
when charindex(type_name(xtype), @numtypes) > 0
then convert(varchar(5),OdbcScale(xtype,xscale))
else ''
end + ')'
end
end + ', '
from syscolumns where id = @objid and number = 0 order by colid
update MyProc set MyStatement = Replace(MyStatement,', ',' ') where
pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT 'AS '
--BEGIN
--IF @' + @colname + ' <= 0
--BEGIN'
INSERT INTO MyProc (MyStatement)
SELECT ' INSERT INTO dbo.' + @objname + ' ('
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 4 and MyStatement like '%' + @colname + '%'
update MyProc set MyStatement = Replace(MyStatement,',','')
where pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT ' )'
INSERT INTO MyProc (MyStatement)
SELECT ' VALUES ('
INSERT INTO MyProc (MyStatement)
SELECT ' @' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 7 and MyStatement like '%' + @colname + '%'
update MyProc set MyStatement = Replace(MyStatement,'@DateCreated,','GETDATE(),')
where ID = 7 AND MyStatement like '%@DateCreated,'
update MyProc set MyStatement = Replace(MyStatement,'@DateModified,','GETDATE(),')
where ID = 7 AND MyStatement like '%@DateModified,'
update MyProc
set MyStatement = Replace(MyStatement,',','')
where pkey = (SELECT max(pkey) from MyProc)
-- SET @colname = @@IDENTITY
INSERT INTO MyProc (MyStatement)
SELECT ')'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '

INSERT INTO MyProc (MyStatement)
SELECT ' '
INSERT INTO MyProc (MyStatement)
SELECT ' '


-- The "UPDATE" routine

INSERT INTO MyProc (MyStatement)
SELECT '/* spUpdate' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spUpdate' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'

INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spUpdate' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'AS UPDATE ' + @objname
INSERT INTO MyProc (MyStatement)
SELECT 'SET '
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ' = @' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%' + @colname + '%'
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%DateCreated %'
update MyProc set MyStatement = Replace(MyStatement,'@DateModified,','GETDATE(),')
where ID = 11 AND MyStatement like '%@DateModified,'
update MyProc set MyStatement = Replace(MyStatement,',',' ')
where pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT ' WHERE ID = @ID'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT ' '


-- The "SELECT" routine

INSERT INTO MyProc (MyStatement)
SELECT '/* spGet' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spGet' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'

INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spGet' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'AS Select '
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%' + @colname + '%'
INSERT INTO MyProc (MyStatement)
SELECT ' WHERE ID = @ID'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT ' '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spInsert' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spUpdate' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spGet' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '



-- disply the results into the output
-- SELECT MyStatement from MyProc ORDER BY ID
end
GO

DROP TABLE dbo.MyProc
GO
CREATE TABLE MyProc
(pkey INT NOT NULL IDENTITY (1, 1),
ID INT ,
MyStatement NVARCHAR(4000))

EXEC spGenerateInsUpdateScript N'dbo.Table1'
GO
EXEC spGenerateInsUpdateScript N'ref.Table2'
GO

Select MyStatement
FROM dbo.MyProc
Order by ID
Go

DROP TABLE dbo.MyProc
GO