Loading ...
Sorry, an error occurred while loading the content.
 

Re: [mugh-sqlcon] case statement

Expand Messages
  • Roji. P. Thomas
    Ravi, You cannot use the T-SQL CASE to achieve the behaviour of Switch or SELECT CASE in other languages. In T-SQL CASE is an expression, not a statement. Also
    Message 1 of 18 , Oct 1, 2005
      Ravi,
       
          You cannot use the T-SQL CASE to achieve the behaviour of Switch or SELECT CASE
      in other languages. In T-SQL CASE is an expression, not a statement.
       
      Also Jai was wrong where he said you can call a sp inside a case construct.
       
      CASE Evaluates a list of conditions and returns one of multiple possible result expressions
      of the same datatype.
       
      You may have to use the IF... ELSE conditions for branching.
       
      Regards
      Roji. P. Thomas
       
      ----- Original Message -----
      From: Ravi
      Sent: Friday, September 30, 2005 11:36 PM
      Subject: [mugh-sqlcon] case statement

      i know how to use case in select statement..

      but is there any way i can use case statement as i can
      use if and else statement in stored procedure.


      i get one parameter as i/p in n i have to compare that
      to a parameter and then execute SP based on the
      condition.. is there any way i can do that w/o IF
      Else.

      Thanks in advance.

      Regards,
      Ravi.

      Regards,
      Ravi Juneja


                 
      __________________________________
      Yahoo! Mail - PC Magazine Editors' Choice 2005
      http://mail.yahoo.com
    • Jai
      thnx for correcting me...Thomas... ... __________________________________ Yahoo! Mail - PC Magazine Editors Choice 2005 http://mail.yahoo.com
      Message 2 of 18 , Oct 1, 2005
        thnx for correcting me...Thomas...

        --- "Roji. P. Thomas" <thomasroji@...> wrote:

        > Ravi,
        >
        > You cannot use the T-SQL CASE to achieve the
        > behaviour of Switch or SELECT CASE
        > in other languages. In T-SQL CASE is an expression,
        > not a statement.
        >
        > Also Jai was wrong where he said you can call a sp
        > inside a case construct.
        >
        > CASE Evaluates a list of conditions and returns one
        > of multiple possible result expressions
        > of the same datatype.
        >
        > You may have to use the IF... ELSE conditions for
        > branching.
        >
        > Regards
        > Roji. P. Thomas
        > http://toponewithties.blogspot.com
        >
        > ----- Original Message -----
        > From: Ravi
        > To: sqlcon@yahoogroups.com
        > Sent: Friday, September 30, 2005 11:36 PM
        > Subject: [mugh-sqlcon] case statement
        >
        >
        > i know how to use case in select statement..
        >
        > but is there any way i can use case statement as i
        > can
        > use if and else statement in stored procedure.
        >
        >
        > i get one parameter as i/p in n i have to compare
        > that
        > to a parameter and then execute SP based on the
        > condition.. is there any way i can do that w/o IF
        > Else.
        >
        > Thanks in advance.
        >
        > Regards,
        > Ravi.
        >
        > Regards,
        > Ravi Juneja
        >
        >
        >
        > __________________________________
        > Yahoo! Mail - PC Magazine Editors' Choice 2005
        > http://mail.yahoo.com
        >
        >
        > SqlCon is a special interests group run under the
        > aegis of MUGH - http://www.mugh.net
        > http://www.mugh.net/sql
        >
        > Visit http://www.mugh.net/dnug to join .NET User
        > Group.
        >
        >
        >
        >
        >
        ------------------------------------------------------------------------------
        > YAHOO! GROUPS LINKS
        >
        > a.. Visit your group "sqlcon" on the web.
        >
        > b.. To unsubscribe from this group, send an
        > email to:
        > sqlcon-unsubscribe@yahoogroups.com
        >
        > c.. Your use of Yahoo! Groups is subject to the
        > Yahoo! Terms of Service.
        >
        >
        >
        ------------------------------------------------------------------------------
        >
        >




        __________________________________
        Yahoo! Mail - PC Magazine Editors' Choice 2005
        http://mail.yahoo.com
      • Meiyalagan.B
        Ravi, If executing the proc based on a variable value is your intention ..... have a look at this code segment. declare @sql varchar(100) , @your_variable int
        Message 3 of 18 , Oct 1, 2005
          Ravi,
           
          If executing the proc based on a variable value is your intention ..... have a look at this code segment.
           
          declare @sql varchar(100) , @your_variable int , @debug int
          set @sql = ''
          set @your_variable = 0
          ........
          ........
          select @sql = case @your_variable
                                    when 1 then 'spProc_1'
                                    when 2 then 'spProc_2'
                                    else 'spProc_3'
                                end
          if @debug > 0
               print 'SQL : ' + @sql
          else
               exec @sql
           
          * Do remember that we are using Dynamic SQL.
           
          hth
           
          Thanks
          Mei

          "Roji. P. Thomas" <thomasroji@...> wrote:
          Ravi,
           
              You cannot use the T-SQL CASE to achieve the behaviour of Switch or SELECT CASE
          in other languages. In T-SQL CASE is an expression, not a statement.
           
          Also Jai was wrong where he said you can call a sp inside a case construct.
           
          CASE Evaluates a list of conditions and returns one of multiple possible result expressions
          of the same datatype.
           
          You may have to use the IF... ELSE conditions for branching.
           
          Regards
          Roji. P. Thomas
           
          ----- Original Message -----
          From: Ravi
          Sent: Friday, September 30, 2005 11:36 PM
          Subject: [mugh-sqlcon] case statement

          i know how to use case in select statement..

          but is there any way i can use case statement as i can
          use if and else statement in stored procedure.


          i get one parameter as i/p in n i have to compare that
          to a parameter and then execute SP based on the
          condition.. is there any way i can do that w/o IF
          Else.

          Thanks in advance.

          Regards,
          Ravi.

          Regards,
          Ravi Juneja


                     
          __________________________________
          Yahoo! Mail - PC Magazine Editors' Choice 2005
          http://mail.yahoo.com


          Yahoo! for Good
          Click here to donate to the Hurricane Katrina relief effort.

        • Santhosh
          Hi Guys, I have a scenario, in which I have to optimise a stored procedure,which deletes unwated data. There are 2 table like parent child(but no referential
          Message 4 of 18 , Oct 2, 2005
            Hi Guys,
            I have a scenario, in which I have to optimise a
            stored procedure,which deletes unwated data.
            There are 2 table like parent child(but no referential
            integrity). The parent contains atleast 1 crore record
            and the child contains atelast 16 crore records.
            Consistintly the database has atleast 17000
            connections. We have process that selects and
            summarises data that runs for every 1 hour.
            The stored procedure does the update like this

            it selects the record key into a temporary table and
            then it delets the record in the child table and then
            it deletes the parent table and then dropps the temp
            table..

            I want to know whether if the deletion can be done
            with out using the temp table will it improve the
            performance.

            Sorry for the longggggggg mail and I appreciate your
            time. Thank you..

            with smiles
            santhosh






            ______________________________________________________
            Yahoo! for Good
            Donate to the Hurricane Katrina relief effort.
            http://store.yahoo.com/redcross-donate3/
          • sqlindia
            Hi Santosh first of all, tell me please why you select record key into temp table????? do you have similiar column into both parents and child table? if yes
            Message 5 of 18 , Oct 3, 2005
              Hi Santosh

              first of all, tell me please why you select record key into temp
              table?????

              do you have similiar column into both parents and child table?
              if yes
              -then you need to see if cluster index(preferably otherwise non-
              cluster will make difference too) on this record key if not you must
              have it in order to find these records directly through index rather
              sql engine scan whole table for this record key. if where clause is
              there in query then make sure you have created covered index on
              these reference columns exists in where clause.

              if not
              -you still need to do above mentioned things.

              This is quick solution and without knowing complete scenario..I hope
              it will help.

              sqlindia



              --- In sqlcon@yahoogroups.com, Santhosh <san_spy@y...> wrote:
              > Hi Guys,
              > I have a scenario, in which I have to optimise a
              > stored procedure,which deletes unwated data.
              > There are 2 table like parent child(but no referential
              > integrity). The parent contains atleast 1 crore record
              > and the child contains atelast 16 crore records.
              > Consistintly the database has atleast 17000
              > connections. We have process that selects and
              > summarises data that runs for every 1 hour.
              > The stored procedure does the update like this
              >
              > it selects the record key into a temporary table and
              > then it delets the record in the child table and then
              > it deletes the parent table and then dropps the temp
              > table..
              >
              > I want to know whether if the deletion can be done
              > with out using the temp table will it improve the
              > performance.
              >
              > Sorry for the longggggggg mail and I appreciate your
              > time. Thank you..
              >
              > with smiles
              > santhosh
              >
              >
              >
              >
              >
              >
              > ______________________________________________________
              > Yahoo! for Good
              > Donate to the Hurricane Katrina relief effort.
              > http://store.yahoo.com/redcross-donate3/
            • Santhosh
              Hi, thanks for your mail. It is a legacy code and im optimising it.We have proper index in the table. The problem is the high traffic. As I said around 10000
              Message 6 of 18 , Oct 3, 2005
                Hi,
                thanks for your mail. It is a legacy code and im
                optimising it.We have proper index in the table. The
                problem is the high traffic. As I said around 10000
                connection will keep on inserting in these tables.I
                think thats y they might have transferred the ids to a
                temp table and then delete in these tables. okey
                thanks for your reply

                with smiles
                santhosh

                --- sqlindia <sqlindia@...> wrote:

                > Hi Santosh
                >
                > first of all, tell me please why you select record
                > key into temp
                > table?????
                >
                > do you have similiar column into both parents and
                > child table?
                > if yes
                > -then you need to see if cluster index(preferably
                > otherwise non-
                > cluster will make difference too) on this record key
                > if not you must
                > have it in order to find these records directly
                > through index rather
                > sql engine scan whole table for this record key. if
                > where clause is
                > there in query then make sure you have created
                > covered index on
                > these reference columns exists in where clause.
                >
                > if not
                > -you still need to do above mentioned things.
                >
                > This is quick solution and without knowing complete
                > scenario..I hope
                > it will help.
                >
                > sqlindia
                >
                >
                >
                > --- In sqlcon@yahoogroups.com, Santhosh
                > <san_spy@y...> wrote:
                > > Hi Guys,
                > > I have a scenario, in which I have to optimise a
                > > stored procedure,which deletes unwated data.
                > > There are 2 table like parent child(but no
                > referential
                > > integrity). The parent contains atleast 1 crore
                > record
                > > and the child contains atelast 16 crore records.
                > > Consistintly the database has atleast 17000
                > > connections. We have process that selects and
                > > summarises data that runs for every 1 hour.
                > > The stored procedure does the update like this
                > >
                > > it selects the record key into a temporary table
                > and
                > > then it delets the record in the child table and
                > then
                > > it deletes the parent table and then dropps the
                > temp
                > > table..
                > >
                > > I want to know whether if the deletion can be done
                > > with out using the temp table will it improve the
                > > performance.
                > >
                > > Sorry for the longggggggg mail and I appreciate
                > your
                > > time. Thank you..
                > >
                > > with smiles
                > > santhosh
                > >
                > >
                > >
                > >
                > >
                > >
                > >
                >
                ______________________________________________________
                >
                > > Yahoo! for Good
                > > Donate to the Hurricane Katrina relief effort.
                > > http://store.yahoo.com/redcross-donate3/
                >
                >
                >




                __________________________________
                Yahoo! Mail - PC Magazine Editors' Choice 2005
                http://mail.yahoo.com
              • Santhosh
                Hi guys, DATEPART( yyyy, CreateDate ) = @SelectYear AND DATEPART( mm, CreateDate ) = @SelectMonth AND DATEPART( dd, CreateDate ) = @SelectDay AND
                Message 7 of 18 , Oct 3, 2005
                  Hi guys,


                  DATEPART( yyyy, CreateDate ) = @SelectYear
                  AND
                  DATEPART( mm, CreateDate ) = @SelectMonth
                  AND
                  DATEPART( dd, CreateDate ) = @SelectDay
                  AND
                  DATEPART( hh, CreateDate ) = @SelectHour


                  With Smiles
                  Santhosh S.J.
                  ReadMe @ santhoshpakkangal.blogspot.com



                  __________________________________
                  Yahoo! Mail - PC Magazine Editors' Choice 2005
                  http://mail.yahoo.com
                • Santhosh
                  Hi guys, The below part is the where condition of a query is there any alternate way to compare that directly (We store the created date as datetime but while
                  Message 8 of 18 , Oct 3, 2005
                    Hi guys,
                    The below part is the where condition of a query is
                    there any alternate way to compare that directly
                    (We store the created date as datetime but while
                    comparing we have to compare till hours only.)

                    Where
                    DATEPART( yyyy, CreateDate ) = @SelectYear
                    AND
                    DATEPART( mm, CreateDate ) = @SelectMonth
                    AND
                    DATEPART( dd, CreateDate ) = @SelectDay
                    AND
                    DATEPART( hh, CreateDate ) = @SelectHour

                    Can the above be replaced as

                    cast(createdate,datetime) = cast('12/12/2005 1:00
                    PM',datetime)

                    but the comparision has to be done only till the hour
                    not till the minutes and seconds.

                    Thanks for your help

                    with smiles
                    santhosh



                    __________________________________
                    Yahoo! Mail - PC Magazine Editors' Choice 2005
                    http://mail.yahoo.com
                  • Jai
                    What help do you really need ??? ... __________________________________ Yahoo! Mail - PC Magazine Editors Choice 2005 http://mail.yahoo.com
                    Message 9 of 18 , Oct 3, 2005
                      What help do you really need ???

                      --- Santhosh <san_spy@...> wrote:

                      > Hi guys,
                      >
                      >
                      > DATEPART( yyyy, CreateDate ) = @SelectYear
                      > AND
                      > DATEPART( mm, CreateDate ) = @SelectMonth
                      > AND
                      > DATEPART( dd, CreateDate ) = @SelectDay
                      > AND
                      > DATEPART( hh, CreateDate ) = @SelectHour
                      >
                      >
                      > With Smiles
                      > Santhosh S.J.
                      > ReadMe @ santhoshpakkangal.blogspot.com
                      >
                      >
                      >
                      > __________________________________
                      > Yahoo! Mail - PC Magazine Editors' Choice 2005
                      > http://mail.yahoo.com
                      >




                      __________________________________
                      Yahoo! Mail - PC Magazine Editors' Choice 2005
                      http://mail.yahoo.com
                    • Rajkumar
                      Hi Santhosh, Try using DATEDIFF(hour,cast(createdate,datetime),cast( 12/12/2005 1:00 PM ,datetime)) = 0 hope this works. Regards, Raj Santhosh
                      Message 10 of 18 , Oct 4, 2005
                        Hi Santhosh,
                         
                        Try using
                         
                        DATEDIFF(hour,cast(createdate,datetime),cast('12/12/2005 1:00
                        PM',datetime)) = 0

                         

                        hope this works.

                         

                        Regards,

                        Raj



                        Santhosh <san_spy@...> wrote:
                        Hi guys,
                           The below part is the where condition of a query is
                        there any alternate way to compare that directly
                        (We store the created date as datetime but while
                        comparing we have to compare till hours only.)

                        Where                                               
                        DATEPART( yyyy, CreateDate ) =  @SelectYear
                                                                      AND
                        DATEPART( mm, CreateDate )   =  @SelectMonth
                                                                      AND
                        DATEPART( dd, CreateDate )   =  @SelectDay
                                                                      AND
                        DATEPART( hh, CreateDate )   =  @SelectHour

                        Can the above be replaced as

                        cast(createdate,datetime) = cast('12/12/2005 1:00
                        PM',datetime)

                        but the comparision has to be done only till the hour
                        not till the minutes and seconds.

                        Thanks for your help

                        with smiles
                        santhosh


                                   
                        __________________________________
                        Yahoo! Mail - PC Magazine Editors' Choice 2005
                        http://mail.yahoo.com


                        Yahoo! for Good
                        Click here to donate to the Hurricane Katrina relief effort.

                      • Santhosh
                        Sorry guys, my full question is below The below part is the where condition of a query is there any alternate way to compare that directly (We store the
                        Message 11 of 18 , Oct 4, 2005
                          Sorry guys, my full question is below

                          The below part is the where condition of a query is
                          there any alternate way to compare that directly
                          (We store the created date as datetime but while
                          comparing we have to compare till hours only.)

                          Where
                          DATEPART( yyyy, CreateDate ) = @SelectYear
                          AND
                          DATEPART( mm, CreateDate ) = @SelectMonth
                          AND
                          DATEPART( dd, CreateDate ) = @SelectDay
                          AND
                          DATEPART( hh, CreateDate ) = @SelectHour

                          Can the above be replaced as

                          cast(createdate,datetime) = cast('12/12/2005 1:00
                          PM',datetime)

                          but the comparision has to be done only till the hour
                          not till the minutes and seconds.

                          Thanks for your help





                          With Smiles
                          Santhosh S.J.
                          ReadMe @ santhoshpakkangal.blogspot.com



                          __________________________________
                          Yahoo! Mail - PC Magazine Editors' Choice 2005
                          http://mail.yahoo.com
                        • Rajkumar
                          Hi Santhosh, Try this DATEDIFF(hour, cast(createdate,datetime) = cast( 12/12/2005 1:00 PM ,datetime)) = 0 Hope this works for you. Regards, Raj Santhosh
                          Message 12 of 18 , Oct 4, 2005
                            Hi Santhosh,
                             
                            Try this
                             
                            DATEDIFF(hour, cast(createdate,datetime) = cast('12/12/2005 1:00
                            PM',datetime)) = 0

                             

                            Hope this works for you.

                             

                            Regards,

                            Raj


                             


                            Santhosh <san_spy@...> wrote:
                            Hi guys,
                               The below part is the where condition of a query is
                            there any alternate way to compare that directly
                            (We store the created date as datetime but while
                            comparing we have to compare till hours only.)

                            Where                                               
                            DATEPART( yyyy, CreateDate ) =  @SelectYear
                                                                          AND
                            DATEPART( mm, CreateDate )   =  @SelectMonth
                                                                          AND
                            DATEPART( dd, CreateDate )   =  @SelectDay
                                                                          AND
                            DATEPART( hh, CreateDate )   =  @SelectHour

                            Can the above be replaced as

                            cast(createdate,datetime) = cast('12/12/2005 1:00
                            PM',datetime)

                            but the comparision has to be done only till the hour
                            not till the minutes and seconds.

                            Thanks for your help

                            with smiles
                            santhosh


                                       
                            __________________________________
                            Yahoo! Mail - PC Magazine Editors' Choice 2005
                            http://mail.yahoo.com


                            Yahoo! for Good
                            Click here to donate to the Hurricane Katrina relief effort.

                          • Jai
                            hi santhosh... its always better to format the date in the format of yyyymmddhh and then make the comaprison...you can use the convert function for this
                            Message 13 of 18 , Oct 4, 2005
                              hi santhosh...

                              its always better to format the date in the format of
                              yyyymmddhh and then make the comaprison...you can use
                              the convert function for this purpose...

                              hope iot helps..

                              thnx Jai


                              --- Santhosh <san_spy@...> wrote:

                              > Sorry guys, my full question is below
                              >
                              > The below part is the where condition of a query
                              > is
                              > there any alternate way to compare that directly
                              > (We store the created date as datetime but while
                              > comparing we have to compare till hours only.)
                              >
                              > Where
                              >
                              > DATEPART( yyyy, CreateDate ) = @SelectYear
                              > AND
                              > DATEPART( mm, CreateDate ) = @SelectMonth
                              > AND
                              > DATEPART( dd, CreateDate ) = @SelectDay
                              > AND
                              > DATEPART( hh, CreateDate ) = @SelectHour
                              >
                              > Can the above be replaced as
                              >
                              > cast(createdate,datetime) = cast('12/12/2005 1:00
                              > PM',datetime)
                              >
                              > but the comparision has to be done only till the
                              > hour
                              > not till the minutes and seconds.
                              >
                              > Thanks for your help
                              >
                              >
                              >
                              >
                              >
                              > With Smiles
                              > Santhosh S.J.
                              > ReadMe @ santhoshpakkangal.blogspot.com
                              >
                              >
                              >
                              > __________________________________
                              > Yahoo! Mail - PC Magazine Editors' Choice 2005
                              > http://mail.yahoo.com
                              >





                              ______________________________________________________
                              Yahoo! for Good
                              Donate to the Hurricane Katrina relief effort.
                              http://store.yahoo.com/redcross-donate3/
                            • Ramesh Karnati
                              Hi Santhosh, Declare @dDate as DateTime Set @dDate = convert(DateTime, YYYY-MM-DD HH:00:00 ) -- Your choice of date with Minutes and seconds as 00 Select
                              Message 14 of 18 , Oct 4, 2005
                                Hi Santhosh,
                                 
                                Declare @dDate as DateTime
                                Set @dDate = convert(DateTime, 'YYYY-MM-DD HH:00:00')
                                -- Your choice of date with Minutes and seconds as "00"
                                 
                                Select <<Column List>>
                                From <<TableName>>
                                Where Convert(VarChar(13), <<Table.DateTimeColumn>>, 120) = Convert(VarChar(13), @dDate, 120)
                                This is may be not be the optimised way but, the according to the requirement it works.
                                 
                                Note: Instead of style "120", we can use other styles which supports Date and Time (eg. 113, 121) with Convert function.
                                 
                                Hope this helps you.
                                 
                                Thanks,
                                Karnati.

                                Santhosh <san_spy@...> wrote:
                                Sorry guys, my full question is below

                                   The below part is the where condition of a query is
                                there any alternate way to compare that directly
                                (We store the created date as datetime but while
                                comparing we have to compare till hours only.)

                                Where                                               
                                DATEPART( yyyy, CreateDate ) =  @SelectYear
                                                                              AND
                                DATEPART( mm, CreateDate )   =  @SelectMonth
                                                                              AND
                                DATEPART( dd, CreateDate )   =  @SelectDay
                                                                              AND
                                DATEPART( hh, CreateDate )   =  @SelectHour

                                Can the above be replaced as

                                cast(createdate,datetime) = cast('12/12/2005 1:00
                                PM',datetime)

                                but the comparision has to be done only till the hour
                                not till the minutes and seconds.

                                Thanks for your help





                                With Smiles
                                Santhosh S.J.
                                ReadMe @ santhoshpakkangal.blogspot.com


                                           
                                __________________________________
                                Yahoo! Mail - PC Magazine Editors' Choice 2005
                                http://mail.yahoo.com


                                Yahoo! for Good
                                Click here to donate to the Hurricane Katrina relief effort.

                              Your message has been successfully submitted and would be delivered to recipients shortly.