Update and Delete stmt. - 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

General SQL Server Forums

New to SQL Server Programming

Update and Delete stmt.

Author

Topic

sadbjp
INNER JOIN

41 Posts

Posted - 2007-05-11 : 11:04:40

Hi,
I have two tables:

1. RubricReport
2. RubricReportDetail

How can I code this step in my stored procedure:

If @ReportID is NULL, insert a row into RubricReport table, and set @ReportID=@@IDENTITY; otherwise, update table RubricReport for columns LastUpdate and LastUpdateBy, and delete table RubricReportDetail where ReportID=@ReportID.

Table RubricReport has columns ReportID, County,Dsitrict, DataYears, LastUpdate and LastUpdateBy

Table RubricReportDetail has columns ReportID, IndicatorID, LocalPerf

Kindly help me.

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 11:50:32

will this help you?


If @ReportID is NULL
begin
     insert into yourtable ...
     select @ReportId = scope_identity()     
end
else
begin
     update RubricReport 
     set LastUpdate = getdate(),
         LastUpdateBy = 'yourUserNameHere'
     where ReportId = @ReportId

     delete RubricReportDetail where ReportId = @ReportId
end

for future reference:
Don't be impatient,
people aren't paid for hanging around here

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

sadbjp
INNER JOIN

41 Posts

Posted - 2007-05-11 : 11:53:56

Thanks a lot. I apologize for any inconvenience caused.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 11:56:02

there was none.
i was just pointing out what is frowned upon here so
that you know what to expect next time and thus get help faster.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources