Professional Web Applications Themes

Use of GETDATE() in Stored procedure logic - Microsoft SQL / MS SQL Server

Hi I have a customer who uses something like: .....OR CALCDUEDATE < GetDate() + 7..... in a sproc. I think I have read something about functions that doesn't return the same output using the same input (they have a name I cannot remember) and that they should be avoided in logic because of problems pre-compiling the stored procedure. Would it be a better idea just to make a parameter to the sproc e.g.: SearchDate, and then let the application do the date retrieval and adding of 7 days and then just cal the sproc with the result of this and ...

  1. #1

    Default Use of GETDATE() in Stored procedure logic

    Hi

    I have a customer who uses something like:

    .....OR CALCDUEDATE < GetDate() + 7.....

    in a sproc.

    I think I have read something about functions that doesn't
    return the same output using the same input (they have a
    name I cannot remember) and that they should be avoided in
    logic because of problems pre-compiling the stored
    procedure.

    Would it be a better idea just to make a parameter to the
    sproc e.g.: SearchDate, and then let the application do
    the date retrieval and adding of 7 days and then just cal
    the sproc with the result of this and the write:

    .....OR CALCDUEDATE < SearchDate ..... instead

    ??

    Yours truly


    Jakob Persson
    Jakob Guest

  2. #2

    Default Re: Use of GETDATE() in Stored procedure logic

    The main argument against using the functions is it puts a bit of business
    rules in the data layer, which is not a great idea in many, if not most
    cases. GETDATE() is not as big a problem, to me, but there are certainly
    apps where you would want to avoid this. On the other hand, it is easier to
    recompile a sproc than a business component, so the argument could be
    reversed.

    It would really depend on your application and its architecture.

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA
    Author: ADO.NET and XML: ASP.NET on the Edge

    ************************************************** **************************
    ****
    Think Outside the Box!
    ************************************************** **************************
    ****
    "Jakob Persson" <dk> wrote in message
    news:0f0f01c36731$338ef090$gbl... 


    Cowboy Guest

  3. #3

    Default Use of GETDATE() in Stored procedure logic

    Deterministic.
    It refers to functions not stored procs. You cannot use
    getdate() in a function.
    In an SP use of getdate() is fine - depending on your
    design, whether it is a good idea or not really depends on
    your architecture.
     
    doesn't 
    in 
    Nigel Guest

Similar Threads

  1. is it a bug in Stored Procedure?
    By Rickey in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 16th, 01:47 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