Professional Web Applications Themes

problem with time_stamp - Microsoft SQL / MS SQL Server

Hi, I have a table with constraint on a column time_stamp, default value is getdate(). I'm trying to insert a row using store procedure, exec Proc1 time_stamp=getdate() -------------------------------- Insertion fail, I got: Incorrect syntax near '('. How come I can't pass getdate()?...

  1. #1

    Default problem with time_stamp

    Hi, I have a table with constraint on a column time_stamp, default value
    is getdate().


    I'm trying to insert a row using store procedure,

    exec Proc1 time_stamp=getdate()

    --------------------------------
    Insertion fail, I got:

    Incorrect syntax near '('.

    How come I can't pass getdate()?








    Mike Guest

  2. #2

    Default Re: problem with time_stamp

    Mike wrote: 

    You can't use expressions as parameters: either variables or literals only.
    declare parm datetime
    set parm=getdate()
    exec Proc1 time_stamp=parm

    HTH,
    Bob Barrows


    Bob Guest

  3. #3

    Default Re: problem with time_stamp

    If the parameter time_stamp in the stored procedure has a default value of
    getdate(), you do not have to pass the parameter when executing the
    procedure. Just execute it this way:
    exec Proc1
    In the case that you do not want to use the default value:
    exec Proc1 time_stamp='2003/01/01'

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


    raydan Guest

  4. #4

    Default Re: problem with time_stamp

    so what about if I do an update on time_stamp?

    I still need to pass in getdate() in order to modify it, right?



    "raydan" <nospamcom> wrote in message
    news:phx.gbl... 
    of [/ref]
    value 
    >
    >[/ref]


    Mike Guest

  5. #5

    Default Re: problem with time_stamp

    I just reread your question and my answer and boy... am I messed up.
    No, you cannot give a default value of GetDate() to a parameter in a SP
    because it is non-deterministic.
    Here's what you can do:
    Create Procedure proc1
    time_stamp DateTime=NULL
    as
    insert into tableName values (isnull(time_stamp, getdate())

    .... and call the SP using:
    exec proc1
    or
    exec proc1 time_stamp = '2003/01/01'

    The same method can be used when you update a table.

    But normally this would be used only if you want the power to override the
    stamp.
    If not:
    Create Procedure proc1 as
    insert into tableName values (getdate())

    .... and call the SP using:
    exec proc1

    Or even simpler, give the column in the table a default value of getdate()
    (this will not work for an update, you could use a trigger in this case or
    include the logic in the SP).

    "Mike" <com> wrote in message
    news:phx.gbl... 
    > of [/ref]
    > value 
    > >
    > >[/ref]
    >
    >[/ref]


    raydan Guest

Similar Threads

  1. contribute problem - access denied file may not existpermission problem
    By Al1973 in forum Macromedia Contribute Connection Administrtion
    Replies: 6
    Last Post: September 17th, 04:16 PM
  2. Replies: 2
    Last Post: September 30th, 12:13 PM
  3. Replies: 0
    Last Post: August 2nd, 03:03 PM
  4. Replies: 2
    Last Post: July 17th, 07:27 AM

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