Pass vale down through dynamic SQL statement

I was trying to refine a dynamic SQL statement I have to also write to a log file so if there is an error I can review the log and take appropriate action. In the statement the parameters of @O_ErrorState and @O_ErrorString contain the error number and description. I have been trying to them use these values in the insert statement to the import log file without success. These values are always null. How do I capture these values as the executesql command is run so I can log the error codes? I am assuming that the insert statement needs to occur within the dynamics sql statement.


DECLARE @dynamicSQL nvarchar(max) = 'DECLARE @O_ErrorState [integer] DECLARE @O_ErrorString [varchar](100) '

      -- Build the dynamics sql statement to insert the header
      SELECT @dynamicSQL += 'EXECUTE taPoHdr @I_vPOTYPE=' + LTRIM(RTRIM(CONVERT(CHAR(10),@POTYPE))) + ',@I_vPONUMBER=''' + LTRIM(RTRIM(@I_GP_PONUMBER)) +  
            ''',@I_vVENDORID=''' + LTRIM(RTRIM(VENDOR_NUMBER_POHD)) + ''',@I_vDOCDATE=''' + CONVERT(CHAR(10),PO_DATE_POHD,111) +  
            ''',@I_vBUYERID=''' + LTRIM(RTRIM(@I_BUYERID)) +      ''',@I_vCURNCYID=''' + LTRIM(RTRIM(@CURNCYID)) + ''',@I_vUSERID=''' + LTRIM(RTRIM(@I_BUYERID)) +      
            ''',@I_vPOSTATUS=' + LTRIM(RTRIM(CONVERT(CHAR(10),@POSTATUS))) + ',@I_vREQDATE=''' + CONVERT(CHAR(10),REQDDATE_POHD,111) +
            ''',@I_vPRMDATE=''' + CONVERT(CHAR(10),PROMDATE_POHD,111) + ''',@I_vPRMSHPDTE=''' + CONVERT(CHAR(10),PRSHDATE_POHD,111) +      
             ''',@O_iErrorState=@O_ErrorState OUTPUT,@oErrString=@O_ErrorString OUTPUT '
            FROM [POPHEADR] H
            WHERE PO_NUMBER_POHD=@I_PONUMBER

      -- Add the output to the log file before executing SQL command.
      SELECT @dynamicSQL +='INSERT INTO [IMPORTLOG] ' +
           '([DOCTYPE],[DOCNUMBER],[ORIGNUMB],[MSTRTYPE],[DATESTAMP],[ERRORCODE],[ERRORDESC]) VALUES(' +
               '''' + LTRIM(RTRIM(@DOCTYPE)) + ''',''' + LTRIM(RTRIM(@I_GP_PONUMBER)) + ''',''' + LTRIM(RTRIM(@I_PONUMBER)) + ''',' + LTRIM(RTRIM(CONVERT(CHAR(10),1))) + ','''+CONVERT(CHAR(10),GETDATE(),111) +
               ''',' +LTRIM(RTRIM(CONVERT(CHAR(10),COALESCE(@O_ErrorState,0)))) + ','''+ LTRIM(RTRIM(COALESCE(@O_ErrorString,''''))) +')'

      -- Insert the purchase order header
      execute sp_executesql @dynamicSQL
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
You don't need dynamic SQL at all:

DECLARE @Rows CURSOR;
DECLARE @VENDOR_NUMBER_POHD INT, @PO_DATE_POHD DATE, @REQDDATE_POHD DATE, @PROMDATE_POHD DATE, @PRSHDATE_POHD DATE;

SET @Rows = CURSOR STATIC FOR
            SELECT VENDOR_NUMBER_POHD, PO_DATE_POHD, REQDDATE_POHD, PROMDATE_POHD, PRSHDATE_POHD
            FROM [POPHEADR] H
            WHERE PO_NUMBER_POHD = @I_PONUMBER;

OPEN @Rows;
FETCH NEXT FROM @Rows INTO @VENDOR_NUMBER_POHD, @PO_DATE_POHD, @REQDDATE_POHD, @PROMDATE_POHD, @PRSHDATE_POHD;
WHILE (@@FETCH_STATUS = 0)
    BEGIN

    
    EXECUTE taPoHdr @I_vPOTYPE = @POTYPE,
                    @I_vPONUMBER = @I_GP_PONUMBER,
                    @I_vVENDORID = VENDOR_NUMBER_POHD,
                    @I_vDOCDATE = PO_DATE_POHD,
                    @I_BUYERID = @I_BUYERID,
                    @I_vCURNCYID = @CURNCYID,
                    @I_vUSERID = @I_BUYERID,
                    @I_vPOSTATUS = @POSTATUS,
                    @I_vREQDATE = REQDDATE_POHD,
                    @I_vPRMDATE = PROMDATE_POHD,
                    @I_vPRMSHPDTE = PRSHDATE_POHD,
                    @O_iErrorState = @O_ErrorState OUTPUT,
                    @oErrString = @O_ErrorString OUTPUT

    INSERT INTO [IMPORTLOG]([DOCTYPE],[DOCNUMBER],[ORIGNUMB],[MSTRTYPE],[DATESTAMP],[ERRORCODE],[ERRORDESC])
    VALUES(@DOCTYPE, @I_GP_PONUMBER, @I_PONUMBER, CHAR(10), GETDATE(), ISNULL(@O_ErrorState, 0), ISNULL(@O_ErrorString, ''))

    FETCH NEXT FROM @Rows INTO @VENDOR_NUMBER_POHD, @PO_DATE_POHD, @REQDDATE_POHD, @PROMDATE_POHD, @PRSHDATE_POHD;

    END

