Stored procedure truncates a variable using the CONCAT function immediately after a parameter is included in the CONCAT statement

user_11632457 0
Viewed 175

I am trying to pass a parameter [e.g. @X nvarchar(MAX)] into a variable [e.g. @message nvarchar(MAX)] inside a stored procedure. The variable is using CONCAT to combine string values and variables, and it ultimately becomes both the body of an email that is sent from SQL Server and the value of a column in a table. For some reason, when I set the variable, the CONCAT ends immediately after the first parameter is passed to it.

One extra detail is that it works fine when I run the stored procedure manually. In this particular case, this issue only appears when the stored procedure is called from an external application.

I have tried the following options without success:

  • Setting the variable without CONCAT using triple single quotes
  • Passing the parameter to a separate variable prior to the @message variable
  • Setting the parameter to allow for OUTPUT to the original application (even though the original application does not need the OUTPUT)
ALTER PROCEDURE [dbo].[TEST] 
    (@param NVARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @message NVARCHAR(MAX),
            @email NVARCHAR(MAX)

    SET @email = 'test@test.com'

    SET @message = CONCAT ('Some text.',CHAR(13) + CHAR(10)
        ,'Value of param: ', @param, CHAR(13) + CHAR(10)
        ,'Some more text.');

    INSERT INTO [database].[dbo].[table] ([Message], [Param])
    VALUES (@message, @param);

    EXEC msdb.dbo.sp_send_dbmail
            @recipients = @email,
            @body = @message,
            @subject = 'SUBJECT OF EMAIL',
            @profile_name = 'profile_name';

The email is sent fine, and the message is stored to [table], but in both cases, the @message variable ends right after the @param is passed to it.

For example, if the value of @param was "paramTest", @message would look like this:

Some text.
Value of param: paramTest

Also, the param is stored fine to [table], so I know that the parameter has a valid value.

Is there a way to capture and see the exact data that is being passed from the external application to the stored procedure? I have been wondering if it is including some sort of special character(s) at the end of the data when it assigns the parameter.

stored-procedures tsql sql-server sql
2 Answers

Check what is passed as a parameter value.

Demo

DECLARE @param NVARCHAR(MAX) = 'abc msg' + CHAR(0); -- all the rest is lost

DECLARE @message NVARCHAR(MAX)

SET @message = CONCAT ('Some text.',CHAR(13) + CHAR(10)
    ,'Value of param: ', @param, CHAR(13) + CHAR(10)
    ,'Some more text.');

select @message;

Returns

Some text.
Value of param: abc msg
user_62199790

I suggest looking at the Message as a sequence of bytes. e.g.: 

select *, cast([Message] as varbinary(max)) as DataAsBytes 
from [database].[dbo].[table]

This allows you to see the byte representation of all characters.

Example:

DECLARE @param NVARCHAR(MAX) = 'b' + CHAR(0); 
DECLARE @message NVARCHAR(MAX)
SET @message = CONCAT ('a', @param, 'c');
select  @message as RawMessage, CAST(@message as varbinary(max)) as MessageAsBytes, LEN(@message) as RawMessageLength, DATALENGTH(CAST(@message as varbinary(max))) as NumBytes

When run in SSMS, shows RawMessage as: ab, so the CHAR(0) character has caused SSMS to not display the following 'c'. MessageAsBytes is shown as: 0x6100620000006300 where: 'a' = 6100, 'b' = 6200, CHAR(0) = 0000, 'c' = 6300. So you can "see" the CHAR(0) character.

Background info on unicode encoding:

https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses

user_701400
Related