SQL Server Performance

varible not declared...

Discussion in 'General DBA Questions' started by tymbuktu, Sep 13, 2005.

  1. tymbuktu New Member

    please can someone help me i am getting the follwing message which i belive are follow up message...

    Server: Msg 137, Level 15, State 2, Line 9
    Must declare the variable '@appid'.
    Server: Msg 137, Level 15, State 1, Line 16
    Must declare the variable '@appid'.
    Server: Msg 137, Level 15, State 1, Line 20
    Must declare the variable '@appid'.
    Server: Msg 137, Level 15, State 1, Line 24
    Must declare the variable '@appid'.

    this is when i execute the following...

    DECLARE @Hours int
    DECLARE @Minutes int
    DECLARE @Seconds int
    DECLARE @TotalTime varchar(250)
    DECLARE @DateRan datetime

    SELECT @DateRan = (SELECT DateTimeOfCall
    FROM application_tbl
    WHERE appid = @appid
    )

    SET DateFormat mdy

    SELECT @Hours = datediff(hh,TimeBegin,GETDATE())
    FROM application_tbl
    WHERE appid = @appid

    SET @Minutes = (SELECT datediff(ss,TimeBegin,GETDATE())
    FROM application_tbl
    WHERE appid = @appid)

    SET @Seconds = (SELECT datediff(ss,TimeBegin,GETDATE())
    FROM application_tbl
    WHERE appid = @appid)

    SET @Minutes = (@Minutes / 60)
    SET @Seconds = (@Seconds % 60)

    SELECT @TotalTime = 'The Time Difference from today for this call is '+ CAST(ABS(@Hours) AS Varchar(2)) + ' hours ' + CAST(ABS(@Minutes) AS Varchar(2)) + ' minutes ' + CAST(ABS(@Seconds) AS Varchar(2)) + ' seconds ' + 'on ' + CAST(@DateRan AS Varchar(30)) + ' for the call that was placed on ' + CAST(@DateRan AS Varchar(30))

    my apologies about all this as i am new to SQL

    ta

    tymbuktu
  2. Akthar New Member

    Hi,

    seems that it asks you to declare the @appid, where is this variable declare?

    AKTHAR DILMOHAMUD
    65 BENARES ST
    PORT LOUIS
    MAURITIUS
  3. Madhivanan Moderator

    It is because you did not declare @appid
    Can you explain what you are trying to do?

    Madhivanan

    Failing to plan is Planning to fail
  4. tymbuktu New Member

    HI and firstly thankyou for both your replies...

    basically i am trying to determine the time between the time now() and the time that a record was entered into the system (datetimeofcall)

    initially i was attempting it on the asp page

    <%
    Dim ShortYearsDifference ' calculates the clients age and inserts into a hidden field in page for update of record
    DaysDifference = DateDiff("d", rsHelpdesk("datetimeofcall"), Now() )
    YearsDifference = DaysDifference / 365
    timeDifference = DaysDifference / 24
    ShortYearsDifference = Left(YearsDifference,2)
    %>
    <%=DaysDifference%>


    BUT i was told to do it on the Database end so i am trying it on the DB the ASP version i did i can only get the number of DAYS but not the exact day, time seconds etc etc...

    Any help will be greatful.

    regards

    Musa
  5. FrankKalis Moderator

  6. Madhivanan Moderator

    Select DateDiff(hh,Col,Getdate()) from yourTable
    See DateDiff function in BOL

    Madhivanan

    Failing to plan is Planning to fail
  7. tymbuktu New Member

    HI the asp script is the complete statement .

    Sorry Madhivanan i am sorta lost here... am i taking the ASP approach or the SQL DB approach.. therefore where do i add the select criteria you specified.

    Regards

    Musa
  8. tymbuktu New Member

    Hi Ideally i would like ti to diplay the value on the ASP page in hour format?
  9. Akthar New Member

    Musa,

    if you used your dB APPROACH, it sould work just try to define the @appid variable. Is it a user input???

    AKTHAR DILMOHAMUD
    65 BENARES ST
    PORT LOUIS
    MAURITIUS
  10. FrankKalis Moderator

  11. tymbuktu New Member

    Yeah Br.Aktar and Frank i tried doing it on the page itself buti got values such as this if i was finding out the hours between now and the time in the DB 10.2916666666667 (this was for the value 09/01/2005 15:41:39 in the datetimeofcall field.) this is not right.. is there any thing you can advise me///

    Yeah Br.Aktar i did define the variable @appID this a int that is automatically generated within the db as a new record is created via the asp page.

    Any help will be much apprieciated.

  12. Akthar New Member

    Hi ,
    see if this example help
    USE pubs
    GO
    SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
    FROM titles
    GO

    taken from BOL

    AKTHAR DILMOHAMUD
    65 BENARES ST
    PORT LOUIS
    MAURITIUS
  13. Akthar New Member

  14. FrankKalis Moderator

    Now you've got me confused. You want to find out the time difference between now and some column in your table? See if this helps you:


    DECLARE @dt DATETIME, @dt_Start DATETIME
    SELECT @dt = GETDATE(), @dt_Start = '20050913'

    SELECT
    @dt Now
    , DATEDIFF(second,@dt_Start, @dt) / 3600 Hours
    , DATEDIFF(second,@dt_Start, @dt)/ 60 % 60 Minutes
    , (DATEDIFF(second,@dt_Start, @dt) % 60) Minutes


    Now Hours Minutes Minutes
    ------------------------------------------------------ ----------- ----------- -----------
    2005-09-13 14:39:17.230 14 39 17

    (1 row(s) affected)

    To make it easier to check, I've set the start date to midnight of today. Is this going your direction?





    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  15. tymbuktu New Member

    Akthar i have tried this...

    USE helpdesk
    GO
    SELECT DATEDIFF(hour, DateTimeOfCall , getdate()) AS appid2
    FROM application_tbl
    GO

    but i get the values in SQl Analyser.. it does not put it into the actual field appID2 so that i can pull it out on the ASP page...

    i just wana real time value of the the columns... so that its viewable on the ASP page.. do you think i need to do a storeprocedure or something.....
  16. tymbuktu New Member

    Frank thanks for ur effort, i really am sorry.. i cant get that too work.. all i want is the difference bettern the current date/time and the date/time in the database field... (called DateTimeOfCall ) and for it to be displayed in HOURS on a web page... i am using ASP. Its just that this bloke on the web told me to do the whole thing within SQL DB but i dont see how that can work and be shown on the ASP page...

    ta

  17. FrankKalis Moderator

    Okay, last try before I go home for today [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @dt DATETIME, @dt_Start DATETIME<br />SELECT @dt = GETDATE(), @dt_Start = '20050913'<br /><br />DECLARE @seconds INT<br />DECLARE @hours INT<br />DECLARE @minutes INT<br />--<br />SET @seconds = DATEDIFF(second,@dt_Start, @dt)<br />SET @hours = FLOOR(@seconds/(60*60))<br />SET @minutes = FLOOR((@seconds - (@hours*3600))/60)<br />SET @seconds = FLOOR(@seconds %60)<br /><br />SELECT @dt<br />SELECT <br /> CAST(@hours AS VARCHAR(5))+<br />':' + <br />RIGHT('0' + CONVERT(VARCHAR(2), @minutes), 2)+<br />':' + <br />RIGHT('0' + CONVERT(VARCHAR(2), @seconds), 2)<br /><br /> AS Time_Elapsed<br /><br /><br />------------------------------------------------------ <br />2005-09-13 16:18:13.760<br /><br />(1 row(s) affected)<br /><br />Time_Elapsed <br />------------ <br />16:18:13<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  18. tymbuktu New Member

    Frank i get this mate...

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


    no worries will speak to ou 2morw

    ta
  19. FrankKalis Moderator

    Yes, probably we have more luck tomorrow. Though I'm still uncertain how your result should look like. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  20. Akthar New Member

    hi,

    you managed to do it tymbuktu?

    AKTHAR DILMOHAMUD
    65 BENARES ST
    PORT LOUIS
    MAURITIUS

Share This Page