Help with Stored Procedure - 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 2005 Forums

.NET Inside SQL Server (2005)

Help with Stored Procedure

Author

Topic

Andy19722222
Starting Member

6 Posts

Posted - 2009-05-19 : 08:16:12

Hi All

I have two tables:
Table Rate
ID
Code
Rate

Table Estimate
ID
EstimateID
RateID
Rate

I am trying to create an SP that returns all Rates from Table Rate but if the ID exists in the Estimate table (ref. RateID) then return the Rate from the Estimate table instead.

Any help would be greatly appreciated. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 08:29:04

[code]
select r.ID, Rate = coalesce(e.Rate, r.Rate)
from Rate r
left join Estimate e on r.ID = e.RateID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Andy19722222
Starting Member

6 Posts

Posted - 2009-05-19 : 09:02:52

Thanks KH
How does the Coalesce work, does it return the first if it exists and if not the second?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 09:07:29

quote:
COALESCE (Transact-SQL)

Returns the first nonnull expression among its arguments.

see http://msdn.microsoft.com/en-us/library/ms191472(SQL.90).aspx

KH
[spoiler]Time is always against us[/spoiler]

Andy19722222
Starting Member

6 Posts

Posted - 2009-05-19 : 09:17:35

Thats great Thanks.
Would it work though if I had a Quantity column that only existed in the Estimate table like:

select r.ID, Rate = coalesce(e.Rate, r.Rate), Quantity = coalesce(e.Quantity, Null)
from Rate r
left join Estimate e on r.ID = e.RateID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 09:29:28

it will work, but the coalesce for Quantity will be redundant. You can remove that


select r.ID, Rate = coalesce(e.Rate, r.Rate), e.Quantity = coalesce(e.Quantity, Null)
from Rate r
left join Estimate e on r.ID = e.RateID

unless you want to return 0 if Quantity is null


Quantity = coalesce(e.Quantity, 0)

KH
[spoiler]Time is always against us[/spoiler]

Andy19722222
Starting Member

6 Posts

Posted - 2009-05-19 : 09:34:23

Thats great KH, saved me loads of time....nice one!

Andy19722222
Starting Member

6 Posts

Posted - 2009-05-19 : 11:31:58

Hi
Unfortunately I have run into another problem. My SP now looks like this:
CREATE PROCEDURE PT_Select_C3CivilsCosts_ByEstimateID
@C3_EstimateID int
AS
SELECT r.ID, r.Code, Rate = coalesce(e.Rate, r.Rate1), e.Quantity, e.Uplift, e.Total
FROM PT_C3CivilsRates r
LEFT JOIN PT_C3CivilsCosts e ON r.ID = e.CodeID
WHERE e.C3_EstimateID = @C3_EstimateID

What I wanted to happen is all rows from PT_C3CivilsRates r are returned and if a row exists in PT_C3CivilsCosts e then take the Rate, Quantity etc from the PT_C3CivilsCosts e table. The above only returns rows where an entry exists in PT_C3CivilsCosts e

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 11:36:07

in future try to post new questions in new thread as it will improve visibility by others.
i think what you want is this


CREATE PROCEDURE PT_Select_C3CivilsCosts_ByEstimateID
@C3_EstimateID	int
AS
SELECT r.ID, r.Code, Rate = coalesce(e.Rate, r.Rate1), e.Quantity, e.Uplift, e.Total
FROM PT_C3CivilsRates r
LEFT JOIN PT_C3CivilsCosts e ON r.ID = e.CodeID
AND e.C3_EstimateID = @C3_EstimateID

Andy19722222
Starting Member

6 Posts

Posted - 2009-05-19 : 11:58:50

Sorry Visa, had thought it was still part of the original question.
Anyway, thanks a lot this now works. Nice One!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 13:24:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources