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'

DYNAMIC SQL INSERT STATEMENT

create table #tmp (
SQLText varchar(8000) )

create table #tmp2 (
Id int identity,
SQLText varchar(8000) )

set nocount on

delete #tmp
delete #tmp2

declare @vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)

declare csrTables cursor for
select name
from sysobjects
where type in ('u')
and name in ('Customers')
order by name


open csrTables
fetch next from csrTables into @vsTableName

while (@@fetch_status = 0)
begin

select @vsSQL = '',
@vsCols = ''
select @vsSQL = @vsSQL +
CASE when sc.type in (39,47,61,111) then
'''''''''+' + 'isnull(rtrim(replace('+ sc.name + ','''''''','''''''''''')),'''')' + '+'''''',''+'
when sc.type = 35 then
'''''''''+' + 'isnull(rtrim(replace(substring('+ sc.name + ',1,1000),'''''''','''''''''''')),'''')' + '+'''''',''+'
else
'isnull(convert(varchar,' + sc.name + '),''null'')+'',''+'
end
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID

select @vsCols = @vsCols + sc.name + ','
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID

select @vsSQL = substring(@vsSQL,1,datalength(@vsSQL)-1)

select @vsCols = substring(@vsCols,1,datalength(@vsCols)-1)

insert #tmp
exec ('select ' + @vsSQL + ' from ' + @vsTableName)

update #tmp
set sqltext = 'insert ' + @vsTableName + '(' + @vsCols + ') values(' + substring(sqltext,1,datalength(sqltext)-1) + ')'

insert #tmp2
select 'DELETE from ' + @vsTableName

insert #tmp2 values ('GO')

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' on'
end

insert #tmp2
select * from #tmp

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' off'
end

insert #tmp2 values ('GO')

insert #tmp2
select 'update statistics ' + @vsTableName

insert #tmp2 values ('GO')

delete #tmp

fetch next from csrTables into @vsTableName

end

close csrTables
deallocate csrTables

update #tmp2
set sqltext = substring(sqltext,1,charindex(',)',sqltext)-1) + ',NULL)'
where not(charindex(',)',sqltext) = 0)

update #tmp2
set sqltext = replace(sqltext, ',''''',',null')
where not (charindex(',''''',sqltext) = 0)

update #tmp2
set sqltext = replace(sqltext, '(''''',',null')
where not (charindex('(''''',sqltext) = 0)

set nocount off

select sqltext from #tmp2 order by id

go

drop table #tmp
drop table #tmp2

Sunday, 12 May 2013

datatable group by



private DataTable GetGroupedBy(DataTable dt, string columnNamesInDt, string groupByColumnNames, string typeOfCalculation)

{

//Return its own if the column names are empty

if (columnNamesInDt == string.Empty || groupByColumnNames == string.Empty)

{

return dt;

}




//Once the columns are added find the distinct rows and group it bu the numbet

DataTable _dt = dt.DefaultView.ToTable(true, groupByColumnNames);




//The column names in data table

string[] _columnNamesInDt = columnNamesInDt.Split(',');




for (int i = 0; i < _columnNamesInDt.Length; i = i + 1)

{

if (_columnNamesInDt[i] != groupByColumnNames)

{

_dt.Columns.Add(_columnNamesInDt[i]);

}

}







//Gets the collection and send it back

for (int i = 0; i < _dt.Rows.Count; i = i + 1)

{

for (int j = 0; j < _columnNamesInDt.Length; j = j + 1)

{

if (_columnNamesInDt[j] != groupByColumnNames)

{

_dt.Rows[i][j] = dt.Compute(typeOfCalculation + "(" + _columnNamesInDt[j] + ")", groupByColumnNames + " = '" + _dt.Rows[i][groupByColumnNames].ToString() + "'");

}

}

}




return _dt;

}



-----------------------------------



how to use

//The way to call the Group By Function //Argument 1 = Data table in IMAGE 1 //Argument 2 = The fields you want for the data table returned //Argument 3 = The field you want to group by //Argument 4 = The function you want to do It can be either SUM, COUNT, AVG etc. DataTable dtGroupedBy = GetGroupedBy(dt, "CodeName,Quantity,Current", "CodeName", "Sum");