CODEWITHSUNDEEP
sql-server
user1731452
user1731452

Reputation: 21

String concatenation problems in SQL Server

ALTER PROCEDURE [dbo].[InsertSMS]
-- Add the parameters for the stored procedure here
@SmsMsgDesc Nvarchar(Max)

AS
BEGIN

SET NOCOUNT ON;

INSERT INTO [Tbl_Log]([LogDescription])VALUES (@SmsMsgDesc)
declare @LogID int;
SET @LogID = CAST(SCOPE_IDENTITY() AS INT)

INSERT INTO [Tbl_SMS]
       ([SmsMsgDesc])
 VALUES
       **(@SmsMsgDesc+CAST(@LogID AS NVarchar(12)))**

END

Problem here is sometimes concatenation does not concatenate the last string I don't know why

Even if I do it like this 

INSERT INTO [Tbl_SMS]
       ([SmsMsgDesc])
 VALUES
       **(@SmsMsgDesc+'Test')**

the constant 'Test' sometimes doesn't appear at the end of the string this drives me crazy please help !

i'm calling this procedure using the following C# function :-

public int InsertSMSDB(string Message)
    {
        try
        {
            //int LogID;
            SqlConnection Conn=new SqlConnection(SmsDBConnection);
            SqlCommand Comm = new SqlCommand("InsertSMS", Conn);
            Comm.CommandType = System.Data.CommandType.StoredProcedure;

            Comm.Parameters.AddWithValue("@SmsMsgDesc", Message);

            Conn.Open();
            int RowEffected=Comm.ExecuteNonQuery();
            Conn.Close();

            if (RowEffected > 0)
            {
                return RowEffected;
            }
            else
            {
                return -1;
            }


        }
        catch (SqlException ex)
        {
            return -1;
        }
    }

Finally Some information may help in investegating this case in the Application there is 2 threads access the Tbl_SMS one Thread for Insertion and 1 Thread for Selection

Upvotes: 2

Views: 475

Answers (1)

Joe G Joseph
Joe G Joseph

Reputation: 24046

If the value passed to procedure @SmsMsgDesc is null then it will not concatenate

try this to avoid the null value

VALUES
       (isnull(@SmsMsgDesc,'')+CAST(@LogID AS NVarchar(12)))

Alternatively

you could change the procedure header

ALTER PROCEDURE [dbo].[InsertSMS]
@SmsMsgDesc Nvarchar(Max)=''

Upvotes: 2

Related Questions