Post reply

Store Procedure...

  • July 15, 2019 at 1:59 pm

    #3663028

    Hi

    Is there anyone who can help me explaining, in real job field, in what kind of scenario I would need to use output parameters in my store procedures. And also I would like to know how data gets into tables, do we manually entered it or its gets inserted via back end file?

    Thank You!!!

  • July 15, 2019 at 4:24 pm

    #3663088

    This sounds like a set of interview questions, but here goes:

    1. I have code that needs two parameters used in the call (query, proc, etc.). I have a stored procedure that sets the values I need in these two parameters. I can use output parameters to set these values
    2. data gets into tables through INSERT or bulk load calls.

     

  • July 15, 2019 at 4:24 pm

    #3663089

    Note, this isn't likely to help you answer the questions if you don't do more research. I'm happy to help, but I'd like to see you make an effort to ask more specific questions.

  • July 19, 2019 at 9:45 am

    #3664449

    An example of a Stored Procedure where an output parameter could be used would be where the caller request a dataset be returned based on some input parameters, but at the same time want to inform the caller about an aspect of the dataset as a whole, like for example that all data in column xxx is actually only nulls. This makes it possible for the caller to set up a presentation  that doesn't use the data from this column (doesn't show the column at all) before fetching the dataset that the procedure provides.

    Another example would be a Stored Procedure that is called to update/insert data into a table. The output parameter could then provide information about what actually happened. IMO that is a more graceful way to provide feedback than have the Stored Procedure issue an error event.

    But of course the caller would need to handle the information that is fed back in the output parameter(s).

  • July 19, 2019 at 12:50 pm

    #3664503

    ns550 wrote:

    in what kind of scenario I would need to use output parameters in my store procedures.

    According only to personal experience, I've found that the usual thing is to report success or failure of the stored procedure when using some form of error trapping within the stored procedure.  There are certainly other uses but the success/failure thing appears to be the most common to me.

    ns550 wrote:

    And also I would like to know how data gets into tables, do we manually entered it or its gets inserted via back end file? Thank You!!!

    The answer here is "YES".  In most systems I've worked on, both methods are used.  The manual method is usually done through a GUI of some sort and is frequently referred to as OLTP, which stands for "OnLine Transaction Processing".  The method of loading data through a "back end file" is referred to by many names but the two most common names are "Bulk Loading" and ETL (Extract, Translate, and Load).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 23, 2019 at 1:16 pm

    #3665379

    Reasons to use output parameters in stored procedures:

    1. To return db keys generated within the procedure
    2. To return data (version id's, timestamp's, etc) which changed/updated as a result of actions which occurred within the procedure
    3. To return a success/failure metric to an API or UI client
    4. To support application logging.  Within a proc if the db throws an exception then the catch block can return information to the application/API about what happened.  The application sends user clients a default message like "Insert not successful", while the log sink records the actual sql error number, message, line #, etc...   For logging purposes we use this table-valued function within catch blocks to return the error fields (and xact_state) generated by sql:

    create function dbo.get_error_metadata()

    returns table

    as

    return

    (select

    error_number() error_number,

    error_severity() error_severity,

    error_state() error_state,

    error_line () error_line,

    error_procedure() error_procedure,

    error_message() error_message,

    xact_state() xact_state);

    go

    The result set gets serialized and sent to the API for logging.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • July 24, 2019 at 8:44 am

    #3665607

    serialized as a single varchar/nvarchar value in an output parameter?

  • July 24, 2019 at 1:31 pm

    #3665706

    Yes exactly.  Something like:

    drop proc if exists wm.api_comment_post;

    go

    create proc wm.api_comment_post

    @securitystamp uniqueidentifier,

    @cw_id bigint,

    @comment nvarchar(1024),

    @test_id bigint output,

    @response nvarchar(max) output

    as

    set nocount on;

    set xact_abort on;

    begin transaction

    begin try

    declare

    @awc_id bigint;

    insert comments(u_id, w_id, cw_id, comment, created_dt, edited_dt)

    select

    wm.func_user_id(@securitystamp),

    cw.w_id,

    cw.cw_id,

    @comment,

    sysutcdatetime(),

    sysutcdatetime()

    from

    wm.class_workouts cw

    where

    cw.cw_id=@cw_id;

    select @awc_id=cast(scope_identity() as bigint);

    select @test_id=@awc_id, @response=(select N'Ok' reply_message, @awc_id awc_id for json path, without_array_wrapper);

    commit transaction;

    end try

    begin catch

    select @test_id=cast(0 as bigint), @response=(select * from dbo.get_error_metadata() for json path, without_array_wrapper);

    if xact_state()=-1

    rollback transaction;

    end catch

    set xact_abort off;

    set nocount off;

    go

    If the test_id returns a positive number, then the response is valid and it contains the key generated in the procedure.  If the test_id returns 0, then the response contains the error meta generated by sql.

     

     

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

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

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