Post reply

Variable declared for inserting the records is setting to empty from second execution onwards.

  • July 9, 2015 at 8:59 am

    #1811349

    Not really having the time to delve all that deep into your procedure, I can only guess what the objective is. However, one thing that folks often trip over, is expecting a temp table to still exist after a stored procedure executes. This is not going to happen unless it's a GLOBAL temp table (##). Also, it appears your first cursor might not need to be a cursor at all. That variable will end up with the value from the last record in that query. If there is only just 1 record that will ever come back from the query, then you are better off with SELECT @variable_name = field_name FROM whatevertable WHERE whatevercondition...

    I don't know if that helps, but if you can elaborate on what the objective here is, it will be much easier for us to help, as just reading through your proc is going to be a bit of a slog for anyone... And we don't even know what's supposed to happen...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • July 9, 2015 at 1:18 pm

    #1811438

    This stored procedure is full of problems. The RBAR (Row By Agonizing Row) is extreme and I'm having some problems figuring out what you're trying to do.

    You need to find which variable is staying as NULL which might generate the problem. With more help such as sample data and DDL for involved tables and functions, we could improve this procedure to be easier to understand and maintain.

    The first 3 cursors that you declare are absolutely not needed as well as some other code.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 10, 2015 at 8:46 am

    #1811634

    narengc (7/9/2015)

    I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. Please see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.

    Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.

    select @L_STMT= 'INSERT INTO '+ @l_table_name + '(' + LTRIM(RTRIM((substring (@L_INS_STMT,2,len(@L_INS_STMT))))) + ') VALUES (' + LTRIM(RTRIM((substring (@L_INS_STMT1,2,len(@L_INS_STMT1))))) +')';

    I think you're on the right track. You've narrowed down to what statement does it right?

    If this were my procedure, I'd take each component part from above and investigate the value of it right at the point of use. If any one of these values or expressions are null they will cause your concatenation results to be null, so while it superficially seems like lots of work, its really a pretty direct route of debugging. So insert some debugging right before executing the above select statement is what I'd do!

    Like so:

    select 'ltable name', label1, @l_table_name val1

    union

    select '@L_INS_STMT', @L_INS_STMT

    union

    ( etc etc etc )

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

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