May 19, 2009 at 8:05 am
I'm trying out functions and am having difficlulty with a ensted IF statement.
I have 3 values - status (text) actuals (money) and committed (money)
If the status = "Closed" then I want to return the Actuals value, otherwise return whichever is the highest of Actuals or Committed.
but I'm getting nowhere
CREATE function dbo.Maxof( @status text, @actuals money, @Committed money)returns money as
begin
IF (@status='Closed')
Return @Result = @actuals
ELSE
IF (@actuals > @Committed)
return @actuals
ELSE
return @Committed
end if
end
May 19, 2009 at 8:10 am
Try this:
create function dbo.Maxof
(@status text,
@actuals money,
@Committed money)
returns money
as
begin
declare @Output money;
if (@status='Closed') or (@actuals > @Committed)
select @Output = @actuals;
else
select @Output = @Committed;
return @Output;
end;
There's no "End If" statement in T-SQL. That's one of the problems.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2009 at 8:20 am
You can enclose clauses from nested IFs in BEGIN ... END blocks to explicitally show the logic. If there is more than 1 statement in a clause, you have to use BEGIN ... END
It can look a little clumsy though
IF
BEGIN
IF
BEGIN
.... statements
END
END
ELSE
BEGIN
... statements
END
May 19, 2009 at 8:23 am
Hi Gsquared
many thanks for this, but I get incorrect syntax near ';'
also - where do I find help for UDFs?
May 19, 2009 at 8:38 am
Not sure about why the error message. Get rid of the semicolons, the function will still run, it's just less "clean".
I also noticed that one of your input parameters is "text" data type, but it should almost certainly be "varchar". To test the function, I changed it to varchar(100). I didn't change anything but that (was trying to find the semicolon issue), and it compiled and ran once I did that. (I hadn't looked at the data type when I was writing it.)
The most basic source of information on UDFs is Books Online. Where I learned them was SQL Server Bible. There's a whole chapter in there, and it really helped me out on the subject.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2009 at 9:19 am
it was just the last semicolon and of course the text/varchar change
many many thanks
May 19, 2009 at 10:36 am
GSquared (5/19/2009)
Get rid of the semicolons, the function will still run, it's just less "clean".
I often see code with semicolons here, but usually without. I have queried in DB2 a couple of times, and the application I used automatically appended any query with a semicolon. What purpose do they serve in SQL Server? Is there a benefit to them?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 19, 2009 at 11:43 am
Greg Snidow (5/19/2009)
GSquared (5/19/2009)
Get rid of the semicolons, the function will still run, it's just less "clean".I often see code with semicolons here, but usually without. I have queried in DB2 a couple of times, and the application I used automatically appended any query with a semicolon. What purpose do they serve in SQL Server? Is there a benefit to them?
They aren't necessary in T-SQL at this time. They will be, per Microsoft, in a future version of SQL Server, but they haven't (so far as I know) said which future version yet.
What they do is delimit end-of-command-phrase. That makes it just a tiny bit easier on the compiler. Doesn't really matter much to the engine.
I find them useful in terms of readability. Says where something ends.
If I wrote the above without periods, it would be harder to read. I treat SQL the same way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2009 at 11:46 am
GSquared (5/19/2009)
Greg Snidow (5/19/2009)
GSquared (5/19/2009)
Get rid of the semicolons, the function will still run, it's just less "clean".I often see code with semicolons here, but usually without. I have queried in DB2 a couple of times, and the application I used automatically appended any query with a semicolon. What purpose do they serve in SQL Server? Is there a benefit to them?
they aren't necessary in T-SQL at this time they will be, per Microsoft, in a future version of SQL Server, but they haven't (so far as I know) said which future version yet what they do is delimit end-of-command-phrase that makes it just a tiny bit easier on the compiler doesn't really matter much to the engine I find them useful in terms of readability says where something ends if I wrote the above without periods, it would be harder to read I treat SQL the same way
Just to illustrate the point, that's English without "end-of-phrase" punctuation/delimitation. Not as readable. The way I see it, written SQL gets a similar benefit.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2009 at 1:07 pm
GSquared (5/19/2009)
Greg Snidow (5/19/2009)
GSquared (5/19/2009)
Get rid of the semicolons, the function will still run, it's just less "clean".I often see code with semicolons here, but usually without. I have queried in DB2 a couple of times, and the application I used automatically appended any query with a semicolon. What purpose do they serve in SQL Server? Is there a benefit to them?
They aren't necessary in T-SQL at this time. They will be, per Microsoft, in a future version of SQL Server, but they haven't (so far as I know) said which future version yet.
What they do is delimit end-of-command-phrase. That makes it just a tiny bit easier on the compiler. Doesn't really matter much to the engine.
I find them useful in terms of readability. Says where something ends.
If I wrote the above without periods, it would be harder to read. I treat SQL the same way.
I'm a C# guy writing semicolons all over the day. It feels like something fresh, new not to use them in T-SQL 😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply