Avoid dynamic sql - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Avoid dynamic sql

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-09-16 : 13:00:09

Hi I have been using the COALESCE statement to remove the need for dynamic sql in dynamic WHERE statements:

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City)

Where @Cus_Name is null if no value is provided(http://www.sqlteam.com/item.asp?ItemID=2077)

I have some situations where I need a left outer join to a table. Due to the left outer join I don't see a way to build a similar statement not using dynamic sql. Having a where statement include a column from a left join, cancels out the left and treats it as equal. Does anyone see a way around this or am I stuck with using dynamic sql? For example, this fails:

SELECT a.Cus_Name,
a.Cus_City,
a.Cus_Country,
b.Car_Year
FROM
Customers a LEFT OUTER JOIN CustomerCars b
WHERE a.Cus_Name = COALESCE(@Cus_Name,a.Cus_Name) AND
a.Cus_City = COALESCE(@Cus_City,a.Cus_City) AND
b.Car_Year = COALESCE(@Car_Year,b.Car_Year)

Having Car_Year in the where clause forces an inner join. Any ideas?

Thanks

Nic

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-16 : 21:51:15

Why would you want to do this with an outer joined table? If the value entered was null you will get a cross join.
You will need to add it inside the join clause and you need something else to join too.

SELECT a.Cus_Name,
a.Cus_City,
a.Cus_Country,
b.Car_Year
FROM
Customers a
LEFT OUTER JOIN CustomerCars b
on b.Car_Year = COALESCE(@Car_Year,b.Car_Year)
WHERE a.Cus_Name = COALESCE(@Cus_Name,a.Cus_Name) AND
a.Cus_City = COALESCE(@Cus_City,a.Cus_City)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources