April 6, 2005 at 7:36 am
Hello,
I'm ok with writing sql and exicuting this on its own.
However I would like to move this on and put several statements into one stored proc to make it neeter.
Does anyone know any good resorces to help me acheive this?
My code is below; and basically i want to feed in the stored proc 3 values, run an update statement, run a select and store this result for the next if statment. then depending on the value returned by the if statement either run a update or insert statement.
update ratehistory
set periodend = 'new start'-1
where rescode = @ResCode and periodend = NULL
select count(*) as rhexisits from ratehistory where periodstart = @NewDate and rescode = @ResCode > 0
if (rhexisits > 0) --update
update ratehistory set rate =
(select rate from grade where grade = @Grade)
where periodstart = @NewDate and rescode = @ResCode
else --insert
insert into ratehistory(rescode,periodstart,rate)
select @ResCode,@NewDate,rate from grade where grade = @Grade
end if
any help would be much appriciated
April 6, 2005 at 7:53 am
Drop all that in a procedure declaration as such:
CREATE PROCEDURE usp_MyProcedure
(
@sParam1 varchar(100),
@sParam2 varchar(100),
@sParam3 varchar(100)
)
AS
... [INSERT PROCEDURE CODE HERE] ...
As far as storing the value from your SELECT COUNT(*) goes you can do this:
--declare variable
DECLARE @lMyCount int
--store the value to the @lMyCount variable
SELECT @lMyCount = COUNT(*) FROM tblTable WHERE ...
--use the value
IF(@lMyCount > 0)
Books Online (BOL) has a lot of the best reference material (sqlservercentral obviously is another good place to look). A good place to start reading would be CREATE PROCEDURE in the index of BOL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply