Post reply

can I see the output value after executing sproc in ssms

  • September 27, 2011 at 3:38 pm

    #245598

    I'm using management studio to excute a stored procedure.

    The stored procedure have both input and output parameters.

    I execute in SSMS by calling the sproc,

    DECLARE @rc int

    DECLARE @year int

    DECLARE @JobType varchar(20)

    DECLARE @JobNm varchar(40)

    DECLARE @CreateDt datetime2(7)

    DECLARE @createdby varchar(128)

    DECLARE @JobID int

    -- Set parameter values here.

    SET @Year=2011

    SET @JobType='Submit'

    SET @JobNm='Transaction'

    SET @createdby=SYSTEM_USER

    EXECUTE @rc = [dbo].[sps_Job_Begin]

    @Year

    ,@JobType

    ,@JobNm

    ,@CreateDt OUTPUT

    ,@CreatedBy

    ,@JobID OUTPUT

    GO

    I suppose I can get the createdDt value and also JobID value in the result pane of ssms because they are the output parameter, but what I got is only Commands completed successfully.

    Is that supposed correct?

    Thanks

  • September 27, 2011 at 3:45 pm

    #1387471

    You have to add an extra select to display the variables.

  • September 27, 2011 at 5:49 pm

    #1387494

    You could also issue a print statement with each param you want to see.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 27, 2011 at 5:52 pm

    #1387495

    so where does the output parameter goes to?

    Should I use the select or print in the stored procedure or after the Exec mysproc...

    statement

  • September 27, 2011 at 7:39 pm

    #1387504

    Select or print is just a personal preference to you on SSMS. It's going to be different when you call it form the application.

    In ssms you'd use print or select after the exec statement.

  • September 28, 2011 at 9:08 am

    #1387841

    Thanks!!

  • September 29, 2011 at 2:21 pm

    #1388630

    I think this is what you are talking about, being able to print the values that are returned:

    DECLARE @st int

    DECLARE @rn int

    DECLARE @em varchar(200)

    EXEC xp_usp_Create_Applicant_Rec 'Jeff','H','Smith',@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT

    SELECT 'xp_usp_Create_Applicant_Rec' as SPROC, @st as Status, @em as ErrorMessage, @rn as RecNum

  • October 4, 2011 at 1:20 pm

    #1390337

    Thank you, it's very helpful

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply