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

Default values in a T-SQL proc ??

Expand Messages
  • John Warner
    I have a stored proc with several parameters. I provide default values for the params. Example: CREATE PROC MyTest ( @p1 varchar(10) = % , @p2 varchar(5) =
    Message 1 of 9 , Nov 1, 2007
    • 0 Attachment
      I have a stored proc with several parameters. I provide default values for
      the params. Example:

      CREATE PROC MyTest (
      @p1 varchar(10) = '%',
      @p2 varchar(5) = 'value' )
      As blah blah
      WHERE field LIKE @p1 AND field2 Like @p2


      Exec MyTest 'Value', 'AnotherValue'

      Result set returned all is well.

      Exec MyTest '', 'AnotherValue'

      Error.

      What am I doing wrong? What I mean is I want these two parameters to be
      optional and if omitted to use my default values. I don't want to drop
      them out of the where clause, I want them to revert to my default. My other
      issue is what do I do for a place holder. My real proc that is in question
      here has about a dozen parameters, I'd like to supply values at times to
      only a few.

      Hope I'm being clear here. Thanks.

      (SQL Server 2K)

      John Warner
    • Arnie Rowland
      John, Here is my teaching example of overloaded or optional parameters. -- Demonstrates the use of optional parameters (Overloading) USE Northwind
      Message 2 of 9 , Nov 1, 2007
      • 0 Attachment
        John,

        Here is my 'teaching' example of 'overloaded' or 'optional' parameters.

        -- >>>> Demonstrates the use of optional parameters (Overloading)

        USE Northwind
        GO

        IF EXISTS
        ( SELECT ROUTINE_NAME
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'EmployeesSELECT '
        )
        DROP PROCEDURE dbo.EmployeesSELECT
        GO

        CREATE PROCEDURE dbo. EmployeesSELECT
        ( @EmployeeID int = NULL
        , @FirstName varchar(20) = NULL
        , @LastName varchar(20) = NULL
        )
        AS

        SELECT
        EmployeeID
        , FirstName
        , LastName
        FROM Employees
        WHERE ( EmployeeID = coalesce( @EmployeeID, EmployeeID )
        AND FirstName = coalesce( @FirstName, FirstName )
        AND LastName = coalesce( @LastName, LastName )
        )
        GO

        EXECUTE dbo.EmployeesSELECT @EmployeeID = 2
        EXECUTE dbo.EmployeesSELECT @LastName = 'Fuller'
        EXECUTE dbo.EmployeesSELECT @FirstName = 'Nancy'

        IF this is not quite what you need, let me know.

        (Note: this is method usually does NOT create an efficient execution
        plan. Performance may be improved by add the [ WITH RECOMPILE ] option.)


        Regards,

        - Arnie Rowland, MVP (SQL Server)

        "I am a great believer in luck, and I find that the harder I work, the
        more I have of it." - Thomas Jefferson (1743-1826)



        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
        Sent: Thursday, November 01, 2007 7:42 AM
        To: Arnie
        Subject: [SQLQueriesNoCode] Default values in a T-SQL proc ??

        I have a stored proc with several parameters. I provide default values
        for
        the params. Example:

        CREATE PROC MyTest (
        @p1 varchar(10) = '%',
        @p2 varchar(5) = 'value' )
        As blah blah
        WHERE field LIKE @p1 AND field2 Like @p2


        Exec MyTest 'Value', 'AnotherValue'

        Result set returned all is well.

        Exec MyTest '', 'AnotherValue'

        Error.

        What am I doing wrong? What I mean is I want these two parameters to be
        optional and if omitted to use my default values. I don't want to drop
        them out of the where clause, I want them to revert to my default. My
        other
        issue is what do I do for a place holder. My real proc that is in
        question
        here has about a dozen parameters, I'd like to supply values at times to
        only a few.

        Hope I'm being clear here. Thanks.

        (SQL Server 2K)

        John Warner





        Yahoo! Groups Links







        Disclaimer - November 1, 2007
        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
      • John Warner
        Performance isn t really an issue here (creating reports, as long as it doesn t consume and entire lunch hour ). This gets it. Thanks. I was not using
        Message 3 of 9 , Nov 1, 2007
        • 0 Attachment
          Performance isn't really an issue here (creating reports, as long as it
          doesn't consume and entire lunch hour <grin>). This gets it. Thanks. I was
          not using the right calling syntax and just could not get my brain around
          named params. In a couple of the defaults I actually have to have more then
          null or the wild card, that was why I really wasn't looking at coalesce or
          isnull. But you provided me with the kick in the head I needed.

          Thanks again!

          John Warner




          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
          > Sent: Thursday, November 01, 2007 10:53 AM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: RE: [SQLQueriesNoCode] Default values in a T-SQL proc ??
          >
          >
          > John,
          >
          > Here is my 'teaching' example of 'overloaded' or 'optional'
          > parameters.
          >
          > -- >>>> Demonstrates the use of optional parameters (Overloading)
          >
          > USE Northwind
          > GO
          >
          > IF EXISTS
          > ( SELECT ROUTINE_NAME
          > FROM INFORMATION_SCHEMA.ROUTINES
          > WHERE ROUTINE_NAME = 'EmployeesSELECT '
          > )
          > DROP PROCEDURE dbo.EmployeesSELECT
          > GO
          >
          > CREATE PROCEDURE dbo. EmployeesSELECT
          > ( @EmployeeID int = NULL
          > , @FirstName varchar(20) = NULL
          > , @LastName varchar(20) = NULL
          > )
          > AS
          >
          > SELECT
          > EmployeeID
          > , FirstName
          > , LastName
          > FROM Employees
          > WHERE ( EmployeeID = coalesce( @EmployeeID, EmployeeID )
          > AND FirstName = coalesce( @FirstName, FirstName )
          > AND LastName = coalesce( @LastName, LastName )
          > )
          > GO
          >
          > EXECUTE dbo.EmployeesSELECT @EmployeeID = 2
          > EXECUTE dbo.EmployeesSELECT @LastName = 'Fuller'
          > EXECUTE dbo.EmployeesSELECT @FirstName = 'Nancy'
          >
          > IF this is not quite what you need, let me know.
          >
          > (Note: this is method usually does NOT create an efficient
          > execution plan. Performance may be improved by add the [ WITH
          > RECOMPILE ] option.)
          >
          >
          > Regards,
          >
          > - Arnie Rowland, MVP (SQL Server)
          >
          > "I am a great believer in luck, and I find that the harder I
          > work, the more I have of it." - Thomas Jefferson (1743-1826)
          >
          >
          >
          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
          > Sent: Thursday, November 01, 2007 7:42 AM
          > To: Arnie
          > Subject: [SQLQueriesNoCode] Default values in a T-SQL proc ??
          >
          > I have a stored proc with several parameters. I provide
          > default values for the params. Example:
          >
          > CREATE PROC MyTest (
          > @p1 varchar(10) = '%',
          > @p2 varchar(5) = 'value' )
          > As blah blah
          > WHERE field LIKE @p1 AND field2 Like @p2
          >
          >
          > Exec MyTest 'Value', 'AnotherValue'
          >
          > Result set returned all is well.
          >
          > Exec MyTest '', 'AnotherValue'
          >
          > Error.
          >
          > What am I doing wrong? What I mean is I want these two
          > parameters to be optional and if omitted to use my default
          > values. I don't want to drop them out of the where clause, I
          > want them to revert to my default. My other issue is what do
          > I do for a place holder. My real proc that is in question
          > here has about a dozen parameters, I'd like to supply values
          > at times to only a few.
          >
          > Hope I'm being clear here. Thanks.
          >
          > (SQL Server 2K)
          >
          > John Warner
          >
          >
        • Dinesh Agrawal
          hi, it is working fine at my end. I am using sql 2000. Thanks & Regards: Dinesh K. Agrawal To: SQLQueriesNoCode@yahoogroups.comFrom: john@jwarner.comDate: Thu,
          Message 4 of 9 , Nov 1, 2007
          • 0 Attachment
            hi,

            it is working fine at my end. I am using sql 2000.

            Thanks & Regards:
            Dinesh K. Agrawal


            To: SQLQueriesNoCode@yahoogroups.comFrom: john@...: Thu, 1 Nov 2007 10:40:31 -0400Subject: [SQLQueriesNoCode] Default values in a T-SQL proc ??




            I have a stored proc with several parameters. I provide default values forthe params. Example:CREATE PROC MyTest (@p1 varchar(10) = '%',@p2 varchar(5) = 'value' )As blah blahWHERE field LIKE @p1 AND field2 Like @p2Exec MyTest 'Value', 'AnotherValue'Result set returned all is well.Exec MyTest '', 'AnotherValue'Error.What am I doing wrong? What I mean is I want these two parameters to beoptional and if omitted to use my default values. I don't want to dropthem out of the where clause, I want them to revert to my default. My otherissue is what do I do for a place holder. My real proc that is in questionhere has about a dozen parameters, I'd like to supply values at times toonly a few.Hope I'm being clear here. Thanks.(SQL Server 2K)John Warner







            [Non-text portions of this message have been removed]
          • Charles Carroll
            http://blogs.aspfriends.com/afblogs/blogs/insightdb/archive/2006/08/04/26.aspx has exactly what you want. To quote from Doug Nelson s blog entry: Here is a
            Message 5 of 9 , Nov 4, 2007
            • 0 Attachment
              http://blogs.aspfriends.com/afblogs/blogs/insightdb/archive/2006/08/04/26.aspx
              has exactly what you want.

              To quote from Doug Nelson's blog entry:
              Here is a very simple trick to allow your stored procedures to have
              optional parameters. By assigning default values to the parameters of
              the procedure you can get a lot of mileage out of a single query.
              This is particularly useful for searching based on various criteria.

              For example

              create procedure [dbo].[sp_search_books]
              @ISBN varchar(15) = null,
              @AuthorLastName varchar(50) = '',
              @Publisher varchar(50) = ''
              @Title varchar(50) = ''
              as

              select
              ISBN,
              Author_FirstName,
              Auther_LastName,
              Title,
              Publisher
              from
              BookTable
              where
              ISBN = IsNull(@ISBN, ISBN),
              and Author_FirstName like
              case @AuthorFirstName when '' then Author_FirstName
              else @AuthorFirstName +'%' end
              and Author_LastName like
              case @AuthorLastName when '' then Author_LastName
              else @AuthorLastName +'%' end
              and Publisher like
              case @Publisher when '' then Publisher
              else @Publisher + '%' end
              and Title like
              case @Title when '' then Title
              else '%' + @Title + '%' end

              This single query can search for books by any combination of the
              parameters. The conditions in the where clause are such that when the
              default value is used, the condition will always be true. So to search
              for books by a publisher starting with 'Apr' the call would look like
              the following:

              sp_search_books null,'','','Apr'

              The conditional logic contains a couple of different ways to do this
              and they are both equivalent.

              First, if the default value of a parameter is null then the IsNull
              construct of the first condition is a simple way to implement this.

              In the second example, the case statement is used to test for the
              default value and simply subsitute the field value in place of the
              parameter value, if the parameter value is something other than the
              default, then the parameter value is used instead.

              For the authors name and publisher, the condition is a "starts with"
              match. For the title the condition is a "contains" match.

              One more thing to remember, in SQL, a null = null is not a match. So
              if the data columns are allowed to have null values, we need to use
              IsNull to supply a dummy value for the match and in that case the
              syntax for the condition will be as follows:

              and IsNull(Author_FirstName,'') like
              case @AuthorFirstName when '' then IsNull(Author_FirstName,'')
              else @AuthorFirstName +'%' end

              Not everyone has a first name so we allow that field to be null. If
              the value of the author field is null, if will be convert to an empty
              string so instead of null = null which does not match, we now have
              ''='' ( 'empty string' = 'empty string' ) which does match.

              On Nov 1, 2007 9:40 AM, John Warner <john@...> wrote:
              >
              >
              >
              >
              >
              >
              > I have a stored proc with several parameters. I provide default values for
              > the params. Example:
              >
              > CREATE PROC MyTest (
              > @p1 varchar(10) = '%',
              > @p2 varchar(5) = 'value' )
              > As blah blah
              > WHERE field LIKE @p1 AND field2 Like @p2
              >
              > Exec MyTest 'Value', 'AnotherValue'
              >
              > Result set returned all is well.
              >
              > Exec MyTest '', 'AnotherValue'
              >
              > Error.
              >
              > What am I doing wrong? What I mean is I want these two parameters to be
              > optional and if omitted to use my default values. I don't want to drop
              > them out of the where clause, I want them to revert to my default. My other
              > issue is what do I do for a place holder. My real proc that is in question
              > here has about a dozen parameters, I'd like to supply values at times to
              > only a few.
              >
              > Hope I'm being clear here. Thanks.
              >
              > (SQL Server 2K)
              >
              > John Warner
              >
              >
            • monojit aich
              I can give u an example ... create proc aa ( @a varchar(100)= % , @b varchar(10)= tt ) as select * from sysobjects where name like @a or name like @b here
              Message 6 of 9 , Nov 5, 2007
              • 0 Attachment
                I can give u an example ...

                create proc aa
                (
                @a varchar(100)='%',
                @b varchar(10)='tt'
                )
                as
                select * from sysobjects
                where name like @a or name like @b


                here both the input parameters are optional ,there are several ways to execute..

                exec aa '%sys%','abc'
                exec aa '','23'
                exec aa -- both take default
                exec aa @a='%sys%' --2nd parameter take default
                exec aa @b='%sys%' --1st parameter take default
                exec aa '%sys%' --2nd parameter take default


                I didnt find any wrong on calling convention as u mentioned Exec MyTest '', 'AnotherValue'



                ----- Original Message ----
                From: Charles Carroll <911@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Monday, November 5, 2007 4:28:26 AM
                Subject: Re: [SQLQueriesNoCode] Default values in a T-SQL proc ??

                http://blogs. aspfriends. com/afblogs/ blogs/insightdb/ archive/2006/ 08/04/26. aspx
                has exactly what you want.

                To quote from Doug Nelson's blog entry:
                Here is a very simple trick to allow your stored procedures to have
                optional parameters. By assigning default values to the parameters of
                the procedure you can get a lot of mileage out of a single query.
                This is particularly useful for searching based on various criteria.

                For example

                create procedure [dbo].[sp_search_ books]
                @ISBN varchar(15) = null,
                @AuthorLastName varchar(50) = '',
                @Publisher varchar(50) = ''
                @Title varchar(50) = ''
                as

                select
                ISBN,
                Author_FirstName,
                Auther_LastName,
                Title,
                Publisher
                from
                BookTable
                where
                ISBN = IsNull(@ISBN, ISBN),
                and Author_FirstName like
                case @AuthorFirstName when '' then Author_FirstName
                else @AuthorFirstName +'%' end
                and Author_LastName like
                case @AuthorLastName when '' then Author_LastName
                else @AuthorLastName +'%' end
                and Publisher like
                case @Publisher when '' then Publisher
                else @Publisher + '%' end
                and Title like
                case @Title when '' then Title
                else '%' + @Title + '%' end

                This single query can search for books by any combination of the
                parameters. The conditions in the where clause are such that when the
                default value is used, the condition will always be true. So to search
                for books by a publisher starting with 'Apr' the call would look like
                the following:

                sp_search_books null,'','',' Apr'

                The conditional logic contains a couple of different ways to do this
                and they are both equivalent.

                First, if the default value of a parameter is null then the IsNull
                construct of the first condition is a simple way to implement this.

                In the second example, the case statement is used to test for the
                default value and simply subsitute the field value in place of the
                parameter value, if the parameter value is something other than the
                default, then the parameter value is used instead.

                For the authors name and publisher, the condition is a "starts with"
                match. For the title the condition is a "contains" match.

                One more thing to remember, in SQL, a null = null is not a match. So
                if the data columns are allowed to have null values, we need to use
                IsNull to supply a dummy value for the match and in that case the
                syntax for the condition will be as follows:

                and IsNull(Author_ FirstName, '') like
                case @AuthorFirstName when '' then IsNull(Author_ FirstName, '')
                else @AuthorFirstName +'%' end

                Not everyone has a first name so we allow that field to be null. If
                the value of the author field is null, if will be convert to an empty
                string so instead of null = null which does not match, we now have
                ''='' ( 'empty string' = 'empty string' ) which does match.

                On Nov 1, 2007 9:40 AM, John Warner <john@jwarner. com> wrote:
                >
                >
                >
                >
                >
                >
                > I have a stored proc with several parameters. I provide default values for
                > the params. Example:
                >
                > CREATE PROC MyTest (
                > @p1 varchar(10) = '%',
                > @p2 varchar(5) = 'value' )
                > As blah blah
                > WHERE field LIKE @p1 AND field2 Like @p2
                >
                > Exec MyTest 'Value', 'AnotherValue'
                >
                > Result set returned all is well.
                >
                > Exec MyTest '', 'AnotherValue'
                >
                > Error.
                >
                > What am I doing wrong? What I mean is I want these two parameters to be
                > optional and if omitted to use my default values. I don't want to drop
                > them out of the where clause, I want them to revert to my default. My other
                > issue is what do I do for a place holder. My real proc that is in question
                > here has about a dozen parameters, I'd like to supply values at times to
                > only a few.
                >
                > Hope I'm being clear here. Thanks.
                >
                > (SQL Server 2K)
                >
                > John Warner
                >
                >



                __________________________________________________
                Do You Yahoo!?
                Tired of spam? Yahoo! Mail has the best spam protection around
                http://mail.yahoo.com

                [Non-text portions of this message have been removed]
              Your message has been successfully submitted and would be delivered to recipients shortly.