CLOSE @Rows

Open in new window

0
rwheeler23Author Commented:
There are actually four sections to this code. One for PO headers and lines and one for Sales Order headers and lines. The ones for the lines need dynamics SQL because of the call to a stored procedure and these routines were written first so when it came time for the headers the concept reused. For the headers you are right dynamic SQL is not needed since it a one to one relationship. Since apparently this cannot be done I will convert the header code to be done without dynamic SQL. What I was trying to do was write to a log table whenever ErrorState was a non-zero value. I try not to use cursors whenever possible.
0
Máté FarkasDatabase Developer and AdministratorCommented:
You did not mention other circumstances which forces to use dynamic SQL however I still don't understand why do you need dynamic SQL. Then you have to change other parts that use dynamic SQL but they don't need (because you see that the code is much simpler and cleaner without dynamic SQL). And don't have a fear from static cursor variables they works differently than simple cursors (you can eliminate cursors when you have a primary key in the table).

If you still want to toil with dynamic SQL then you have to append loggin part into the same command (because currently it logs only the result of the latest execution):

SELECT @dynamicSQL += 'EXECUTE taPoHdr @I_vPOTYPE = ' + LTRIM(RTRIM(CONVERT(CHAR(10), @POTYPE)))
                    + '               ,@I_vPONUMBER   = ''' + LTRIM(RTRIM(@I_GP_PONUMBER)) + ''''
                    + '               ,@I_vVENDORID   = ''' + LTRIM(RTRIM(VENDOR_NUMBER_POHD)) + ''''
                    + '               ,@I_vDOCDATE    = ''' + CONVERT(CHAR(10),PO_DATE_POHD,111) +  ''''
                    + '               ,@I_vBUYERID    = ''' + LTRIM(RTRIM(@I_BUYERID)) + ''''
                    + '               ,@I_vCURNCYID   = ''' + LTRIM(RTRIM(@CURNCYID)) + ''''
                    + '               ,@I_vUSERID     = ''' + LTRIM(RTRIM(@I_BUYERID)) + ''''
                    + '               ,@I_vPOSTATUS   = ''' + LTRIM(RTRIM(CONVERT(CHAR(10),@POSTATUS))) + ''''
                    + '               ,@I_vREQDATE    = ''' + CONVERT(CHAR(10),REQDDATE_POHD,111) + ''''
                    + '               ,@I_vPRMDATE    = ''' + CONVERT(CHAR(10),PROMDATE_POHD,111) + ''''
                    + '               ,@I_vPRMSHPDTE  = ''' + CONVERT(CHAR(10),PRSHDATE_POHD,111) + ''''
                    + '               ,@O_iErrorState = @O_ErrorState OUTPUT'
                    + '               ,@oErrString    = @O_ErrorString OUTPUT;'
                    + CHAR(13) + CHAR(10)
                    + 'INSERT INTO [IMPORTLOG]([DOCTYPE],[DOCNUMBER],[ORIGNUMB],[MSTRTYPE],[DATESTAMP],[ERRORCODE],[ERRORDESC]) '
                    + 'VALUES('''  + LTRIM(RTRIM(@DOCTYPE)) + ''''
                            + ',''' + LTRIM(RTRIM(@I_GP_PONUMBER)) + ''''
                            + ',''' + LTRIM(RTRIM(@I_PONUMBER)) + ''''
                            + ',''' + LTRIM(RTRIM(CONVERT(CHAR(10), 1))) + ''''
                            + ',''' + CONVERT(CHAR(10), GETDATE(), 111) + ''''
                            + ',ISNULL(@O_ErrorState, 0) ' 
                            + ',LTRIM(RTRIM(ISNULL(@O_ErrorString, '''')'
                    + ')'
                    + CHAR(13) + CHAR(10)
FROM [POPHEADR] H
WHERE PO_NUMBER_POHD=@I_PONUMBER

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

rwheeler23Author Commented:
As is normally the case, there are multiple ways to solve the same problem. When it comes to the lines of these orders I needed a way to loops through all the lines before writing the header record. For these it was thought that dynamic SQL was the at to go. This way I build a command with all the detail and then commit those detail records. The header record has to come last because when its stored procedure is called it aggregates the totals from the lines. I was not aware of static cursors. I have always been told not to use cursors due to the performance hit. I will definitely look into your suggestions. You have been most helpful. By the way for the PO header table the PK is PONUMBER and for the PO detail it is PONUMBER and LineNumber. How does having a PK eliminate the need for cursors?
0
Máté FarkasDatabase Developer and AdministratorCommented:
In your case cursor (or some similar solution) is required. Cursor is slower than a simple UPDATE or SELECT but if you have to use it then you have no choice.
In your case the execution of cursor has little overhead opposite to executing stored procedure.

Here is the logic to replace a cursor:
DECLARE @PONUMBER INT = 0;

WHILE (1 = 1)
	BEGIN

	SELECT	TOP 1
			@PONUMBER = PONUMBER,
			@VENDOR_NUMBER_POHD = VENDOR_NUMBER_POHD

			-- ... other fields

	FROM [POPHEADR] H
	WHERE PO_NUMBER_POHD = @I_PONUMBER
	AND PONUMBER > @PONUMBER
	ORDER BY PONUMBER;

	IF @@ROWCOUNT = BREAK;

	-- Put your code here to use values from the table

	END

Open in new window

0
rwheeler23Author Commented:
Thank you your insight. It has been most helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.