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?
 
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
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.