Friday, 17 May 2013

SQL NEW INSERT STORE PROCEDURE DYNAMICALLY

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'

No comments:

Post a Comment

Note: only a member of this blog may post a comment.