T-SQL Dynamic WHERE and ORDER BY

Answer to this thread: http://forums.asp.net/t/1298204.aspx

Declare @FilterBy nvarchar(50)

Declare @Filter nvarchar(50)

Declare @SortBy nvarchar(50)

Declare @Order nvarchar(4)

 

SET

@FilterBy=‘Country’

SET

@Filter=‘s’

SET

@SortBy=‘Rating’

SET

@Order=‘ASC’

 

SELECT

Category, Country, State, City, DateUploaded, Rating, NumRatings, NumViews, NumComments FROM [Photos]

WHERE

(ISNULL(Category,) = CASE @FilterBy WHEN ‘Category’ THEN @Filter ELSE ISNULL(Category,) END ) AND

(ISNULL(Country,) = CASE @FilterBy WHEN ‘Country’ THEN @Filter ELSE ISNULL(Country,) END ) AND

(ISNULL(State,) = CASE @FilterBy WHEN ‘State’ THEN @Filter ELSE ISNULL(State,) END ) AND

(ISNULL(City,) = CASE @FilterBy WHEN ‘City’ THEN @Filter ELSE ISNULL(City,) END )

ORDER

BY

CASE

@Order

WHEN

‘ASC’ THEN

CASE

@SortBy

WHEN

‘DateUploaded’ THEN DateUploaded

WHEN ‘Rating’ THEN Rating

WHEN ‘NumRatings’ THEN NumRatings

WHEN ‘NumViews’ THEN NumViews

WHEN ‘NumComments’ THEN NumComments END

END

ASC,

CASE

@Order

WHEN

‘DESC’ THEN

CASE

@SortBy

WHEN

‘DateUploaded’ THEN DateUploaded

WHEN ‘Rating’ THEN Rating

WHEN ‘NumRatings’ THEN NumRatings

WHEN ‘NumViews’ THEN NumViews

WHEN ‘NumComments’ THEN NumComments END

END

DESC

 

–table script:

CREATE

TABLE [dbo].[Photos](

[Category] [nvarchar]

(255) NULL,

[Country] [nvarchar]

(255) NULL,

[State] [nvarchar]

(255) NULL,

[City] [nvarchar]

(255) NULL,

[DateUploaded] [nvarchar]

(255) NULL,

[Rating] [nvarchar]

(255) NULL,

[NumRatings] [nvarchar]

(255) NULL,

[NumViews] [nvarchar]

(255) NULL,

[NumComments] [nvarchar]

(255) NULL,

[id] [int] Not

NULL

)


Generate date range T-SQL

I came across this script from one blog to replace a calendar table:

;with mycte as

(

select cast(‘2001-01-01’ as datetime) DateValue

union all

select DateValue + 1 from mycte

where DateValue + 1 < ‘2010-3-31’

)

select

* from mycte

http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx

I used this script to answer a question here:

http://forums.asp.net/t/1297396.aspx

;with mycte as

(

select cast(‘2000-01-01’ as datetime) DateValue

union all

select DateValue + 1 from mycte

where DateValue + 1 < ‘2010-12-31’

)

SELECT

DatesRange.Name, mycte.dateValue

FROM

mycte CROSS JOIN

DatesRange

WHERE mycte.dateValue BETWEEN DatesRange.FromDT AND DatesRange.ToDT

OPTION (MAXRECURSION 0)

 


Video clips for VS

 
I have taken a short break with some very useful clips from this link.
http://www.internet.com/video/visualstudio_archive.php#archive

