ALTER PROCEDURE [dbo].[StoredProcedureGeneratorWithIdentityAndAlter]
-- Description: <Stored procedure for StoredProcedureGeneratorWithIdentityAndAlter>
AS
DECLARE @test_name varchar(max),@test_columnname varchar(max),@columns varchar(max),@functionname varchar(max)
DECLARE @primarykeyname varchar(max),@CHARACTER_MAXIMUM_LENGTH varchar(max),@NUMERIC_PRECISION varchar(max),@NUMERIC_SCALE varchar(max)
DECLARE @varset varchar(max),@varadd varchar(max),@DATA_TYPE varchar(max),@primaryType varchar(max),@typeOfProcedure varchar(max)
DECLARE @vardeclForAdd varchar(max), @vardeclForEdit varchar(max),@columnsForAdd varchar(max)
SET @columns=''
SET @columnsForAdd=''
SET @primarykeyname=''
SET @functionname=''
SET @primaryType=''
-------------
SET @CHARACTER_MAXIMUM_LENGTH=''
SET @NUMERIC_PRECISION=''
SET @NUMERIC_SCALE =''
SET @vardeclForAdd=''
SET @vardeclForEdit=''
SET @varset=''
SET @varadd=''
---------------------
DECLARE test_cursor CURSOR FOR
SELECT TABLE_NAME FROM information_schema.Tables WHERE (TABLE_NAME = 'tbl_a')
OPEN test_cursor
FETCH NEXT FROM test_cursor
INTO @test_name
WHILE @@FETCH_STATUS = 0
BEGIN
IF((SELECT COUNT(COLUMN_NAME) from information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME=@test_name AND CONSTRAINT_NAME= 'PK_'+@test_name)<>0)
BEGIN
SELECT @primarykeyname=COLUMN_NAME from information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME=@test_name AND CONSTRAINT_NAME= 'PK_'+@test_name
END
---------------------------------------------------
DECLARE testcolumn_cursor CURSOR FOR
select column_name,ISNULL(CHARACTER_MAXIMUM_LENGTH,''),ISNULL(NUMERIC_PRECISION,''),ISNULL(NUMERIC_SCALE,''),DATA_TYPE from information_schema.columns
where table_name = @test_name
order by ordinal_position
OPEN testcolumn_cursor
FETCH NEXT FROM testcolumn_cursor
INTO @test_columnname,@CHARACTER_MAXIMUM_LENGTH ,@NUMERIC_PRECISION,@NUMERIC_SCALE,@DATA_TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@columns='')
BEGIN
IF(@primarykeyname='')
BEGIN
SET @primarykeyname=@test_columnname
SET @primaryType=@DATA_TYPE
END
ELSE
BEGIN
IF(@primarykeyname=@test_columnname)
BEGIN
SET @primaryType=@DATA_TYPE
END
END
SET @columns=CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9)+CHAR(9)+@test_columnname
IF((SELECT COUNT(Name)
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 AND OBJECT_NAME(id)=@test_name AND Name=@test_columnname)=0)
BEGIN
SET @columnsForAdd =CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9)+CHAR(9)+@test_columnname
SET @varadd=CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9)+CHAR(9)+'@'+@test_columnname
SET @vardeclForAdd=CHAR(13)+CHAR(10)+' @'+@test_columnname+' '+@DATA_TYPE
SET @vardeclForEdit=CHAR(13)+CHAR(10)+' @'+@test_columnname+' '+@DATA_TYPE
END
ELSE
BEGIN
IF(@primarykeyname=@test_columnname)
BEGIN
SET @vardeclForEdit=CHAR(13)+CHAR(10)+' @'+@test_columnname+' '+@DATA_TYPE
END
END
END
ELSE
BEGIN
IF(@primarykeyname=@test_columnname)
BEGIN
SET @primaryType=@DATA_TYPE
END
SET @columns=@columns+','+CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9)+CHAR(9)+@test_columnname
IF((SELECT COUNT(Name)
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 AND OBJECT_NAME(id)=@test_name AND Name=@test_columnname)=0)
BEGIN
--(
IF(@columnsForAdd='')
BEGIN
SET @columnsForAdd =@test_columnname
SET @varadd='@'+@test_columnname
END
ELSE
BEGIN
SET @columnsForAdd =@columnsForAdd+','+CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9)+CHAR(9)+@test_columnname
SET @varadd=@varadd+', '+CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9)+CHAR(9)+'@'+@test_columnname
END
--)
--(
IF(@varset='')
BEGIN
SET @varset= @test_columnname+' = @'+@test_columnname
END
ELSE
BEGIN
SET @varset=@varset+','+CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9)+CHAR(9)+ @test_columnname+' = @'+@test_columnname
END
--)
IF(@vardeclForAdd='')
BEGIN
SET @vardeclForAdd=CHAR(13)+CHAR(10)+'@'+@test_columnname+' '+@DATA_TYPE
END
ELSE
BEGIN
SET @vardeclForAdd=@vardeclForAdd+' ,'+CHAR(13)+CHAR(10)+' @'+@test_columnname+' '+@DATA_TYPE
END
--)
--(
IF(@vardeclForEdit='')
BEGIN
SET @vardeclForEdit=CHAR(13)+CHAR(10)+'@'+@test_columnname+' '+@DATA_TYPE
END
ELSE
BEGIN
SET @vardeclForEdit=@vardeclForEdit+' ,'+CHAR(13)+CHAR(10)+' @'+@test_columnname+' '+@DATA_TYPE
END
--)
END
ELSE
BEGIN
--(
IF(@primarykeyname=@test_columnname)
BEGIN
--(
IF(@vardeclForEdit='')
BEGIN
SET @vardeclForEdit='@'+@test_columnname+' '+@DATA_TYPE
END
ELSE
BEGIN
SET @vardeclForEdit=@vardeclForEdit+' ,'+CHAR(13)+CHAR(10)+' @'+@test_columnname+' '+@DATA_TYPE
END
--)
END
--)
END
--)
END
---------------------------------------------------------------------
IF(@DATA_TYPE='varchar' or @DATA_TYPE='binary' or @DATA_TYPE='char' or @DATA_TYPE='nchar' or @DATA_TYPE='nvarchar' or @DATA_TYPE='varbinary')
BEGIN
IF(@CHARACTER_MAXIMUM_LENGTH=-1)
BEGIN
--(
IF((SELECT COUNT(Name)
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 AND OBJECT_NAME(id)=@test_name AND Name=@test_columnname)=0)
BEGIN
SET @vardeclForAdd=@vardeclForAdd+'(MAX)'
SET @vardeclForEdit=@vardeclForEdit+'(MAX)'
END
ELSE
BEGIN
IF(@primarykeyname=@test_columnname)
BEGIN
SET @vardeclForEdit=@vardeclForEdit+'(MAX)'
END
END
--)
IF(@primarykeyname=@test_columnname)
BEGIN
SET @primaryType=@primaryType+'(MAX)'
END
END
ELSE
BEGIN
--(
IF((SELECT COUNT(Name)
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 AND OBJECT_NAME(id)=@test_name AND Name=@test_columnname)=0)
BEGIN
SET @vardeclForAdd=@vardeclForAdd+'('+@CHARACTER_MAXIMUM_LENGTH+')'
SET @vardeclForEdit=@vardeclForEdit+'('+@CHARACTER_MAXIMUM_LENGTH+')'
END
ELSE
BEGIN
IF(@primarykeyname=@test_columnname)
BEGIN
SET @vardeclForEdit=@vardeclForEdit+'('+@CHARACTER_MAXIMUM_LENGTH+')'
END
END
--)
IF(@primarykeyname=@test_columnname)
BEGIN
SET @primaryType=@primaryType+'('+@CHARACTER_MAXIMUM_LENGTH+')'
END
END
END
ELSE IF(@DATA_TYPE='decimal' or @DATA_TYPE='numeric')
BEGIN
IF((SELECT COUNT(Name)
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 AND OBJECT_NAME(id)=@test_name AND Name=@test_columnname)=0)
BEGIN
SET @vardeclForAdd=@vardeclForAdd+'('+@NUMERIC_PRECISION+','+@NUMERIC_SCALE+')'
SET @vardeclForEdit=@vardeclForEdit+'('+@NUMERIC_PRECISION+','+@NUMERIC_SCALE+')'
END
ELSE
BEGIN
IF(@primarykeyname=@test_columnname)
BEGIN
SET @vardeclForEdit=@vardeclForEdit+'('+@NUMERIC_PRECISION+','+@NUMERIC_SCALE+')'
END
END
IF(@primarykeyname=@test_columnname)
BEGIN
SET @primaryType=@primaryType+'('+@NUMERIC_PRECISION+','+@NUMERIC_SCALE+')'
END
END
-------------------------------------------------------------------
SET @CHARACTER_MAXIMUM_LENGTH=''
SET @NUMERIC_PRECISION=''
SET @NUMERIC_SCALE =''
FETCH NEXT FROM testcolumn_cursor
INTO @test_columnname,@CHARACTER_MAXIMUM_LENGTH ,@NUMERIC_PRECISION,@NUMERIC_SCALE ,@DATA_TYPE
END
CLOSE testcolumn_cursor
DEALLOCATE testcolumn_cursor
SET @functionname=SUBSTRING(@test_name,5,LEN(@test_name))
--VIEW ALL
SET @typeOfProcedure='CREATE'
IF((Select count(name) from sysobjects where type = 'P' and category = 0 and name=@functionname +'ViewAll')<>0)
BEGIN
SET @typeOfProcedure='ALTER'
END
execute(@typeOfProcedure+' PROCEDURE '+ @functionname +'ViewAll
AS
SELECT '
+@columns+
' FROM '
+@test_name)
--VIEW
SET @typeOfProcedure='CREATE'
IF((Select count(name) from sysobjects where type = 'P' and category = 0 and name=@functionname +'View')<>0)
BEGIN
SET @typeOfProcedure='ALTER'
END
execute(@typeOfProcedure+
' PROCEDURE '+ @functionname +'View
@'+@primarykeyname+' '+@primaryType+'
AS
SELECT '+@columns+' FROM '+@test_name+' WHERE '+@primarykeyname+'= @'+@primarykeyname)
--DELETE
SET @typeOfProcedure='CREATE'
IF((Select count(name) from sysobjects where type = 'P' and category = 0 and name=@functionname +'Delete')<>0)
BEGIN
SET @typeOfProcedure='ALTER'
END
EXECUTE(@typeOfProcedure+
' PROCEDURE '+ @functionname +'Delete
@'+@primarykeyname+' '+@primaryType+'
AS
DELETE FROM '+@test_name+' WHERE '+@primarykeyname+'= @'+@primarykeyname)
--MAX
IF((SELECT COUNT(Name)
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1 AND OBJECT_NAME(id)=@test_name AND Name=@primarykeyname)=0)
BEGIN
SET @typeOfProcedure='CREATE'
IF((Select count(name) from sysobjects where type = 'P' and category = 0 and name=@functionname +'Max')<>0)
BEGIN
SET @typeOfProcedure='ALTER'
END
EXECUTE(@typeOfProcedure+
' PROCEDURE '+ @functionname +'Max
AS
SELECT ISNULL( MAX('+@primarykeyname+'+1),1)
FROM '+@test_name)
END
--ADD
SET @typeOfProcedure='CREATE'
IF((Select count(name) from sysobjects where type = 'P' and category = 0 and name=@functionname +'Add')<>0)
BEGIN
SET @typeOfProcedure='ALTER'
END
execute(
@typeOfProcedure+' PROCEDURE '+ @functionname +'Add
'+@vardeclForAdd+'
AS
INSERT INTO '+@test_name+'
('+@columnsForAdd+')
VALUES
('+@varadd+')'
)
--EDIT
SET @typeOfProcedure='CREATE'
IF((Select count(name) from sysobjects where type = 'P' and category = 0 and name=@functionname +'Edit')<>0)
BEGIN
SET @typeOfProcedure='ALTER'
END
execute(@typeOfProcedure+
' PROCEDURE '+ @functionname +'Edit '
+@vardeclForEdit+'
AS
UPDATE '+@test_name+'
SET '+@varset+'
WHERE '+@primarykeyname+'= @'+@primarykeyname
)
SET @columns=''
SET @columnsForAdd=''
SET @functionname=''
SET @primarykeyname=''
SET @varset=''
SET @varadd=''
SET @vardeclForAdd=''
SET @vardeclForEdit=''
---------------------------------------------------
FETCH NEXT FROM test_cursor
INTO @test_name
END
CLOSE test_cursor
DEALLOCATE test_cursor
No comments:
Post a Comment