WHERE clause statement problem

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

Transact-SQL (2008)

WHERE clause statement problem

Author

Topic

Timax
Starting Member

37 Posts

Posted - 2015-05-08 : 19:12:50

Please help me to re write this WHERE clause. Part of my WHERE clause have this statement:
(@STparam IS NULL OR dbo.Orders.[Status] = @STparam) and it's not working the way I need it to. @STparam is reading values from check box (in access) and have ether -1 (on) and 0 (off) values. What I want to get from it is if @STparam = 0 then no criteria set and it @STparam - -1 then dbo.Orders[Status] sets to value 1. Can't get this working for some reason :)

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-05-09 : 01:49:20

I didn't understand the case of @STparam = 0 . You need all the records ?


WHERE
(
	(@STparam = 0)
	OR
	(@STparam = -1 AND dbo.Orders.[Status] = ABS(@STparam))
)

sabinWeb MCP

Kristen
Test

22859 Posts

Posted - 2015-05-09 : 03:54:05

I normally code this type of scenario on the basis that the "Match anything" might be NULL or 0, and anything else should be matched exactly:


WHERE (COALESCE(@STparam, 0) = 0 OR dbo.Orders.[Status] = @STparam)

but if @STparam is a Checkbox it can only ever convey one status value ("Checked" or -1), is there actually only ONE possible value for dbo.Orders.[Status] that you want to select?


WHERE (COALESCE(@STparam, 0) = 0 OR dbo.Orders.[Status] = 1)

would do for that, or if you don't want to allow for the sitation were @STparam is defaulted to NULL then:


WHERE (@STparam = 0 OR dbo.Orders.[Status] = 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-05-10 : 16:09:07

What is the datatype for the parameter?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Timax
Starting Member

37 Posts

Posted - 2015-05-28 : 02:33:57

Thank you all for help. I managed to make this work with your advice and create function that is performing correctly but very slow.
What is it makes this function to work very slow? How can I rewrite Where clause for better performance?

ALTER FUNCTION [dbo].[EstimaSearchJobsU]
(
-- Add the parameters for the function here
@CUSparam AS INT, --Customer number
@CHKparam AS INT, --Option for open and closed jobs
@TXTparam VARCHAR(max), --String to search
@FLDparam VARCHAR(20) --Field to search
)
RETURNS
@t TABLE
(
-- Add the column definitions for the TABLE variable here
[Estimate #] INT,
PartNumber VARCHAR(max),
[Description] VARCHAR(max),
Rev VARCHAR(20),
[Date] VARCHAR(20),
CQty VARCHAR(20),
FQty INT,
Qty INT,
Total_Comp INT,
Bd_Panel INT,
[Turn Around] VARCHAR(20),
Size VARCHAR (50)
)
AS
BEGIN

INSERT @t
SELECT dbo.E_Estimates.[Estimate #] AS [Estim #], dbo.[Part Numbers].PartNumber, dbo.[Part Numbers].[Description], dbo.[Part Numbers].Rev,
CONVERT (varchar(10), CAST(dbo.E_Estimates.Dat AS date), 101) AS Date, CASE WHEN CAST([CompQty] AS VARCHAR(10)) = 0 THEN 'None' ELSE CAST([CompQty] AS VARCHAR(10)) END AS CQty,
dbo.E_Estimates.[Fab Qty] AS FQty, dbo.E_Estimates.Qty AS AQty, dbo.E_Estimates.Total_Comp, dbo.E_Estimates.Bd_Panel,
CASE WHEN CAST([Turn_around] AS VARCHAR(20)) = 0 THEN 'Split Assembly' ELSE CAST([Turn_around] AS VARCHAR(20)) END AS [Turn Around], CASE WHEN CAST([Bd_width] AS VARCHAR(10)) IS NULL
THEN '' ELSE CAST([Bd_width] AS VARCHAR(10)) + ' X ' + CAST([Bd_lenght] AS VARCHAR(10)) + ' ' + CASE WHEN CAST([Bd_unit] AS VARCHAR(10))
= 1 THEN 'inch' ELSE 'mm' END END AS Size
FROM dbo.E_Estimates LEFT OUTER JOIN
dbo.[Part Numbers] ON dbo.E_Estimates.PNID = dbo.[Part Numbers].PNID LEFT OUTER JOIN
dbo.Customers ON dbo.E_Estimates.Customer = dbo.Customers.CustID
WHERE (@CUSparam = 113 OR dbo.E_Estimates.Customer = @CUSparam) AND

((
@CHKparam = 0 AND ((dbo.E_Estimates.[Assy Q]) = -1) AND ((dbo.E_Estimates.E_Status)=1 Or (dbo.E_Estimates.E_Status)=2)

)
OR
(
@CHKparam = -1 AND ((dbo.E_Estimates.[Assy Q]) = -1 Or (dbo.E_Estimates.[Assy Q]) = -2)

))
AND
(
(@FLDparam = '[Part Number]' AND (dbo.[Part Numbers].PartNumber) Like '%' + @TXTparam + '%')
OR (@FLDparam = '[Description]' AND (dbo.[Part Numbers].[Description]) LIKE '%' + @TXTparam + '%')
)

ORDER BY [Estim #]

RETURN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources