IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[test_create_procedure]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[test_create_procedure]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE create_procedure @table VARCHAR(200),
@DeveloperName VARCHAR(200),
@Createtable VARCHAR(20)
--create_procedure 'Products','VISHAL','Products'
AS
SET NOCOUNT ON
DECLARE @testTable VARCHAR(8000)
DECLARE @testTable2 VARCHAR(8000)
DECLARE @testTable3 VARCHAR(8000)
DECLARE @opration VARCHAR(8000)
DECLARE @testTable_UPDATE VARCHAR(8000)
DECLARE @final VARCHAR(8000)
DECLARE @OP VARCHAR(100)
SET @testTable = ''
SET @testTable2 = ''
SET @final = ''
SET @testTable3 = ''
SET @testTable_UPDATE = ''
SET @opration = ''
DECLARE @Datetime VARCHAR(50)
SET @Datetime = getdate()
SELECT @testTable = @testTable + ',
' + column_name
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
SELECT @testTable2 = @testTable2 + ',
@' + column_name + ' ' + data_type + '(' + cast(character_maximum_length AS VARCHAR(10)) + ')' + CASE is_nullable
WHEN 'no'
THEN ' '
WHEN 'yes'
THEN '=null'
END
FROM information_schema.columns
WHERE table_name = @table
AND character_maximum_length <> NULL
AND (column_default IS NULL)
AND data_type <> 'text'
SELECT @testTable2 = @testTable2 + ',
@' + column_name + ' ' + data_type
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
AND (
character_maximum_length = NULL
OR data_type = 'text'
)
SELECT @testTable3 = @testTable3 + ',
@' + column_name
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
SELECT @testTable_UPDATE = @testTable_UPDATE + ',
' + column_name + ' =@' + column_name
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
DECLARE @testTable_UPDATE_IDENTITY NVARCHAR(MAX)
SELECT @testTable_UPDATE_IDENTITY = '
' + column_name + ' =@' + column_name
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 1)
SET @testTable = SUBSTRING(@testTable, 2, len(@testTable))
SET @testTable2 = SUBSTRING(@testTable2, 4, len(@testTable2))
SET @testTable3 = SUBSTRING(@testTable3, 2, len(@testTable3))
SET @testTable_UPDATE = SUBSTRING(@testTable, 2, len(@testTable_UPDATE))
SET @opration = ' insert into [' + @table + ']
(
' + @testTable + '
)
values
(
' + @testTable3 + '
)'
DECLARE @OPERATION_UPDATE NVARCHAR(MAX)
SET @OPERATION_UPDATE = 'UPDATE [' + @table + ']
SET ' + @testTable_UPDATE + ' WHERE [' + @testTable_UPDATE_IDENTITY + ']'
--PRINT @OPERATION_UPDATE
SET @OP = 'InsertNew' + @table
SET @final = '/*
----------------------------------------------------------------------------------------
Store Procedure Name : SP__' + @OP + '
----------------------------------------------------------------------------------------
1- Creation Date :' + convert(VARCHAR, getdate(), 103) + '
2- Last Update :' + convert(VARCHAR, getdate(), 103) + '
3- Parametars No:6
4- Creation By :' + @DeveloperName + '
5- Last Update By :' + @DeveloperName + '
6- Return Value : Dataset
---------------------------------------------------------------------------------------
*/
Create PROCEDURE SP__' + @OP + '
(
' + @testTable2 + '
)
AS
set nocount on
' + @opration + '
---------------------------------------------------------------------------------------
' + @OPERATION_UPDATE + '
Select * from [' + @table + ']'
--exec (@final)
PRINT @final
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--create_procedure 'Products','VISHAL','Products'
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[test_create_procedure]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[test_create_procedure]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE create_procedure @table VARCHAR(200),
@DeveloperName VARCHAR(200),
@Createtable VARCHAR(20)
--create_procedure 'Products','VISHAL','Products'
AS
SET NOCOUNT ON
DECLARE @testTable VARCHAR(8000)
DECLARE @testTable2 VARCHAR(8000)
DECLARE @testTable3 VARCHAR(8000)
DECLARE @opration VARCHAR(8000)
DECLARE @testTable_UPDATE VARCHAR(8000)
DECLARE @final VARCHAR(8000)
DECLARE @OP VARCHAR(100)
SET @testTable = ''
SET @testTable2 = ''
SET @final = ''
SET @testTable3 = ''
SET @testTable_UPDATE = ''
SET @opration = ''
DECLARE @Datetime VARCHAR(50)
SET @Datetime = getdate()
SELECT @testTable = @testTable + ',
' + column_name
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
SELECT @testTable2 = @testTable2 + ',
@' + column_name + ' ' + data_type + '(' + cast(character_maximum_length AS VARCHAR(10)) + ')' + CASE is_nullable
WHEN 'no'
THEN ' '
WHEN 'yes'
THEN '=null'
END
FROM information_schema.columns
WHERE table_name = @table
AND character_maximum_length <> NULL
AND (column_default IS NULL)
AND data_type <> 'text'
SELECT @testTable2 = @testTable2 + ',
@' + column_name + ' ' + data_type
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
AND (
character_maximum_length = NULL
OR data_type = 'text'
)
SELECT @testTable3 = @testTable3 + ',
@' + column_name
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
SELECT @testTable_UPDATE = @testTable_UPDATE + ',
' + column_name + ' =@' + column_name
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 0)
DECLARE @testTable_UPDATE_IDENTITY NVARCHAR(MAX)
SELECT @testTable_UPDATE_IDENTITY = '
' + column_name + ' =@' + column_name
FROM information_schema.columns
WHERE table_name = @table
AND (COLUMNPROPERTY(OBJECT_ID(@table), column_name, 'isidentity') = 1)
SET @testTable = SUBSTRING(@testTable, 2, len(@testTable))
SET @testTable2 = SUBSTRING(@testTable2, 4, len(@testTable2))
SET @testTable3 = SUBSTRING(@testTable3, 2, len(@testTable3))
SET @testTable_UPDATE = SUBSTRING(@testTable, 2, len(@testTable_UPDATE))
SET @opration = ' insert into [' + @table + ']
(
' + @testTable + '
)
values
(
' + @testTable3 + '
)'
DECLARE @OPERATION_UPDATE NVARCHAR(MAX)
SET @OPERATION_UPDATE = 'UPDATE [' + @table + ']
SET ' + @testTable_UPDATE + ' WHERE [' + @testTable_UPDATE_IDENTITY + ']'
--PRINT @OPERATION_UPDATE
SET @OP = 'InsertNew' + @table
SET @final = '/*
----------------------------------------------------------------------------------------
Store Procedure Name : SP__' + @OP + '
----------------------------------------------------------------------------------------
1- Creation Date :' + convert(VARCHAR, getdate(), 103) + '
2- Last Update :' + convert(VARCHAR, getdate(), 103) + '
3- Parametars No:6
4- Creation By :' + @DeveloperName + '
5- Last Update By :' + @DeveloperName + '
6- Return Value : Dataset
---------------------------------------------------------------------------------------
*/
Create PROCEDURE SP__' + @OP + '
(
' + @testTable2 + '
)
AS
set nocount on
' + @opration + '
---------------------------------------------------------------------------------------
' + @OPERATION_UPDATE + '
Select * from [' + @table + ']'
--exec (@final)
PRINT @final
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--create_procedure 'Products','VISHAL','Products'
Comments
Post a Comment