asked on

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

Máté Farkas

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

rwheeler23

ASKER

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.

ASKER CERTIFIED SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

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?

Máté Farkas

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

rwheeler23

ASKER

Thank you your insight. It has been most helpful.