Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    220

    Unanswered: OUTPUT Params with Stored Procedures

    Howdy

    This has been driving me nuts....I have tried every combination of input & output params & no joy.

    All I want to do is get the local time on a server & push it out of a sproc as an output parameter.

    My code is :
    ---------------------------------------------------------------------------------
    CREATE PROCEDURE sp__GetTheTime @TheTime varchar(20) OUTPUT

    set @TheTime = ( getdate() )
    GO
    ----------------------------------------------------------------------------------
    then I call it :

    exec sp_GetTheTime @TheTime OUTPUT

    (1) Can I do this is does it have to be more complex?
    (2) Can I use a sproc to produce output only?
    (3) Am I using wrong datatype for the output parameter?

    Any help welcome

    Cheers,

    SG.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    the procedure remains the same ---
    ALTER PROCEDURE sp_GetTheTime @TheTime varchar(20) OUTPUT as
    select @TheTime = (getdate())
    GO


    to get the output variable use : -->

    declare @thetime varchar(20)
    exec sp_GetTheTime @thetime output
    select @thetime

    will work

  3. #3
    Join Date
    Jun 2003
    Location
    İstanbul
    Posts
    19

    Re: OUTPUT Params with Stored Procedures

    I hope I haven't misunderstood the problem but I have tried this and it works well.

    CREATE PROCEDURE sp__GetTheTime @TheTime varchar(20) OUTPUT
    AS
    SET @TheTime = getdate()
    GO


    ---------------------------------------------
    DECLARE @Time VARCHAR(20)
    exec sp__GetTheTime @TheTime=@Time OUTPUT
    go
    Kezban

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy,

    Thanks for your help. Passing parameters seems like pulling teeth.

    Can you help me one last step : I want to generate an output from one stored procedure, then pass that output value to another stored procedure which will then process that passed value.

    The output generating sproc is :

    CREATE PROCEDURE sp__GetDaTime @OutputTime varchar(40)
    OUTPUT
    AS
    SET @OutputTime = getdate()
    GO

    This bit is now fine.

    What I want to do is execute ( or call?) the next sproc that will take the output parameter from the first sproc & use it as an input parameter.....

    For the 2nd sproc I used :

    CREATE PROCEDURE sp__ReadDaTime @InputTime varchar(40)
    AS
    declare @Showtime varchar(40)
    SET @ShowTime = @InputTime
    print '@ShowTime = '+@Showtime
    GO

    which accepts ( hopefully ) @OutputTime from the first sproc and hopefully displays the time.

    Then I tried to run them together :

    exec sp__GetDaTime @OutputTime OUTPUT
    exec sp__ShowDaTime @TheTime varchar(40)

    but it keeps giving errors like 'Must declare the variable '@OutputTime''

    Do I get the sproc that accepts the output param to call sp__GetDaTime or the other way around? I am utterly confused on how to do the 2nd part ( lots of swearing...;-)) so any help welcome as it should be straight forward....annoying++!! BOL isnt much use & a simple explanation would be very useful....they dont seem to exist!! (Annoyance ++ )

    Thanks, this will SAVE my sanity!

    SG.
    Last edited by sqlguy7777; 08-14-03 at 23:34.

  5. #5
    Join Date
    Jun 2003
    Location
    İstanbul
    Posts
    19
    I think the code given below will do the task you want.

    ----

    CREATE PROCEDURE sp__GetDaTime @OutputTime varchar(40)
    OUTPUT
    AS
    SET @OutputTime = getdate()
    GO
    -----------------------------------------------------------------
    CREATE PROCEDURE sp__ReadDaTime @InputTime varchar(40)
    AS
    declare @Showtime varchar(40)
    SET @ShowTime = @InputTime
    print '@ShowTime = '+@Showtime
    GO
    -----------------------------------------------------------------
    DECLARE @InOutTime VARCHAR(40)
    exec sp__GetDaTime @InOutTime OUTPUT
    exec sp__ReadDaTime @InOutTime
    Kezban

Posting Permissions

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