[RESOLVED] SPROC - Conditional Branching

[Mark Gambo]

Is there a better way of branching other that this:

DECLARE @Type INT
SET @Type = 2
 
If @Type = 1
    PRINT 'Number 1'
If @Type = 2
    PRINT 'Number 2'
If @Type = 3
    PRINT 'Number 3'
If @Type = 4
    PRINT 'Number 4'

I need this to work on the following db: MS SQL 7.0, MS SQL 2000, MS SQL 2K5 and possibly MySQL 4.1.

Thanks!

[sevenhalo]

You can try:

DECLARE @Type INT
SET @Type = 2

SELECT CASE @Type
	WHEN 1 THEN 'Number 1'
	WHEN 2 THEN 'Number 2'
	WHEN 3 THEN 'Number 3'
	WHEN 4 THEN 'Number 4'
	WHEN 5 THEN 'Number 5'
	ELSE 'Something Else'
END

[szlamany]

@sevenhalo - SELECT CASE is a SELECT statement - not a branch statement.

IF/blocks create logic path flow in the SPROC...

CASE/WHEN statements can only be put into a SELECT statement for altering data return.

[sevenhalo]

Just curious, couldn't you place it into a cursor and print the value from there?

---Edit-----------
Ohh, nvm... NOW I see what he's asking. Forget what I said.

[Mark Gambo]

So I guess I can only way for me to Conditional Branch is to use the example that I posted?

Thanks!

[szlamany]

There are several flavors of IF/blocks in T-SQL

If @Type=1 Print 'Got a 1' -- This is a single line If - the default
 
If @Type=1
        Print 'Got a 1'
Else    Print 'Not a 1' -- This is a single line If with Else
 
-- When you need several lines in the IF you need to make your own "blocks"
 
If @Type=1
Begin
  Print 'Got a 1'
  Print 'This is the second line of Got a 1'
End
Else
Begin
  Print 'Not a 1'
  Print 'This is the second line of Not a 1'
End

Whenever I start needing anything more then the default single line If or If/Else I start using the Begin/End blocks. If can have many Else layers within the Begin/End blocks - it's just a little overwhelming for the eyes with all those Begin/End's...

Here's an example of a SPROC that got pretty complex...

If @SD<@MemWelStart and @MemWelStart is not null
Begin
    Set @SD=@MemWelStart
    Set @SRsn=@SRsn+'A'     -- Start prior to WELSTART (member)
End
 
If @ED>@MemWelEnd and @MemWelEnd is not null
Begin
    Set @ED=@MemWelEnd
    Set @ERsn=@ERsn+'B'     -- End after WELEND (member)
End
 
If @MAffil<>'1'
Begin
    If @SD<@MasWelStart and @MasWelStart is not null
    Begin
        Set @SD=@MasWelStart
        Set @SRsn=@SRsn+'C' -- Start prior to WELSTART (patient)
    End
 
    If @ED>@MasWelEnd and @MasWelEnd is not null
    Begin
        Set @ED=@MasWelEnd
        Set @ERsn=@ERsn+'D' -- End after WELEND (patient)
    End
End
 
If @MAffil='3' and @WelHandi<>'Y'
Begin
    Set @DepRsn=''
    Set @ColEnd=null
 
    If @EOM19<@SD
    Begin
        Set @SRsn=@SRsn+'I'
        Set @ColStart=(Select Min(CStart) From @WelCollege Where @SD<=CEnd)
        If @ColStart is Null or @ColStart>@SD
        Begin
            Set @SD=@ColStart
        End
        Set @DepEnd=(Select Min(CEnd) From @WelCollege Where CStart=@SD)
        Set @DepRsn=IsNull((Select Top 1 CRsn From @WelCollege Where CStart=@SD and CRsn<>''),'G')
        If @DepEnd is Null 
        Begin
            Set @DepEnd=(Select Min(CEnd) From @WelCollege Where CStart=@ColStart)
            Set @DepRsn=IsNull((Select Top 1 CRsn From @WelCollege Where CStart=@ColStart and CRsn<>''),'G')
        End
    End
    Else
    Begin   
        Set @DepRsn='E'     -- Reached Age 19 and not in school
        Set @DepEnd=@EOM19  -- We are considering cutting off at age 19
        Set @ColEnd=(Select Top 1 CEnd From @WelCollege Where DateAdd(dd,-1,CStart)<=@DepEnd and CEnd>@SD Order by CStart Desc)
    End
 
    -- Let's see if we have a college start date that will give us a new cut off date
    If @ColEnd is not null
    Begin
        If @ColEnd>@DepEnd
        Begin
            Set @DepEnd=@ColEnd
            Set @DepRsn='G'     -- College end date reached
        End
    End
 
    If @ED>@DepEnd
    Begin
        Set @ED=@DepEnd
        Set @ERsn=@ERsn+@DepRsn
        If @ED>@EOM23
        Begin
            Set @ED=@EOM23
            Set @ERsn=@ERsn+'J'
        End
        Else
        Begin
            Update @EligHrs Set EEnd=@ED Where EStart=@OrigSD
            Insert into @EligHrs Select DateAdd(dd,1,@ED),@OrigED,@EElig,@EGrp,@EGrpOver,@ESRsn,@EERsn,@EEWho,''
        End
    End
End

[Mark Gambo]

Steve,

Thanks again. Since I introduced SPROCS into my app db access has been cut in half.

[szlamany]

Steve,

Thanks again. Since I introduced SPROCS into my app db access has been cut in half.

That is a really great piece of information - I wonder how many people, if they knew this, would finally abandon in-line SQL statements!

[Mark Gambo]

That is a really great piece of information - I wonder how many people, if they knew this, would finally abandon in-line SQL statements!

Too bad they don't work with MS Access DB , I have a few clients that would benefit greatly. I am trying to get them to upgrade to at least MySQL but they like the Access enviorment. Thats ok three of them have DB at about the size of 1.5 Gig. Oh boy I can't wait until I get the call:

"Hey Mark, we can't access the data in our database, what could be the problem?"

[Mark Gambo]

Steve,
Seriously, how can I design a SPROC in order to prevent a SQL Injection Attack? What I am currently doing now is analyzing the user's input prior to sending the SQL to DB (Searching for the keywords: INSERT, DELETE, Semi-Colons, 1=1, etc.). Is there a better way of doing this?

[szlamany]

Steve,
Seriously, how can I design a SPROC in order to prevent a SQL Injection Attack? What I am currently doing now is analyzing the user's input prior to sending the SQL to DB (Searching for the keywords: INSERT, DELETE, Semi-Colons, 1=1, etc.). Is there a better way of doing this?

Sounds like this might be better served as a new thread...

Give me an example of a SPROC that you have with parameters that you think can be SQL-injected.

[Mark Gambo]

Sounds like this might be better served as a new thread...

Give me an example of a SPROC that you have with parameters that you think can be SQL-injected.

Thanks, I'll do it a little later.