How does -1 work in SQL Statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

How does -1 work in SQL Statement?

Author

Topic

viperbyte
Posting Yak Master

132 Posts

Posted - 2013-06-26 : 09:02:32

Good morning all,

In query designer if I supply negative one when prompted with the following code I get all categories. But when I run the code in regular SQL Server Management stuido in the query designer by removing the categorID parts and hard coding -1 in the where clause I get 0 records.

How is this -1 being translated to mean ALL in the Reporting Tool's Query Designer? Or am I missing some fundemental SQL understanding?

SELECT
Production.ProductCategory.Name AS Category
,Production.ProductSubcategory.Name AS SubCategory
,Production.Product.Name AS Product
,Production.Product.Color
,Production.Product.ListPrice
FROM
Production.Product INNER JOIN Production.ProductSubcategory ON
Production.Product.ProductSubCategoryID=
Production.ProductSubcategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory ON
Production.ProductSubCategory.ProductCategoryID=
Production.ProductCategory.ProductCategoryID
WHERE
ProductCategory.ProductCategoryID = @CategoryID OR @CategoryID = -1
ORDER BY
Category, SubCategory, Product

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-26 : 09:05:27

SQL Server might be getting a NULL, although I don't know which step along the way causes that to happen. You can test that theory by replacing the "@CategoryID = -1" with "@CategoryID IS NULL" and running the query in SSMS.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 09:48:37

I think you've set -1 as value for CategoryID in Reporting Tool's Query Designer prompt which is causing it to bypass the filter due to OR condition and give you full categories data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

viperbyte
Posting Yak Master

132 Posts

Posted - 2013-06-26 : 09:50:50

Still 0 records after the experiment with the following code. I'd really like to know how this -1 is working cuz I like knowing what I'm doing and just because the books says do this and all records will display. As far as I know this behaviour is undocumented. But that can't be right.

SELECT
Production.ProductCategory.Name AS Category
,Production.ProductSubcategory.Name AS SubCategory
,Production.Product.Name AS Product
,Production.Product.Color
,Production.Product.ListPrice
FROM
Production.Product INNER JOIN Production.ProductSubcategory ON
Production.Product.ProductSubCategoryID=
Production.ProductSubcategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory ON
Production.ProductSubCategory.ProductCategoryID=
Production.ProductCategory.ProductCategoryID
WHERE
ProductCategory.ProductCategoryID IS NULL
ORDER BY
Category, SubCategory, Product

viperbyte
Posting Yak Master

132 Posts

Posted - 2013-06-26 : 09:53:25

Visakh6,

yes in the desinger I have this code:

SELECT -1 AS ProductCategoryID, '(All Categories)' AS NAME
FROM Production.ProductCategory

UNION

SELECT ProductCategoryID, Name
FROM Production.ProductCategory
ORDER BY NAME

But I don't see the connection on how -1 means All.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 09:57:51

See this where condition


WHERE 
ProductCategory.ProductCategoryID = @CategoryID OR @CategoryID = -1

when -1 is passed as value the blue part becomes true so it will ignore the other part which is where the filteration happens
so it literally bypasses filter and bring you full result set

see this to understand this method

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

viperbyte
Posting Yak Master

132 Posts

Posted - 2013-06-26 : 10:12:19

Oh, ok,

I tried doing this:
WHERE
ProductCategory.ProductCategoryID = @CategoryID OR TRUE

But that's not legit SQL. So basicaly so if that type of code would be supported then it should work I suppose. I didn't know that Where clauses ultimately ended up be translated as where true or false.

Thanks for the scoop.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 10:18:45

quote:
Originally posted by viperbyte

Oh, ok,

I tried doing this:
WHERE
ProductCategory.ProductCategoryID = @CategoryID OR TRUE

But that's not legit SQL. So basicaly so if that type of code would be supported then it should work I suppose. I didn't know that Where clauses ultimately ended up be translated as where true or false.

Thanks for the scoop.

you're welcome

the above where is not valid as you need a condition on either sides not just a boolean value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources