Post reply

Is there a SET based alternative to using CURSOR for adding numbers of user defined and localized error message with sp_addmessage?

  • February 25, 2012 at 2:52 am

    #257478

    Hi all. I've got a following situation:

    Temporary table #Temptable (msg_id, lang_id, severity, islogged, msg_text) with sample data like

    50001,1030,16,0,'Just some error text'

    50001,1033,.......

    50001,1035,.......

    50002,1030,.......

    50002,1033,.......

    50002,1035,.......

    and so on.

    To add the data to the system catalog I am using the sp_addmessage stored procedure (please notice this is a system stored procedure)

    To achieve that I used the cursor which gets the data from temporary table for each row and passes them to the sp_addmessage procedure.

    You may already assume that this is a very slow process, for example it takes like 12 seconds to add miserable 200 rows.

    My questionis if there is any SET based alternative to cursor in this situation?

  • February 25, 2012 at 8:35 am

    #1451701

    doesn't look like it;

    if you look at sp_addmessage via sp_helptext sp_addmessage,

    you'll see tat besides a boatload of validation, it still calls an internal proc proc a bunch of times for each parameter of the message being added.

    other than a cursor, or generating the stack of commands from your table and executing them, there's no easy way, but it would still be a one time thing, right?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 13, 2012 at 2:07 pm

    #1458828

    I automate this kind of admin work all the time. It's code generating code, but it's not a cursor and performs quite well up the point where db admin tasks typically need to go. Something like this should work fine:

    IF OBJECT_ID(N'tempdb..#Temptable') IS NOT NULL

    DROP TABLE #Temptable;

    GO

    CREATE TABLE #Temptable

    (

    msg_id INT,

    lang_id INT,

    severity INT,

    islogged BIT,

    msg_text NVARCHAR(2048)

    );

    GO

    INSERT INTO #Temptable

    (

    msg_id,

    lang_id,

    severity,

    islogged,

    msg_text

    )

    SELECT 50001,

    1030,

    16,

    0,

    'Just some error text'

    UNION ALL

    SELECT 50002,

    1030,

    16,

    0,

    'Just some error text'

    --etc.

    GO

    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N''

    SELECT @sql = @sql + 'EXEC sys.sp_addmessage

    @msgnum = ' + CAST(msg_id AS NVARCHAR(10)) + ',

    @severity = ' + CAST(severity AS NVARCHAR(10)) + ',

    @msgtext = N''' + msg_text + ''',

    @lang = ' + CAST(lang_id AS NVARCHAR(10)) + ',

    @with_log = ' + CAST(islogged AS NCHAR(1)) + ';

    '

    FROM #Temptable

    SELECT @sql

    --EXEC(@sql);

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 3 (of 3 total)

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