October 16, 2024 at 4:13 pm
I have a query that joins a base table to a sentTable that tracks records sent from the base table.
When a record is sent to the user the id is written to the sentTable so that when the user call my proc only the non sent record will then be sent. I am using a left join from my base table to the sentTable (base_rowid = sent_rowid) and my where clause is sent_rowid is null.
This is if the user send no parameters. However, if the users send say a vendor parameter I want all records for that vendor that have been sent or not.
so my current where clause is where sent_rowid is null how do I query if the vendor parameter is not null or blank and then ignore the where sent_rowID is null and bring back any record for the vendor.
October 16, 2024 at 4:30 pm
WHERE sent_rowid is null or vendor = @vendor_param
?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 16, 2024 at 5:12 pm
An IF...ELSE is a Control-of-Flow element. It cannot be used inside of atomic statements, because that would make them non-atomic. I believe that WHERE clauses can only appear in atomic statements, so IF...ELSE elements CANNOT appear in WHERE clauses.
A CASE expression returns a single value. It can be used anywhere that an expression can be used. A common mistake is to try to return a Boolean expression. T-SQL does not have a Boolean data type, so a CASE expression CANNOT return a Boolean expression. A workaround is to return a bit or Y/N and then check whether the returned value is "TRUE"/"FALSE".
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 16, 2024 at 5:35 pm
I think this will match what you need, if I understand correctly:
SELECT ...
FROM dbo.baseTable bt
LEFT OUTER JOIN dbo.sentTable st ON st.sent_rowid = bt.rowid
WHERE ((@vendor IS NULL AND st.sent_rowid IS NULL) OR
(@vendor IS NOT NULL AND st.vendor LIKE @vendor)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply