Need help in update - 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)

Need help in update

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-03-09 : 05:20:35

I have a sp in which i update some records for the particular sourceid which i pass from stored procedure as a input parameter

CREATE Procedure [dbo].[JobNumberCorrection]
@SourceId Int

As
update job set jobcode = jobcode + ' - ' + convert(varchar(15),jobnumber)
where sourceid = @SourceId and jobnumber not in
(
select t.jobnumber
from job t
inner join
(
select jobcode, max_jnumber = max(jobnumber)
from job where sourceid = @SourceId
group by jobcode
) m
on t.jobcode = m.jobcode
and t.jobnumber = m.max_jnumber
)

Now I don't want's to pass sourceid as parameter.
I want to occur update for all sourceid
What Modification i needed ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-09 : 08:07:39

pass in NULL for @SourceID ?
Change to


 from job where sourceid = coalesce(@SourceId, sourceid)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-09 : 08:11:25

Also make NULL as default value for @SourceID parameter.

CREATE Procedure [dbo].[JobNumberCorrection]
@SourceId Int = NULL
As
...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-09 : 08:26:53

I think this will do the same thing, and faster...

CREATE PROCEDURE dbo.JobNumberCorrection
AS

UPDATE		j
SET		j.JobCode = j.JobCode + ' - ' + CAST(x.MaxJobNum AS VARCHAR)
FROM		Job AS j
LEFT JOIN	(
			SELECT		JobCode,
					SourceID,
					MAX(JobNumber) AS MaxJobNum
			FROM		Job
			GROUP BY	JobCode,
					SourceID
		) AS x ON x.JobCode = j.JobCode AND x.SourceID = j.SourceID AND x.MaxJobNum > j.JobNumber

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources