Equivalent to VB AndAlso in SQL?

Is there an equivalent to VB's AndAlso/OrElse and C#'s &&/|| in SQL (SQL Server 2005). I am running a select query similar to the following:

SELECT a,b,c,d FROM table1 WHERE (@a IS NULL OR a = @a) AND (@b IS NULL OR b = @b) AND (@c IS NULL OR c = @c) AND (@d IS NULL OR d = @d)

For example, if the "@a" parameter passed in as NULL there is no point in evaluating the 2nd part of the WHERE clause (a = @a). Is there a way to avoid this either by using special syntax or rewriting the query?

Thanks, James.

-------------Problems Reply------------

The only way to guarantee the order of evaluation is to use CASE

WHERE
CASE
WHEN @a IS NULL THEN 1
WHEN a = @a THEN 0
ELSE 0
END = 1
AND /*repeat*/

In my experience this is usually slower then just letting the DB engine sort it out.

TerrorAustralis's answer is usually the best option for non-nullable columns

The query engine will take care of this for you. Your query, as written, is fine. All operators will "short circuit" if they can.

Try this:

AND a = ISNULL(@a,a)

This function looks at @a. If it is not null it equates the expression

AND a = @a

If it is null it equates the expression

AND a = a

(Since this is always true, it replaces the @b is null statement)

Another way is to do:

IF (@a > 0) IF (@a = 5)
BEGIN

END

Another if after the condition will do an "AndAlso" logic.

I want to emphesise that this is just a short way to write:

IF (@a > 0)
IF (@a = 5)
BEGIN

END

Take this example:

SELECT * FROM Orders
WHERE orderId LIKE '%[0-9]%'
AND dbo.JobIsPending(OrderId) = 1

Orders.OrderId is varchar(25)

dbo.JobIsPending(OrderId) UDF with int parameter

No short circuit is made as the conversion fails in dbo.JobIsPending(OrderId) when Orders.OrderId NOT LIKE '%[0-9]%'

tested on SQL Server 2008 R2

Category:sql Views:0 Time:2009-10-30

Related post

  • Is there an equivalent to SHA1() in MS-SQL? 2008-10-08

    Converting a couple stored procedures from MySQL to Microsoft SQL server. Everything is going well, except one procedure used the MySQL SHA1() function. I cannot seem to find an equivalent to this in MS-SQL. Does anyone know a valid equivalent for SH

  • What is the equivalent datatype for datetime of SQL Server 2008 in ADO? 2011-05-25

    The datetime datatype in SQL Server 2008 supports milliseconds. I am trying to execute a stored procedure that accepts a datetime parameter, with an arg with milliseconds, as input/output value. I am unable to convert the string that I pass in to the

  • C# LINQ equivalent of a somewhat complex SQL query 2008-12-31

    So I have a SQL Query as Follows SELECT P.Date, P.CategoryName, P.ProductName, SUM(Quantity) Quantity, SUM(Sales) TotalSales, IsLevelThree FROM Products P LEFT JOIN LevelThreeTracking LTT ON P.Date = LTT.Date AND P.CategoryName = P.CategoryName AND P

  • LINQ to SQL Equivalent of ISDATE() in T-SQL and casting? 2009-03-03

    Does anyone know the equivalent of ISDATE() in LINQ to SQL query syntax? I've got a varchar field in SQL that contains dates and I need to filter out the non-date rows. was hoping for something like this: var query = SomeDataContext; query = from p i

  • What are equivalent C# data types for SQL Server's date, time and datetimeoffset? 2009-03-17

    What are the most suitable equivalent C# data types for the date datatypes in SQL Server? I'm specifically looking for date time datetimeoffset --------------Solutions------------- Here are the equivalent CLR data types for date, time and datetimeoff

  • What is the equivalent for LOCK_ESCALATION = TABLE in SQL Server 2005? 2009-05-08

    I have a script that was generated in SQL Server 2008, but I need to execute it against a SQL Server 2005 database. What would an equivalent statement for the following be in SQL Server 2005? ALTER TABLE dbo.Event SET (LOCK_ESCALATION = TABLE) ------

  • Django DB API equivalent of a somewhat complex SQL query 2009-09-03

    I'm new to Django and still having some problems about simple queries. Let's assume that I'm writting an email application. This is the Mail model: class Mail(models.Model): to = models.ForeignKey(User, related_name = "to") sender = models.ForeignKey

  • Does SQL Server have an equivalent to Oracle's PL/SQL Package? 2010-02-14

    I was wondering if SQL Server has an equivalent to Oracle PL/SQL Package? It is really nice to build your sprocs, functions, etc into a Package. --------------Solutions------------- Don't know if is a pure T-SQL equivalent, but you can Create all you

  • Equivalent of Debug.Assert for SQL Server 2010-07-08

    I'm adapting a large number of SQL Server 2005 scripts to merge two of our databases together. The scripts are run from a .cmd file which calls sqlcmd to run the scripts in order. However, I'm experiencing one or two issues where the scripts are fail

  • equivalent of rowlock xlock holdlock (sql server) for postgres 2010-09-25

    in sql server begin tran select * from foos with (rowlock, xlock, holdlock) where id =7 ... commit tran will lock the row for reading and writing and it will hold the lock until the end of the transaction, is there an equivalent of this in postgresql

  • What's equivalent to TRUNCATE TABLE in SQL Server? 2011-10-19

    I'm looking for something equivalent to TRUNCATE TABLE How I do this using SQL Server 2005? Thanks in advance! --------------Solutions------------- t-sql for sql server 2005: TRUNCATE TABLE schema.tablename; GO link: http://msdn.microsoft.com/en-us/l

  • Is 'LEFT OUTER JOIN' equivalent to 'JOIN' in Microsoft SQL 2012-02-09

    On this MSDN page, it shows that these are equivelant; LEFT OUTER JOIN or LEFT JOIN My question, in MSSQL is JOIN also equivalent to LEFT JOIN --------------Solutions------------- No. JOIN is equivalent to INNER JOIN. Check THIS example. Since it ret

  • Equivalent in MySQL to MS SQL Jobs? 2012-04-04

    Is there a MySQL equivalent to the MS SQL Server jobs and job scheduling? Or is that kind of thing considered an application-layer thing in the MySQL world? --------------Solutions------------- Are you looking for the Event Scheduler? When you create

  • An equivalent to If contains using SQL? 2012-04-28

    I am creating a program that needs to check if a value exists in column 1 of my database, if not then add it or if it does then add 1 to the value in column 2. Currently the program only adds each value to the database and I don't know how to do the

  • What is the C# equivalent of the Oracle PL/SQL COALESCE function? 2008-09-03

    Is there a one statement or one line way to accomplish something like this, where the string s is declared AND assigned the first non-null value in the expression? //pseudo-codeish string s = Coalesce(string1, string2, string3); or, more generally, o

  • Linq to SQL equivalent of SUM GROUP BY SQL statement 2010-02-10

    I'm having a hard time figuring out how to translate this simple SQL statement to (c#) linq to SQL : SELECT table1.vat, SUM(table1.QTY * table2.FLG01 + table1.QTY * table2.FLG04) FROM table1 inner join table2 on table2.key= table1.key where '2010-02-

  • Is there an equivalent to typedef in Transact-SQL? 2010-02-12

    Is there any way of aliasing datatypes in T-SQL (SQL Server 2005), a bit like a C typedef? I'd like to be able to alias, say, VARCHAR(20) as 'my_datatype' so that wherever I wish to use VARCHAR(20) I could instead use my_datatype. Synonyms allow you

  • Is there an equivalent method to sp_generateinserts for SQL Server 2008? 2010-07-02

    In SQL Server 2005, there was a stored procedure available called sp_generateinserts. However in SQL Server 2008, it doesn't seem to exist. Does anyone know if it is still around? Thanks. --------------Solutions------------- That wasn't a built in sy

  • Is there an equivalent to 'mysqladmin processlist' for SQL Server? 2010-07-09

    I've been trying to formulate a query to help myself identify resource-heavy queries/database/users using SQL Server and haven't gotten it down quite yet. I want to build a query that will do what 'mysqladmin processlist' would do for MySQL. I've ref

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.118 (s). 11 q(s)