Dynamics GP Stored procedure [smFormatStringsForExecs]

You’ve seen it often enough in GP stored procedures, but what is it doing?

EXEC @iStatus = smFormatStringsForExecs 
    @I_vInputString = @I_charEndCustomer,
    @O_cOutputString = @cEndCustomer OUTPUT,
    @O_iErrorState = @O_iErrorState OUTPUT 

The above code snippet is the common code pattern it appears in. I had always assumed, until today that this procedure was cleaning the input into the procedures for anti-SQL injection attack purposes, alas it seems not.

The procedure actually turns the passed string into a quoted string for use as parameters when building up SQL by concatenation within other GP stored procedures.

Example:

IF we pass the string 6252''5 002     (with the trailing spaces) into the procedure, this is what we find:

DECLARE    @return_value int,
        @O_cOutputString char(255),
        @O_iErrorState int

EXEC    @return_value = [dbo].[smFormatStringsForExecs]
        @I_vInputString = N'6252''5  002   ',
        @O_cOutputString = @O_cOutputString OUTPUT,
        @O_iErrorState = @O_iErrorState OUTPUT

SELECT    @O_cOutputString as N'@O_cOutputString',
        @O_iErrorState as N'@O_iErrorState'

SELECT    'Return Value' = @return_value

So the procedure has taken any trailing space out and wrapped the passed string in quotes and doubled up any quotes that were in the string to delimit them. Thus the output of the procedure can be used in string concatenation to build a SQL script dynamically.

So you don’t want to use the output of this parameter directly or it won’t work:

Example

i.e. don’t do this with the output from the format strings procedure…

DELETE FROM RM00101 WHERE CUSTNMBR=@O_cOutputString

-as it will not match any customer numbers (unless your customer numbers are in quotes!)