CASE Expression in Where Clause of SQL Server Query

When we need to write conditions with case in the where clause

we can either write the first part or second part or both in a case expression.

a usual expression in the where clause should contain two parts

eg: WHERE ID=23

OR WHERE ID=@ID etc..

So here we can use the case expression for the first part or the second part

the following examples will guide you.

DECLARE @IsActive int
SET @IsActive=1
SELECT * FROM Employee
WHERE  IsSponsor = CASE @IsActive
WHEN 1 THEN 1
WHEN 0 THEN 0
ELSE 0
END

in the above CASE  is used in the second part

DECLARE @IsActive int
SET @IsActive=1
SELECT * FROM Employee
WHERE CASE
WHEN UserRole=1 AND IsSponsor=@IsActive THEN 1
WHEN UserRole=2 AND IsTrainee=@IsActive THEN 1
ELSE 0
END =1

in the above codes the first part becaomes either 1 or 0 depending on the conditions and then it is checking with “=1” which you can find after the END

you can Find END=1 there  actually we are trying to make 0=1 or 1==1

then the below is an example for nested case when

DECLARE @IsActive int
SET @IsActive=1
SELECT * FROM Employee
WHERE CASE
WHEN UserRole=1
CASE
WHEN IsSponsor=@IsActive THEN 1
ELSE 0
END
WHEN UserRole=2
CASE
WHEN IsTrainee=@IsActive THEN 1
ELSE 0
END
ELSE 0
END=1

here also

the first part will make  either 0 or 1 depending up on the conditions and then it is matched with =1  which you can find in the last line END =1

Another Examples

SELECT * FROM Orders
    WHERE CustomerID=CASE @Var1 WHEN 'Customers' THEN @Var2 ELSE 0 END
       OR EmployeeID=CASE @Var1 WHEN 'Employees' THEN @Var2 ELSE 0 END

SELECT * FROM Orders
    WHERE 1 =
      CASE @Var1
         WHEN 'Customers' THEN CASE WHEN CustomerID=@Var2 THEN 1 ELSE 0 END
         WHEN 'Employees' THEN CASE WHEN EmployeeID=@Var2 THEN 1 ELSE 0 END
      END
Suggesions and replaies are welcome..
Happy Programming..
Rinosh K  Sasidharan
http://www.rinosh.co.cc
http://www.rinoshsasidharan.wordpress.com
			

			
						

ASP.Net Menu Control Donot working on IE8 and Google Chrome

Hi

When you use the   asp:Menu to generate menu

it will create table and td in the HTML View an alternative to voercome this

we can us css adapters.

http://download.codeplex.com/Project/Download/FileDownload.aspx?ProjectName=cssfriendl

otherwise a simple solution is you have to set the z-index property

of the

<DynamicMenuStyle CssClass="IE8Fix" />  of the menu element
remember the element not the DynamicMenuItemStyle.
and add style in css

.IE8Fix  { z-index: 100; }

and to correct it in the Google Chrome.

you have to add the following code in the page load

if (Request.UserAgent.IndexOf("AppleWebKit") > 0)
 Request.Browser.Adapters.Clear();

Regards
Rinosh.K.Sasidharan
www.rinosh.co.cc
Happy Programming


		
	
			

open a new browser window using javascript