Dynamically textbox and save data to database with a SqlDataSource

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="dynamicTextBoxVB.aspx.vb" Inherits="dynamicTextBoxVB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
         <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
            SelectCommand="SELECT [ID], [Name] FROM [NameForTextBox]" DataSourceMode="DataReader"></asp:SqlDataSource>
  
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
            DeleteCommand="DELETE FROM [NameForTextBoxForInsert] WHERE [ID] = @ID" InsertCommand="INSERT INTO [NameForTextBoxForInsert] ([fromLabel], [fromTextBox]) VALUES (@fromLabel, @fromTextBox)"
            SelectCommand="SELECT [ID], [fromLabel], [fromTextBox] FROM [NameForTextBoxForInsert]"
            UpdateCommand="UPDATE [NameForTextBoxForInsert] SET [fromLabel] = @fromLabel, [fromTextBox] = @fromTextBox WHERE [ID] = @ID">
            <DeleteParameters>
                <asp:Parameter Name="ID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="fromLabel" Type="String" />
                <asp:Parameter Name="fromTextBox" Type="String" />
                <asp:Parameter Name="ID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="fromLabel" Type="String" />
                <asp:Parameter Name="fromTextBox" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
            DataSourceID="SqlDataSource2">
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
                    SortExpression="ID" />
                <asp:BoundField DataField="fromLabel" HeaderText="fromLabel" SortExpression="fromLabel" />
                <asp:BoundField DataField="fromTextBox" HeaderText="fromTextBox" SortExpression="fromTextBox" />
            </Columns>
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Insert data from Textbox" />
    
    </div>
    </form>
</body>
</html>
 
Imports System.Data.SqlClient
Imports System.Data
Imports System

Partial Class dynamicTextBoxVB
    Inherits System.Web.UI.Page
    Protected Overloads Overrides Sub OnInit(ByVal e As EventArgs)
        'generate all textbox 

        myfunctionAddTextBox()
        MyBase.OnInit(e)

    End Sub

    Protected Sub myfunctionAddTextBox()
        Dim ctrlTable1 As New Table()
        ctrlTable1.CellPadding = 0
        ctrlTable1.CellSpacing = 1
        ctrlTable1.Attributes.Add("Border", "1")


        Dim reader As System.Data.SqlClient.SqlDataReader = DirectCast(SqlDataSource1.[Select](DataSourceSelectArguments.Empty), System.Data.SqlClient.SqlDataReader)


        ' start 

        While reader.Read()
            Dim tr As New TableRow()


            Dim tc1 As New TableCell()
            Dim lbl1 As New Label()

            lbl1.Attributes.Add("runat", "Server")
            lbl1.EnableViewState = True
            lbl1.Width = 20
            lbl1.ID = "lbl" + reader(0).ToString()

            lbl1.Visible = True
            lbl1.Text = reader(1).ToString()
            '0:ID; 1:Name; 
            tc1.Controls.Add(lbl1)
            tr.Cells.Add(tc1)


            Dim tc2 As New TableCell()
            Dim tb2 As New TextBox()

            tb2.Attributes.Add("runat", "Server")
            ' tb2.EnableViewState = true; 
            tb2.Width = 80
            tb2.ID = "txt" + reader(0).ToString()
            'tb2.ReadOnly = true; 
            tb2.Visible = True


            tc2.Controls.Add(tb2)
            tr.Cells.Add(tc2)


            ctrlTable1.Rows.Add(tr)
        End While


        Me.Form.Controls.Add(ctrlTable1)
    End Sub
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim reader As SqlDataReader = DirectCast(SqlDataSource1.[Select](DataSourceSelectArguments.Empty), SqlDataReader)

        While reader.Read()
            Dim strLabel As String = ""
            Dim strTextBox As String = ""

            Dim myLabel As New Label()
            Dim myLabelId As String = "lbl" + reader(0).ToString()

            Dim txtTextBox As New TextBox()
            Dim myTxtId As String = "txt" + reader(0).ToString()





            txtTextBox = DirectCast(Me.FindControl(myTxtId), TextBox)
            If txtTextBox IsNot Nothing Then

                strTextBox = txtTextBox.Text.ToString()
            Else
                strTextBox = ""
            End If


            myLabel = DirectCast(Me.FindControl(myLabelId), Label)
            If myLabel IsNot Nothing Then

                strLabel = myLabel.Text.ToString()
            Else
                strLabel = ""
            End If



            If strLabel <> "" Then


                SqlDataSource2.InsertParameters("fromLabel").DefaultValue = strLabel
                SqlDataSource2.InsertParameters("fromTextBox").DefaultValue = strTextBox
                'SqlDataSource2.InsertParameters["createdBy"].DefaultValue = User.Identity.Name; 
                'SqlDataSource2.InsertParameters["createdDate"].DefaultValue = DateTime.Now.ToString(); 



                'add data to table2 
                SqlDataSource2.Insert()



                txtTextBox.Text = ""

            End If
        End While
        ' Close the reader 
        reader.Close()


    End Sub

End Class
 
 
 
C#:

public partial class dynamicTextBox : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected override void OnInit(EventArgs e)

{

//generate all textbox

myfunctionAddTextBox();

base.OnInit(e);

}

protected void myfunctionAddTextBox()

{

Table ctrlTable1 = new Table();

ctrlTable1.CellPadding = 0;

ctrlTable1.CellSpacing = 1;

ctrlTable1.Attributes.Add(

"Border", "1");

 

SqlDataReader reader = (SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);

// start

while(reader.Read())

{

TableRow tr = new TableRow();

 

TableCell tc1 = new TableCell();

Label lbl1 = new Label();

lbl1.Attributes.Add(

"runat", "Server");

lbl1.EnableViewState =

true;

lbl1.Width = 20;

lbl1.ID = "lbl" + reader[0].ToString();

lbl1.Visible =

true;

lbl1.Text = reader[1].ToString();

//0:ID; 1:Name;

tc1.Controls.Add(lbl1);

tr.Cells.Add(tc1);

 

TableCell tc2 = new TableCell();

TextBox tb2 = new TextBox();

tb2.Attributes.Add(

"runat", "Server");

// tb2.EnableViewState = true;

tb2.Width = 80;

tb2.ID =

"txt" + reader[0].ToString();

//tb2.ReadOnly = true;

tb2.Visible = true;

tc2.Controls.Add(tb2);

tr.Cells.Add(tc2);

ctrlTable1.Rows.Add(tr);

}

reader.Close();

this.Form.Controls.Add(ctrlTable1);

}

protected void Button1_Click(object sender, EventArgs e)

{

SqlDataReader reader = (SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);

 

 

while (reader.Read())

{

String strLabel = "";

String strTextBox = "";

Label myLabel = new Label();

String myLabelId = "lbl" + reader[0].ToString();

TextBox txtTextBox = new TextBox();

String myTxtId = "txt" + reader[0].ToString();

 

 

 

 

txtTextBox = (TextBox)this.FindControl(myTxtId);

if (txtTextBox != null)

{

strTextBox = txtTextBox.Text.ToString();

}

else

{

strTextBox =

"";

}

 

myLabel = (

Label)this.FindControl(myLabelId);

if (myLabel != null)

{

strLabel = myLabel.Text.ToString();

}

else

{

strLabel =

"";

}

 

 

 

 

if (strLabel != "")

{

 

SqlDataSource2.InsertParameters[

"fromLabel"].DefaultValue = strLabel;

SqlDataSource2.InsertParameters["fromTextBox"].DefaultValue = strTextBox;

//SqlDataSource2.InsertParameters["createdBy"].DefaultValue = User.Identity.Name;

//SqlDataSource2.InsertParameters["createdDate"].DefaultValue = DateTime.Now.ToString();

 

 

//add data to table2

SqlDataSource2.Insert();

txtTextBox.Text =

String.Empty;

 

}

}

// Close the reader

reader.Close();

 

}

 

}

 

 
 
 
Tables:

CREATE

TABLE [dbo].[NameForTextBoxForInsert](

[ID] [int]

IDENTITY(1,1) NOT NULL,

[fromLabel] [nvarchar](50) NULL,

[fromTextBox] [nvarchar]

(50) NULL)

 

CREATE TABLE [dbo].[NameForTextBox](

[ID] [int] NULL,[Name] [nvarchar](50) NULL

)

 


Dynamically generate a table with UNPIVOT operation

In SQL Server 2005, you can use UNPIVOT to convert your table to a normalized form(table) like:

Table:

