is it possible to nest if statements in stored proc?
-
hi,
I'm pretty new to stored procs and at the moment im trying to put one if statement within another. I can get one If statement to work but when i try to put two in i get problems.
Can system bracked be used to seperate the if statement s {} or anything else?
thanks
-
Have a look at BOL for IF...ELSE. There is an example. If you still struggle, please post the code.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes,you can nest if statements in storedprocedure
example
create procedure dbo.exampleSp
(@RefreshFactweekly int,@RefreshFactMonthly int)
WITH RECOMPILE
as
begin
declare @CurrYr int
select @curryr=FiscalyearId from Salesdate where SDate=getdate()
if @RefreshFactweekly = 1
begin
if @RefreshFactMonthly = 1
begin
Insert prodsales
Select d.id from products d (nolock) join salesdate s (nolock)
On d.salesdateid=s.salesdateid where s.fiscalyearid
in (@CurrYr,(@CurrYr-1),(@CurrYr-2)) order by d.salesdateid
end
else
Begin
Insert @prodsales
Select d.id from products d (nolock) join salesdate s (nolock)
On d.salesdateid=s.salesdateid where s.fiscalyearid
in (@CurrYr) order by d.salesdateid
end
end
end --end of proc
HTH
-
Thanks,
Just has a go at creating my proc using begin and end, but nothing is returned by the aspx page. can anyone see what is wrong with it?
CREATE PROCEDURE [UpdateRateHistory]
@ResCode VARCHAR(255),
@Grade VARCHAR(255),
@NewDate DATETIME
AS
--declare variables
DECLARE @HasRate int
DECLARE @HasRateToday int
DECLARE @ChangeRate int
--check if rate history exisits for res
SELECT @HasRate = count(*) from ratehistory where rescode = @ResCode
--check if there exists a rate history starting today
SELECT @HasRateToday = count(*) from ratehistory where periodstart = @NewDate and rescode = @ResCode and periodend is null
--check if the rate input is the same as the current ratehistory, -ie where period end is null
SELECT @ChangeRate = count(*) from ratehistory where periodend is null and rescode = @ResCode and Rate = (select rate from grade where grade = @Grade)
IF (@HasRate > 0)
BEGIN
IF (@ChangeRate < 1)
BEGIN
ELSE
IF (@HasRateToday > 0)
BEGIN
update ratehistory set rate = (select rate from grade where grade = @Grade) where periodstart = @NewDate and rescode = @ResCode and periodend is null
ELSE
UPDATE ratehistory set periodend = @NewDate-1 WHERE periodend is null and ResCode = @ResCode
insert into ratehistory(rescode,periodstart,rate) select @ResCode,@NewDate,rate from grade where grade = @Grade
END
END
ELSE
insert into ratehistory(rescode,periodstart,rate) select @ResCode,@NewDate,rate from grade where grade = @Grade
END
GO
-
This part
IF (@ChangeRate < 1)
BEGIN
ELSE
looks strange.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply