Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2004
    Posts
    51

    Unanswered: stored proc with getdate parameter

    trying to create SP with parameter and i want to use current date getdate() as parameter.. doesn't seem to work. do i have to use getdate in where clause?

    here my SP

    CREATE PROC report
    (@date datetime)
    SET @date = (getdate())-1
    as
    SELECT..here goes my select statement
    where (@date = mydatecolumindatebase)

    but im getting error on line 3 and 4
    ........
    Server: Msg 156, Level 15, State 1, Procedure getdatetest, Line 3
    Incorrect syntax near the keyword 'SET'.
    Server: Msg 156, Level 15, State 1, Procedure getdatetest, Line 4
    Incorrect syntax near the keyword 'as'.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Umm, a parameter is supposed to be submitted by the calling procedure.

    Also, you can't have that set statement before the "as" clause.

    If you want your procedure to default to the previous date, try this:

    CREATE PROC report
    (@date datetime = Null)
    as
    SET @date = convert(char(10), coalesce(@date, datediff(day, -1, getdate()), 120)

    SELECT..here goes my select statement
    where (@date = mydatecolumindatebase)
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jan 2004
    Posts
    51
    thanks for your reply..
    still getting error on my select statement..

    Server: Msg 156, Level 15, State 1, Procedure report, Line 5
    Incorrect syntax near the keyword 'SELECT'.

    Create_Date is the column im trying get current date result. my goal is a query that will return in alias total column total records for each person and for current date.
    my select statement looks like this..

    SELECT Count(distinct Create_Date) AS Total,

  4. #4
    Join Date
    Jan 2004
    Posts
    51
    also create_date column is not datetime format in database.. it varchar and i can't change that bc the way imports comes it..
    data's are in this format 01/02/2004 01:24:45

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Varchar will convert implicitly to datetime as long as it represents a valid date.

    You should use the ISDATE() function to validate all the dates stored in you database as strings.

    You will have to post your code if you want assistance with the error.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jan 2004
    Posts
    51
    ok,, i created this SP and works when parameter is supplied

    CREATE PROC report
    (@date datetime)
    as
    SELECT Count( DISTINCT Create_Date) AS Total,
    Submitter,
    (EMP_FRST+' '+EMP_LAST) AS Name,
    (SUP_FRST+' '+SUP_LAST) AS Supervisor,
    (MGR_FRST+' '+MGR_LAST) AS Manager

    FROM Reports.dbo.data, Webdata.dbo.Members
    WHERE (Create_Date = @date) AND Submitter = ID
    GROUP BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter
    ORDER BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter ASC
    GO
    .................
    but i want to always return current date report instead of supplying parameter..

    here new SP im working on..

    ...........................
    alter PROC report
    (@date datetime = Null)
    as
    SET @date = convert(char(10), coalesce(@date, datediff(day, -1, getdate()), 120)
    SELECT Count( DISTINCT Create_Date) AS Total,
    Submitter,
    (EMP_FRST+' '+EMP_LAST) AS Name,
    (SUP_FRST+' '+SUP_LAST) AS Supervisor,
    (MGR_FRST+' '+MGR_LAST) AS Manager

    FROM Reports.dbo.data, Webdata.dbo.Members
    WHERE (@date = Create_Date)AND Submitter = ID
    GROUP BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter
    ORDER BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter ASC
    GO
    .......
    this one return error

    Server: Msg 156, Level 15, State 1, Procedure report, Line 5
    Incorrect syntax near the keyword 'SELECT'.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you ALWAYS want to return the current date (though your code indicates previous date...) then you don't need @data as a parameter at all. Just declare it in your procedure:

    alter PROC report
    as
    declare @date datetime
    SET @date = convert(char(10),datediff(day, -1, getdate()), 120)

    SELECT Count( DISTINCT Create_Date) AS Total,
    Submitter,
    (EMP_FRST+' '+EMP_LAST) AS Name,
    (SUP_FRST+' '+SUP_LAST) AS Supervisor,
    (MGR_FRST+' '+MGR_LAST) AS Manager

    FROM Reports.dbo.data, Webdata.dbo.Members
    WHERE (@date = Create_Date)AND Submitter = ID
    GROUP BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter
    ORDER BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter ASC

    ...or do away with it entirely:

    alter PROC report
    as

    SELECT Count( DISTINCT Create_Date) AS Total,
    Submitter,
    (EMP_FRST+' '+EMP_LAST) AS Name,
    (SUP_FRST+' '+SUP_LAST) AS Supervisor,
    (MGR_FRST+' '+MGR_LAST) AS Manager

    FROM Reports.dbo.data, Webdata.dbo.Members
    WHERE (Create_Date = convert(char(10),datediff(day, -1, getdate()), 120)) AND Submitter = ID
    GROUP BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter
    ORDER BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter ASC
    GO


    You should also consider dropping the " AND Submitter = ID" from your WHERE clause and specifying a table join instead:

    FROM Reports.dbo.data inner join Webdata.dbo.Members on Submitter = ID
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jan 2004
    Posts
    51
    im sorry
    i meant previous date

  9. #9
    Join Date
    Jan 2004
    Posts
    51
    i use your sample and im getting
    Server: Msg 241, Level 16, State 1, Procedure report, Line 4
    Syntax error converting datetime from character string.
    Last edited by densma; 04-07-04 at 15:03.

  10. #10
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    by looking at your procedure i kinda get the impression that you are trying to get the date minus one day.
    if that is what you want maybe you could rewrite your query.

    i like this one you can run it on northwind

    CREATE PROC report
    @date datetime
    as
    SELECT * from orders where (orderdate = (@date) -1 )

    exec report '5-SEP-1996'

    this should bring up orderid 10297 with an orderdate of '4-sep-1996'

    hope this is what you want
    Last edited by Ruprect; 04-07-04 at 15:11.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Somewhere in your data there are invalid date strings. You can avoid them using the ISDATE() function in your statement:

    alter PROC report
    as

    SELECT Count( DISTINCT Create_Date) AS Total,
    Submitter,
    (EMP_FRST+' '+EMP_LAST) AS Name,
    (SUP_FRST+' '+SUP_LAST) AS Supervisor,
    (MGR_FRST+' '+MGR_LAST) AS Manager

    FROM Reports.dbo.data, Webdata.dbo.Members
    WHERE (Create_Date = convert(char(10),datediff(day, -1, getdate()), 120)) AND Submitter = ID
    and IsDate(Create_Date) = 1
    GROUP BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter
    ORDER BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter ASC
    GO

    ...but you need to locate them and fix them. It is almost never a good idea to store date values as strings. The strings should be validated during the import and then converted to datetime.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Jan 2004
    Posts
    51
    This last one ran without error but returns nothing (0 row(s) affected)
    is that for previous date? b/c there's no data for today yet.

    string in date values suck..this has been driving me nut

  13. #13
    Join Date
    Jan 2004
    Posts
    51
    hey Ruprect
    i want to exe sp without parameter to return result for previous date.
    date column in database is varchat

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My bad!

    You should be using the DATEADD function rather than DATEDIFF:

    alter PROC report
    as

    SELECT Count( DISTINCT Create_Date) AS Total,
    Submitter,
    (EMP_FRST+' '+EMP_LAST) AS Name,
    (SUP_FRST+' '+SUP_LAST) AS Supervisor,
    (MGR_FRST+' '+MGR_LAST) AS Manager

    FROM Reports.dbo.data, Webdata.dbo.Members
    WHERE (Create_Date = convert(char(10),dateadd(day, -1, getdate()), 120)) AND Submitter = ID
    and IsDate(Create_Date) = 1
    GROUP BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter
    ORDER BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter ASC
    GO
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Lindman does dates...

Posting Permissions

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