Professional Web Applications Themes

Exec procedure and date parameters - Microsoft SQL / MS SQL Server

How can I successfully pass a date parameter to an exec procedure? I have the following code. declare tablename nvarchar(30) declare d_startDateTime datetime declare d_endDateTime datetime set d_endDateTime = dbo.f_GetDateTime(getdate (),'143059') --cutoff date and time set d_startDateTime = d_endDateTime - 1 set tablename = 't_dup_bill_req' exec ('select * from ' + tablename + ' where d_date_provided between ' + d_StartDateTime + ' and ' + d_EndDateTime ) When I execute the code, I get the following error message: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '15'. If I take the WHERE part off ...

  1. #1

    Default Exec procedure and date parameters

    How can I successfully pass a date parameter to an exec
    procedure? I have the following code.

    declare tablename nvarchar(30)
    declare d_startDateTime datetime
    declare d_endDateTime datetime
    set d_endDateTime = dbo.f_GetDateTime(getdate
    (),'143059') --cutoff date and time
    set d_startDateTime = d_endDateTime - 1
    set tablename = 't_dup_bill_req'

    exec ('select * from ' + tablename + ' where
    d_date_provided between ' + d_StartDateTime + ' and ' +
    d_EndDateTime )

    When I execute the code, I get the following error
    message:

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '15'.

    If I take the WHERE part off as follows: exec ('select *
    from ' + tablename) it works fine.

    If I enter the table name in the query as follows as
    shown below, everything works as well.

    select * from t_dup_bill_req where d_date_provided
    between d_StartDateTime and d_EndDateTime

    What am I doing wrong?

    Thanks

    Emmanuel Guest

  2. #2

    Default Re: Exec procedure and date parameters

    The problem seems to be with f_GetDateTime. It cannot take GetDate() as a
    parameter; create a local variable & pass in the date value as & input
    parameter.

    Also, why do you want to do such coding? Use proper table names instead of
    passing them as variables. For a better perspective refer to:
    http://www.algonet.se/~sommar/dynamic_sql.html

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Exec procedure and date parameters

    I have the same issue :-)

    Below SQL works

    declare startdate datetime, enddate datetime, sql varchar(1000)
    select startdate = '06/01/2003'
    select enddate = '06/03/2003'
    select top 10 * from mytable where mydate between startdate and enddate

    But , below one error out with message
    Server: Msg 241, Level 16, State 1, Line 4
    Syntax error converting datetime from character string.

    declare startdate datetime, enddate datetime, sql varchar(1000)
    select startdate = '06/01/2003'
    select enddate = '06/03/2003'
    select sql = 'select * from mytable where mydate between ' + startdate +
    ' AND ' + enddate
    execute sql

    Here startdate and enddate are parameters and used inside a SP.
    I have to use dynamic SQL for my logic and don't want to use CONVERT
    function.
    How to make this dynamic SQL work ??

    Thx
    Sh


    P.S:- Got suggestions to use double quotes on startdate and enddate , but
    NO LUCK





    "Anith Sen" <com> wrote in message
    news:phx.gbl... 


    Shamim Guest

  4. #4

    Default Re: Exec procedure and date parameters

    You need to have parantheses around your sql, like:
    EXECUTE (sql)

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  5. #5

    Default Re: Exec procedure and date parameters

    > select startdate = '06/01/2003' 

    Why are you using ambiguous date formats? I strongly recommend YYYYMMDD
    format.

     


    How about

    SET sql = 'SELECT * from mytable where mydate between ''' + startdate +
    ''' AND ''' + enddate + ''''
    EXEC(sql)

    Or, without the seemingly unnecessary dynamic SQL,

    SELECT * FROM mytable WHERE mydate BETWEEN startdate AND enddate


    Aaron Guest

  6. #6

    Default Re: Exec procedure and date parameters

    The f_GetDateTime is called before executing the exec
    procedure. I am only passing the result of f_GetDateTime
    to the exec procedure. If I manually put the date in, I
    still get the same error message.
     
    varchar(1000) 
    startdate and enddate 
    varchar(1000) 
    between ' + startdate + 
    inside a SP. 
    use CONVERT 
    and enddate , but [/ref]
    take GetDate() as a [/ref]
    value as & input [/ref]
    table names instead of [/ref]
    refer to: 
    >
    >
    >.
    >[/ref]
    Emmanuel Guest

  7. #7

    Default Re: Exec procedure and date parameters

    I am also using the parameters because the table name and
    dates changes and I would like to avoid writing different
    procedure to handle each table. I will check out the link
    you suggested.

    Thanks

     
    take GetDate() as a 
    value as & input 
    table names instead of 
    refer to: 
    Emmanuel Guest

  8. #8

    Default Re: Exec procedure and date parameters


    I think you need to quote the date values in the exec statement. So you get:

    exec ('select * from ' + tablename + ' where d_date_provided between "' +
    d_StartDateTime + '" and "' + d_EndDateTime +'"' )

    -Brian


    "Emmanuel" <com> wrote in message
    news:003401c34bb6$0a5a7fa0$gbl... 


    brian Guest

  9. #9

    Default Re: Exec procedure and date parameters

    It is because the variables are declared as DATETIME & you are trying to
    concatenate it with a VARCHAR string. You can either declare the variables
    as VARCHAR or do:

    SET sql = 'SELECT * FROM tbl WHERE dtcol
    BETWEEN ''' + CONVERT( VARCHAR, d_StartDateTime, 112) +
    ''' AND ''' +
    CONVERT( VARCHAR, d_EndDateTime , 112) + ''''
    EXEC (sql) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  10. #10

    Default Re: Exec procedure and date parameters

    Thanks Anith. Quoting the dates and converting them to
    VARCHAR did the trick.

    Thanks again.

     
    you are trying to 
    declare the variables 
    d_StartDateTime, 112) + 
    + '''' 
    Emmanuel Guest

  11. #11

    Default Re: Exec procedure and date parameters

    Good to know it worked!!!
    But just reminding, being used CONVERT function , it wont use INDEX for
    search criteria.

    Shamim

    "Emmanuel" <com> wrote in message
    news:023b01c34bc4$15aeb430$gbl... 
    > you are trying to 
    > declare the variables 
    > d_StartDateTime, 112) + 
    > + '''' [/ref]


    Shamim Guest

  12. #12

    Default Re: Exec procedure and date parameters

    Shamim (com) writes: 
    >> d_StartDateTime, 112) + 
    >> + '''' [/ref][/ref]

    You are mixning apples and oranges. Anith gave you a link to my article,
    http://www.algonet.se/~sommar/dynamic_sql.html. Did you read it? Yes, I
    know it is a bit long, but seriously, you can improve your writing of
    dynamic SQL by picking up some ideas there.

    One very simple idea is to include a PRINT sql. Then you will see
    that the convert is no longer there. The convert is only there when
    you build the string.

    But if you use sp_executesql instead, you don't need any convert at
    all.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  13. #13

    Default Re: Exec procedure and date parameters

    Emmanuel (com) writes: 

    As you will find when you read the article, writing one procedure per
    table is not a bad idea. Possibly it is bad a idea to have more than
    one table.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Optional Parameters ASP to SQL stored procedure
    By ross in forum ASP Database
    Replies: 0
    Last Post: May 25th, 02:53 PM
  2. Stored Procedure Parameters (text)
    By Chopper in forum ASP
    Replies: 10
    Last Post: September 22nd, 10:14 PM
  3. #22403 [Com]: PHP crashes when executing a sql procedure without parameters
    By daniel dot beet at accuratesoftware dot com in forum PHP Development
    Replies: 0
    Last Post: July 15th, 12:29 PM
  4. Need more help on how to pass parameters from stored procedure into trigger
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 1st, 09:51 PM
  5. procedure parameters
    By s Lehaire in forum Oracle Server
    Replies: 2
    Last Post: January 10th, 05:45 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139