Friday, March 19, 2010

Generate Select and Update Procedure

Generates Select Procedure
Use the this link create-parameters to create paramaters for this.

declare @ProcName varchar(250)
declare @SchemaName varchar(250)
DECLARE @NewLineChar AS CHAR(2)
DECLARE @HorizontalTabChar AS CHAR(1)
DECLARE @VerticalTabChar AS CHAR(1)
DECLARE @ColNames AS varchar(8000)
DECLARE @TableName AS varchar(200)

SET @ProcName = 'Getcompany'
SET @SchemaName = 'dbo'
SET @NewLineChar =  CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)
set @VerticalTabChar = CHAR(11)
SET @ColNames = REPLICATE(@HorizontalTabChar, 3)
SET @TableName = 'company'

SELECT
@ColNames = @ColNames + syscolumns.name + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
where sysobjects.ID = OBJECT_ID(@TableName)

SET @ColNames = Left(@ColNames, Len(@ColNames)-6)

PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[' +@SchemaName+ '].[' + @ProcName +']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
+ @NewLineChar + 'BEGIN'
+ @NewLineChar + @HorizontalTabChar + 'DROP PROCEDURE [' +@SchemaName+ '].[' + @ProcName +'] '
+ @NewLineChar + 'END'
+ @NewLineChar + 'GO'
+ @NewLineChar
+ @NewLineChar + 'CREATE PROCEDURE [' +@SchemaName+ '].[' + @ProcName +']'
+ @NewLineChar + 'AS'
+ @NewLineChar + @HorizontalTabChar + ' BEGIN'
+ @NewLineChar + REPLICATE(@HorizontalTabChar, 2) + ' SELECT'
+ @NewLineChar + @ColNames
+ @NewLineChar + REPLICATE(@HorizontalTabChar, 2)+  'FROM ' + @TableName
+ @NewLineChar + @HorizontalTabChar + ' END'

Generates Update Procedure
To pass parameters to the Update Procedure use the link to generate Parameters.
Generate Parameters
DECLARE @ProcName             VARCHAR(250)
DECLARE @SchemaName           VARCHAR(250)
DECLARE @NewLineChar AS       CHAR(2)
DECLARE @HorizontalTabChar AS CHAR(1)
DECLARE @VerticalTabChar AS   CHAR(1)
DECLARE @ColNames AS          VARCHAR(8000)
DECLARE @TableName AS         VARCHAR(200)

SET @ProcName          = 'zxcvbnm'
SET @SchemaName        = 'dbo'
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)
SET @VerticalTabChar   = CHAR(11)
SET @ColNames          = REPLICATE(@HorizontalTabChar, 3)
SET @TableName   = 'company'

SELECT @ColNames       = @ColNames + syscolumns.name + ' = @' + syscolumns.name + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)
FROM   sysobjects
JOIN syscolumns
ON     sysobjects.id = syscolumns.id
WHERE  sysobjects.ID        = OBJECT_ID(@TableName)

SET @ColNames = LEFT(@ColNames, LEN(@ColNames)-6)

PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[' +@SchemaName+ '].[' + @ProcName +']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
+ @NewLineChar + 'BEGIN'
+ @NewLineChar + @HorizontalTabChar + 'DROP PROCEDURE [' +@SchemaName+ '].[' + @ProcName +'] '
+ @NewLineChar + 'END'
+ @NewLineChar + 'GO'
+ @NewLineChar
+ @NewLineChar + 'CREATE PROCEDURE [' +@SchemaName+ '].[' + @ProcName +']'
+ @NewLineChar + 'AS'
+ @NewLineChar + @HorizontalTabChar + ' BEGIN'
+ @NewLineChar + REPLICATE(@HorizontalTabChar, 2) + ' UPDATE ' + @TableName
+ @NewLineChar + REPLICATE(@HorizontalTabChar, 3) + 'SET'
+ @NewLineChar + @ColNames
+ @NewLineChar + @HorizontalTabChar + ' END'

No comments: