Nested IF statement and Declare problem

19 May 2006 4:47 PM

Daniel Regalia

This is prolly more of a gut check, but needed to know if this looks right.

I am making another Scalar function..
CREATE FUNCTION [dbo].[EvalTradeCode]
(
    @tradeSymbol char(15)
)
RETURNS int(1)
AS
BEGIN
Declare @intOffset int

If (left(tradesymbol, 1) = '@')
    If (isnumeric(left(right(tradesymbol, 6), 1))
        @intOffset = 1

If left(tradesymbol, 1) = '+'
    If isnumeric(left(right(tradesymbol, 6), 1)
        @intOffset = 1

IF left(tradesymbol, 1) <>'@' and left(tradesymbol, 1) <> '+'
    If isnumeric(left(right(tradesymbol, 5), 1)
        @intOffset = 1

    RETURN @intOffset

END

I was getting an error because I was using the 'then' statement in there
(remember..i'm a VB programmer... and I did check out the bol site..lol)

I took the 'then' statements out. and now the only error that comes up is the:
'Oncorrect syntax near '@intOffset' ' Error.

I'm not sure if this is because of the way that it's being used in the
function, or if i've got something bass ackwards.

Thanks for your input!

~Doc

--
www.krushradio.com - Internet Radio for the rest of us

19 May 2006 4:54 PM

Vern Rabe

Try replacing
@intOffset = 1
with
SET @intOffset = 1

HTH
Vern

Show quote

"Daniel Regalia" wrote:

> This is prolly more of a gut check, but needed to know if this looks right.
>
> I am making another Scalar function..
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
>     @tradeSymbol char(15)
> )
> RETURNS int(1)
> AS
> BEGIN
> Declare @intOffset int
>
> If (left(tradesymbol, 1) = '@')
>     If (isnumeric(left(right(tradesymbol, 6), 1))
>         @intOffset = 1
>
> If left(tradesymbol, 1) = '+'
>     If isnumeric(left(right(tradesymbol, 6), 1)
>         @intOffset = 1
>
> IF left(tradesymbol, 1) <>'@' and left(tradesymbol, 1) <> '+'
>     If isnumeric(left(right(tradesymbol, 5), 1)
>         @intOffset = 1
>
>     RETURN @intOffset
>
> END
>
> I was getting an error because I was using the 'then' statement in there
> (remember..i'm a VB programmer... and I did check out the bol site..lol)
>
> I took the 'then' statements out. and now the only error that comes up is the:
> 'Oncorrect syntax near '@intOffset' ' Error.
>
> I'm not sure if this is because of the way that it's being used in the
> function, or if i've got something bass ackwards.
>
> Thanks for your input!
>
> ~Doc
>
> --
> www.krushradio.com - Internet Radio for the rest of us

19 May 2006 5:01 PM

Omnibuzz

Not one.. there are lots of changes :)
no offences.
Here is the function.. Hope this helps.

CREATE FUNCTION [dbo].[EvalTradeCode]
(
    @tradeSymbol char(15)
)
RETURNS int
AS
BEGIN
Declare @intOffset int

If (left(@tradeSymbol, 1) = '@')
    If isnumeric(left(right(@tradeSymbol, 6), 1)) = 1
        set @intOffset = 1

If left(@tradeSymbol, 1) = '+'
    If isnumeric(left(right(@tradeSymbol, 6), 1)) = 1
        set @intOffset = 1

IF left(@tradeSymbol, 1) <>'@' and left(@tradeSymbol, 1) <> '+'
    If isnumeric(left(right(@tradeSymbol, 5), 1)) = 1
        set @intOffset = 1

    RETURN @intOffset

END

19 May 2006 5:11 PM

Daniel Regalia

None Taken...

It's a learning experience for me :D. Just add this question to my beer
tab. Thanks OmniBuzz

~Doc

--
www.krushradio.com - Internet Radio for the rest of us

Show quote

"Omnibuzz" wrote:

> Not one.. there are lots of changes :)
> no offences.
> Here is the function.. Hope this helps.
>
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
>     @tradeSymbol char(15)
> )
> RETURNS int
> AS
> BEGIN
> Declare @intOffset int
>
> If (left(@tradeSymbol, 1) = '@')
>     If isnumeric(left(right(@tradeSymbol, 6), 1)) = 1
>         set @intOffset = 1
>
> If left(@tradeSymbol, 1) = '+'
>     If isnumeric(left(right(@tradeSymbol, 6), 1)) = 1
>         set @intOffset = 1
>
> IF left(@tradeSymbol, 1) <>'@' and left(@tradeSymbol, 1) <> '+'
>     If isnumeric(left(right(@tradeSymbol, 5), 1)) = 1
>         set @intOffset = 1
>
>     RETURN @intOffset
>
> END
>

19 May 2006 5:16 PM

Omnibuzz

Sure Sir. I remember the first one you promised too..
Anything for a beer :)

Show quote

"Daniel Regalia" wrote:

> None Taken...
>
> It's a learning experience for me :D. Just add this question to my beer
> tab. Thanks OmniBuzz
>
> ~Doc
>
>
>
> --
> www.krushradio.com - Internet Radio for the rest of us
>
>
> "Omnibuzz" wrote:
>
> > Not one.. there are lots of changes :)
> > no offences.
> > Here is the function.. Hope this helps.
> >
> > CREATE FUNCTION [dbo].[EvalTradeCode]
> > (
> >     @tradeSymbol char(15)
> > )
> > RETURNS int
> > AS
> > BEGIN
> > Declare @intOffset int
> >
> > If (left(@tradeSymbol, 1) = '@')
> >     If isnumeric(left(right(@tradeSymbol, 6), 1)) = 1
> >         set @intOffset = 1
> >
> > If left(@tradeSymbol, 1) = '+'
> >     If isnumeric(left(right(@tradeSymbol, 6), 1)) = 1
> >         set @intOffset = 1
> >
> > IF left(@tradeSymbol, 1) <>'@' and left(@tradeSymbol, 1) <> '+'
> >     If isnumeric(left(right(@tradeSymbol, 5), 1)) = 1
> >         set @intOffset = 1
> >
> >     RETURN @intOffset
> >
> > END
> >

19 May 2006 5:02 PM

Daniel Regalia

Gave it a shot.....it didn't like it

Incorrect syntax near the keyword 'Set'

---
If (left(tradesymbol, 1) = '@')
   If (isnumeric(left(right(tradesymbol, 6), 1))
     Set @intOffset = 1

If left(tradesymbol, 1) = '+'
   If isnumeric(left(right(tradesymbol, 6), 1)
     Set @intOffset = 1

IF left(tradesymbol, 1) <>'@' and left(tradesymbol, 1) <> '+'
If isnumeric(left(right(tradesymbol, 5), 1)
    Set @intOffset = 1
------

www.krushradio.com - Internet Radio for the rest of us

Show quote

"Vern Rabe" wrote:

> Try replacing
>    @intOffset = 1
> with
>    SET @intOffset = 1
>
> HTH
> Vern
>
> "Daniel Regalia" wrote:
>
> > This is prolly more of a gut check, but needed to know if this looks right.
> >
> > I am making another Scalar function..
> > CREATE FUNCTION [dbo].[EvalTradeCode]
> > (
> >     @tradeSymbol char(15)
> > )
> > RETURNS int(1)
> > AS
> > BEGIN
> > Declare @intOffset int
> >
> > If (left(tradesymbol, 1) = '@')
> >     If (isnumeric(left(right(tradesymbol, 6), 1))
> >         @intOffset = 1
> >
> > If left(tradesymbol, 1) = '+'
> >     If isnumeric(left(right(tradesymbol, 6), 1)
> >         @intOffset = 1
> >
> > IF left(tradesymbol, 1) <>'@' and left(tradesymbol, 1) <> '+'
> >     If isnumeric(left(right(tradesymbol, 5), 1)
> >         @intOffset = 1
> >
> >     RETURN @intOffset
> >
> > END
> >
> > I was getting an error because I was using the 'then' statement in there
> > (remember..i'm a VB programmer... and I did check out the bol site..lol)
> >
> > I took the 'then' statements out. and now the only error that comes up is the:
> > 'Oncorrect syntax near '@intOffset' ' Error.
> >
> > I'm not sure if this is because of the way that it's being used in the
> > function, or if i've got something bass ackwards.
> >
> > Thanks for your input!
> >
> > ~Doc
> >
> > --
> > www.krushradio.com - Internet Radio for the rest of us

19 May 2006 5:05 PM

Omnibuzz

I feel it can better be written this way. You can validate better than me.
You should be a procedural logic expert :) Let me know.

CREATE FUNCTION [dbo].[EvalTradeCode]
(
    @tradeSymbol char(15)
)
RETURNS int
AS
BEGIN
Declare @intOffset int

Set @intOffset = 0

If (left(@tradeSymbol, 1) = '@') or (left(@tradeSymbol, 1) = '+')
begin
    If isnumeric(left(right(@tradeSymbol, 6), 1)) = 1
        set @intOffset = 1
end
else
begin
    If isnumeric(left(right(@tradeSymbol, 5), 1)) = 1
        set @intOffset = 1
end
    RETURN @intOffset

END

19 May 2006 5:31 PM

Jim Underwood

One problem is that your parenthesis are not properly matching up. Another,
and this is just personal preference, is you are not using begin and end to
group your if else logic. I prefer to have a begin and end for every if
statement, and indent accordingly. It makes the code easier to follow, and
leaves no confusion as to the order of nested ifs.

Show quote

"Daniel Regalia" <DanielRega***@discussions.microsoft.com> wrote in message
news:4618C615-2579-4999-B0BC-3DB935F7527F@microsoft.com...
> This is prolly more of a gut check, but needed to know if this looks
right.
>
> I am making another Scalar function..
> CREATE FUNCTION [dbo].[EvalTradeCode]
> (
> @tradeSymbol char(15)
> )
> RETURNS int(1)
> AS
> BEGIN
> Declare @intOffset int
>
> If (left(tradesymbol, 1) = '@')
> If (isnumeric(left(right(tradesymbol, 6), 1))
> @intOffset = 1
>
> If left(tradesymbol, 1) = '+'
> If isnumeric(left(right(tradesymbol, 6), 1)
> @intOffset = 1
>
> IF left(tradesymbol, 1) <>'@' and left(tradesymbol, 1) <> '+'
> If isnumeric(left(right(tradesymbol, 5), 1)
> @intOffset = 1
>
> RETURN @intOffset
>
> END
>
> I was getting an error because I was using the 'then' statement in there
> (remember..i'm a VB programmer... and I did check out the bol site..lol)
>
> I took the 'then' statements out. and now the only error that comes up is
the:
> 'Oncorrect syntax near '@intOffset' ' Error.
>
> I'm not sure if this is because of the way that it's being used in the
> function, or if i've got something bass ackwards.
>
> Thanks for your input!
>
> ~Doc
>
> --
> www.krushradio.com - Internet Radio for the rest of us