Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Sep 8th, 2013  |  syntax: None  |  size: 3.13 KB  |  views: 54  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. SELECT [Theme].[Name], [ThemeType].[Type]
  2. FROM [Theme]
  3. Left Outer Join [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
  4. ORDER BY CASE WHEN [ThemeType].[Type] IS NULL THEN 1 ELSE 0 END, [ThemeType].[Type]
  5.        
  6. SELECT [Theme].[Name], [ThemeType].[Type] FROM [Theme]
  7. Left Outer Join [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
  8. WHERE [Theme].[ThemeTypeId] LIKE '%'
  9. ORDER BY CASE WHEN [ThemeType].[Type] IS NULL THEN 1 ELSE 0 END, [ThemeType].[Type]
  10.        
  11. <asp:SqlDataSource ID="SqlDataSource6" runat="server"
  12.     ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
  13.     SelectCommand="SELECT [Theme].[Name], [ThemeType].[Type] FROM [Theme] Left Outer Join [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType] ORDER BY CASE WHEN [ThemeType].[Type] IS NULL THEN 1 ELSE 0 END, [ThemeType].[Type]">
  14.      <SelectParameters>
  15.             <asp:QueryStringParameter Name="changeParam" Type="String" />
  16. </SelectParameters>
  17.        
  18. SELECT [Theme].[Name], [ThemeType].[Type] FROM [Theme]
  19. Left Outer Join [ThemeType]
  20.              ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
  21. WHERE COALESCE([Theme].[ThemeTypeId], 'null') LIKE '%'
  22. ORDER BY CASE
  23.          WHEN [ThemeType].[Type] IS NULL
  24.          THEN 1
  25.          ELSE 0
  26.          END, [ThemeType].[Type]
  27.        
  28. SELECT
  29.      [Theme].[Name],
  30.      [ThemeType].[Type]
  31. FROM
  32.      [Theme] LEFT OUTER JOIN [ThemeType]
  33.           ON
  34.      [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
  35. WHERE
  36.      [Theme].[ThemeTypeId] = @changeParam
  37.            OR
  38.       (COALESCE([Theme].[ThemeTypeId], N'NULL') = N'NULL' AND @changeParam IS NULL)
  39. ORDER BY
  40.      CASE WHEN [ThemeType].[Type] IS NULL THEN 1 ELSE 0 END,
  41.      [ThemeType].[Type]
  42.        
  43. DECLARE @t TABLE
  44. (
  45.     Theme       nvarchar(100),
  46.     ThemeType   nvarchar(100)
  47. )
  48.  
  49. INSERT INTO
  50.     @t
  51.     (
  52.         Theme,
  53.         ThemeType
  54.     )
  55. --I use the union since I am working on SQL Server 2005
  56. SELECT
  57.     N'1',
  58.     N'1111'
  59. UNION
  60. SELECT
  61.     N'1',
  62.     N'----'
  63. UNION
  64. SELECT
  65.     N'2',
  66.     N'2222'
  67. UNION
  68. SELECT
  69.     null,
  70.     N'2222'
  71.  
  72. DECLARE @s  nvarchar(100)
  73. SET @s = N'1' -- change this line for the criteria
  74. --SET @s = null
  75.  
  76. SELECT
  77.     *
  78. FROM
  79.     @t
  80. WHERE
  81.     Theme = @s
  82.         OR
  83.     (COALESCE(Theme, N'NULL') = N'NULL' AND @s IS NULL)
  84.        
  85. protected void viewThemeTypeAssociationsDropDown_OnSelectedIndexChanged(object sender, EventArgs e)
  86. {
  87.     if (viewThemeTypeAssociationsDropDown.SelectedIndex == 0)
  88.     {
  89.         SqlDataSource6.SelectCommand = "SELECT [Theme].[Name], [ThemeType].[Type] FROM [Theme] Left Outer Join [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType] ORDER BY [Theme].[Name] ASC";
  90.     }
  91.     else if (viewThemeTypeAssociationsDropDown.SelectedIndex == 1)
  92.     {
  93.         SqlDataSource6.SelectCommand = "SELECT [Theme].[Name], [ThemeType].[Type] FROM [Theme], [ThemeType] WHERE [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType] ORDER BY [Theme].[Name] ASC";
  94.     }
  95.     else
  96.     {
  97.         SqlDataSource6.SelectCommand = "SELECT [Theme].[Name], [ThemeType].[Type] FROM [Theme] Left Outer Join [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType] WHERE [Theme].[ThemeTypeId] IS NULL ORDER BY [Theme].[Name] ASC";
  98.     }
  99. }