To open a new browser window, use the window.open() method.
the following code opens this page in a new window.
myRef = window.open(''+self.location,'mywin',
'left=20,top=20,width=500,height=500,toolbar=1,resizable=0');
The general syntax of the window.open() method is as follows:
winRef = window.open( URL, name [ , features [, replace ] ] )
The return value, stored in the variable winRef, is the reference to your new window. You can use this reference later, for example, to close this window (winRef.close()), give focus to the window (winRef.focus()) or perform other window manipulations.
The parameters URL, name, features, replace have the following meaning:
URL	 String specifying the location of the Web page to be displayed in the new window. If you do not want to specify the location, pass an empty string as the URL (this may be the case when you are going to write some script-generated content to your new window).
name	 String specifying the name of the new window. This name can be used in the same constructions as the frame name provided in the frame tag within a frameset <FRAME NAME=name ...>. For example, you can use hyperlinks of the form <a target=name href="page.htm">, and the hyperlink destination page will be displayed in your new window.
If a window with this name already exists, then window.open() will display the new content in that existing window, rather than creating a new one.
features	 An optional string parameter specifying the features of the new window. The features string may contain one or more feature=value pairs separated by commas.
replace	 An optional boolean parameter. If true, the new location will replace the current page in the browser's navigation history. Note that some browsers will simply ignore this parameter.
The following features are available in most browsers:
toolbar=0|1	 Specifies whether to display the toolbar in the new window.
location=0|1	 Specifies whether to display the address line in the new window.
directories=0|1	 Specifies whether to display the Netscape directory buttons.
status=0|1	 Specifies whether to display the browser status bar.
menubar=0|1	 Specifies whether to display the browser menu bar.
scrollbars=0|1	 Specifies whether the new window should have scrollbars.
resizable=0|1	 Specifies whether the new window is resizable.
width=pixels	 Specifies the width of the new window.
height=pixels	 Specifies the height of the new window.
top=pixels	 Specifies the Y coordinate of the top left corner of the new window. (Not supported in version 3 browsers.)
left=pixels	 Specifies the X coordinate of the top left corner of the new window. (Not supported in version 3 browsers.)
The return value, stored in the variable winRef, is the reference to your new window. You can use this reference later, for example, to close this window (winRef.close()), give focus to the window (winRef.focus()) or perform other window manipulations.The parameters URL, name, features, replace have the following meaning:URL	 String specifying the location of the Web page to be displayed in the new window. If you do not want to specify the location, pass an empty string as the URL (this may be the case when you are going to write some script-generated content to your new window).name	 String specifying the name of the new window. This name can be used in the same constructions as the frame name provided in the frame tag within a frameset <FRAME NAME=name ...>. For example, you can use hyperlinks of the form <a target=name href="page.htm">, and the hyperlink destination page will be displayed in your new window.If a window with this name already exists, then window.open() will display the new content in that existing window, rather than creating a new one.
features	 An optional string parameter specifying the features of the new window. The features string may contain one or more feature=value pairs separated by commas.replace	 An optional boolean parameter. If true, the new location will replace the current page in the browser's navigation history. Note that some browsers will simply ignore this parameter.The following features are available in most browsers:toolbar=0|1	 Specifies whether to display the toolbar in the new window.location=0|1	 Specifies whether to display the address line in the new window.directories=0|1	 Specifies whether to display the Netscape directory buttons.status=0|1	 Specifies whether to display the browser status bar.menubar=0|1	 Specifies whether to display the browser menu bar.scrollbars=0|1	 Specifies whether the new window should have scrollbars.resizable=0|1	 Specifies whether the new window is resizable.width=pixels	 Specifies the width of the new window.height=pixels	 Specifies the height of the new window.top=pixels	 Specifies the Y coordinate of the top left corner of the new window. (Not supported in version 3 browsers.)left=pixels	 Specifies the X coordinate of the top left corner of the new window. (Not supported in version 3 browsers.)
			

How to open new window (popup) and center on screen using javascript

Here is a java script function that opens a new window (popup) and puts it on center of screen:

<script>
function PopupCenter(pageURL, title,w,h) {
var left = (screen.width/2)-(w/2);
var top = (screen.height/2)-(h/2);
var targetWin = window.open (pageURL, title, 'toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=no, copyhistory=no, width='+w+', height='+h+', top='+top+', left='+left);
}
</script>

The link example would be like this:
<a href="javascript:void(0);" onclick="PopupCenter('http://www.rinoshb4u.wordpress.com', 'myPop1',400,400);">CLICK TO OPEN POPUP</a>