Skip to main content

Posts

Showing posts from 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 =...

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 '''''''''+...

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] + ")", groupByColumnName...

Building a Database Driven Hierarchical Menu using ASP.NET

Download AspSooperFish