CREATE TABLE [dbo].[PW_Table](

[DateInt] [int]

NULL,

[Meter_1] [float]

NULL,

[Meter_2] [float] NULL,

[Meter_3] [float]

NULL,

[Meter_4] [float]

NULL,

[Meter_5] [float] NULL,

[Meter_6] [float]

NULL,

[Meter_7] [float]

NULL,

[myDate] [datetime] NULL

)

ON [PRIMARY]

–Query with UNPIVOT

SELECT   [dateInt], MeterID, ReadingValue FROM 
(SELECT   [dateInt], [Meter_1], [Meter_2], [Meter_3], [Meter_4], [Meter_5], [Meter_6], [Meter_7]
FROM         PW_Table) t1 
UNPIVOT 
(ReadingValue For MeterID
IN ([Meter_1], [Meter_2], [Meter_3], [Meter_4], [Meter_5], [Meter_6], [Meter_7])) as unPvt 
You want this script to be more flexiable to include unknown number of columns. We can use dynamic query to do this:
 
SET
NOCOUNT ON
DECLARE
@T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)

INSERT
INTO @T SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='PW_Table' and Column_Name Like 'Meter%'

DECLARE
@T1 AS TABLE(num int NOT NULL PRIMARY KEY)

DECLARE @i AS int

SET @i=1

 

WHILE @i <20

BEGIN

INSERT INTO @T1 SELECT @i

SET @i=@i+1

END


DECLARE @cols AS nvarchar(MAX), @cols2 AS nvarchar(MAX),@y AS nvarchar(20)

SET @y = (SELECT MIN(y) FROM @T)

SET @cols = N''
SET @cols2 = N''

WHILE @y IS NOT NULL

BEGIN

SET @cols = @cols + N',['+CAST(@y AS nvarchar(20))+N']' 


SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)

END

SET @cols = SUBSTRING(@cols, 2, LEN(@cols))
 

DECLARE @sql AS nvarchar(MAX)

SET  @sql = N'SELECT dateInt, MeterID, ReadingValue FROM (SELECT dateInt, ' +@cols + N' FROM   PW_Table) as t
UNPIVOT 
(ReadingValue For MeterID
IN (' + @cols + N')) AS unPvt'

EXEC sp_executesql @sql 


Truncate tables with foreign key between them

How to truncate tables with foreign key linked with each other?

You can drop the Foreign Key before you do theTRUNCATE and add it back to the table after you are done. By the way, the TRUNCATE action is logged but it is just not the same way as the DELETE action. You can roll back a truncate in a transaction with the page logging.

Here is a simple sample for the question:

–Drop the foreign key ALTER TABLE dbo.CategoryTable1Sub

DROP CONSTRAINT FK_CategoryID

GO

truncate

table dbo.CategoryTable1

truncate table dbo.CategoryTable1Sub

GO

–Add Foreign key back

ALTER

TABLE dbo.CategoryTable1Sub ADD CONSTRAINT

FK_CategoryID

FOREIGN KEY

(

CatID )REFERENCES dbo.CategoryTable1

( Category_ID )

GO


Dynamically change ORDER BY column and direction

Declare @Ordered varchar(20)

Declare

@AscDesc varchar(5)

--Declare @mystring varchar(25)

SET

@Ordered ='num1'

SET

@AscDesc='ASC'

;

WITH Publication AS

(

SELECT col1, col2, col3, num1, ROW_NUMBER() OVER(

ORDER

BY

CASE

@AscDesc

WHEN

'ASC' THEN

CASE

@Ordered

WHEN

'col1' THEN col1

WHEN 'col2' THEN col2

WHEN 'col3' THEN col3

WHEN 'num1' THEN CONVERT(VARCHAR(50),num1)

WHEN

'Date' THEN CONVERT(varchar,PublicationDate,112)

END

END

ASC,

CASE

@AscDesc

WHEN

'DESC' THEN

CASE

@Ordered

WHEN 'col1' THEN col1

WHEN 'col2' THEN col2

WHEN 'col3' THEN col3

WHEN 'num1' THEN CONVERT(VARCHAR(50),num1)

WHEN

'Date' THEN CONVERT(varchar,PublicationDate,112)

END

END

DESC

)

AS RowNum, id

FROM

[aTable]

)

select

* from Publication