Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    60

    Unanswered: What's wrong with this stored procedure

    When I try to create the stored procedure below, I receive the following error. Can someone please let me know what is wrong with the code? The problem seems to be with the default parameter of getdate(). This is because when I comment the default parameter, it works fine. Please note that I need the @BeginDate parameter to be varchar(10). I cannot have it as datetime.

    Msg 156, Level 15, State 1, Procedure test, Line 4
    Incorrect syntax near the keyword 'convert'.
    Msg 137, Level 15, State 2, Procedure test, Line 8
    Must declare the scalar variable "@BeginDate".


    /* Stored Procedure*/
    Create PROCEDURE test --test
    @BeginDate varchar(10) = convert(varchar(10),getdate(),101)
    as
    select convert(datetime,@BeginDate)



    Thanks!

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Please note that I need the @BeginDate parameter to be varchar(10). I cannot have it as datetime.
    Code:
    1. Create procedure
    
    Create PROC test
    @BeginDate datetime 
    AS
    select convert(varchar(10), @BeginDate,101) as Begin_Date
    GO
    
    2. Execute it with specified date.
    
    EXEC test '01/10/2005'
    I didn't get what exactly you want to perform.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Aug 2003
    Posts
    60
    I mentioned in the issue that I cannot have the parameter as datetime. It is getting used in a report where all the date parameters need to be in varchar data type.

    Thanks

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by sajmera
    I mentioned in the issue that I cannot have the parameter as datetime. It is getting used in a report where all the date parameters need to be in varchar data type.
    You meant you want to convert datetime data to varchar datatype..?

    Have you tried VIEW for reports..? It's good option for reports.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by sajmera
    I mentioned in the issue that I cannot have the parameter as datetime. It is getting used in a report where all the date parameters need to be in varchar data type.

    Thanks
    Code:
    Create PROC test
    @BeginDate varchar(20)
    AS
    Declare @BegindateVar Datetime
    set @BegindateVar=convert(datetime,@Begindate)
    
    select convert(Varchar(20), @BeginDateVar,101) as Begin_Date
    GO
    
    EXEC test '01/10/2005'
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sajmera, the problems with your procedure are:
    1). You are not enclosing your parameter declaration in parenthesis.
    2). You appear to be trying to assign a value to the parameter while you are defining it.
    3). Your procedure is probably superfluous, because SQL Server implicits converts datetime and varchar datatypes.

    You need to review the Books Online syntax for stored procedures and parameter declarations. Then you need to clearly define the purpose of this code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2005
    Posts
    45
    If you are trying to extend the procedure so that it has a default value for your date parameter, you can try the following:

    Code:
    Create PROCEDURE test --test
    @BeginDate varchar(10) = NULL
    as 
    if @BeginDate is null
    	set @BeginDate = convert(varchar(10),getdate(),101)
    select convert(datetime,@BeginDate)
    
    exec test '1/1/71'
    exec test
    This worked for me. Hope this helps.
    Cat

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    minor point, but just as an fyi:

    Quote Originally Posted by catdavis67
    if @BeginDate is null
    set @BeginDate = convert(varchar(10),getdate(),101)
    can be more clearly (imo) written in a single statement like this:

    Code:
    set @BeginDate = coalesce(@BeginDate, convert(varchar(10),getdate(),101))
    It's particularly useful when you have more args to pick from, because coalesce can take any number of args. looks cleaner than using if blocks or isnull.

  9. #9
    Join Date
    Jun 2005
    Posts
    45
    Quote Originally Posted by jezemine
    minor point, but just as an fyi:



    can be more clearly (imo) written in a single statement like this:

    Code:
    set @BeginDate = coalesce(@BeginDate, convert(varchar(10),getdate(),101))
    It's particularly useful when you have more args to pick from, because coalesce can take any number of args. looks cleaner than using if blocks or isnull.
    You learn something new everyday! Thanks a bunch for that, I'd never heard of COALESCE before.

    Cat

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya know, when I see a post title like that, my first reaction is always:

    PEBKAC
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •