Creating a string variable SQL...

[lidds]

I have the below stored procedure:

ALTER PROCEDURE [dbo].[spInsertComm] @commTag as nvarChar(255), @commTitle as nvarchar(255), @commDisp as nvarchar(255), 
@commType as nvarchar(255), @commDate as varchar(25), @commArea as nvarchar(100), @commModRef as nvarchar(100), 
@commOrig as nvarchar(100), @commDesc as ntext, @commAction as ntext, @commCreated as varchar(50), @commImage as image, @commRptImage as image, @commReview as nvarchar(100), 
@commObj as nvarchar(100), @workShare as nvarchar(100), @userCreatedID as varchar(10), @commCommited as varchar(3), @camOriginX as varchar(50), @camOriginY as varchar(50), @camOriginZ as varchar(50), @camTargetX as varchar(50),
@camTargetY as varchar(50), @camTargetZ as varchar(50),@objOriginX as varchar(50), @objOriginY as varchar(50), @objOriginZ as varchar(50), @objID as varchar(50), @viewInModel as varchar(3), @MISData as varchar(5),@MISAppData as varchar(5),@application varchar(50),
@inSession as varchar(5),@camUpVecX as varchar(50),@camUpVecY as varchar(50),@camUpVecZ as varchar(50),@camRotAxisX as varchar(50),@camRotAxisY as varchar(50),@camRotAxisZ as varchar(50),@focalDistance as varchar(50),@camRotation as varchar(50), @fileUnits as varchar(50),
@prefix as nvarchar(255),@suffix as nvarchar(255)
AS 
INSERT INTO commentsTbl (commTag,commTitle,commDisp,commAssignDisp,commType,commDate,commArea,commModRef,commOrig,commDesc, 
commAction,commCreated,commImage,commRptImage,commHistory,original,commReview,commObj,modifyDate,workShare,userCreatedID,commCommited,commStatus,camOriginX,camOriginY,camOriginZ,camTargetX,camTargetY,camTargetZ,objOriginX,
objOriginY,objOriginZ,objID,viewInModel,MISData,MISAppData,Application,inSession,camUpVecX,camUpVecY,camUpVecZ,camRotAxisX,camRotAxisY,camRotAxisZ,focalDistance,camRotation,fileUnits) VALUES (@commTag,@commTitle,@commDisp,@commDisp,@commType,@commDate,@commArea,@commModRef,@commOrig,@commDesc,@commAction,@commCreated,@commImage,
@commRptImage,'No','Yes',@commReview,@commObj,@commCreated,@workShare,@userCreatedID,@commCommited,'Unapproved',@camOriginX,@camOriginY,@camOriginZ,@camTargetX,@camTargetY,@camTargetZ,@objOriginX,@objOriginY,
@objOriginZ,@objID,@viewInModel,@MISData,@MISAppData,@application,@inSession,@camUpVecX,@camUpVecY,@camUpVecZ,@camRotAxisX,@camRotAxisY,@camRotAxisZ,@focalDistance,@camRotation,@fileUnits) 

DECLARE @ID int
DECLARE @commID varchar
DECLARE @newCommID int
DECLARE @strCommID nvarchar

SET @ID = @@IDENTITY

SELECT @newCommID = (commID)+1 FROM uniqueIDTbl
UPDATE uniqueIDTbl SET commID=@newCommID

SET @strCommID = @prefix + CONVERT(nvarchar(255),@newCommID) + @suffix

UPDATE commentsTbl SET commID=@strCommID WHERE [ID]=@ID

SELECT * FROM commentsTbl WHERE [ID]=@ID

I am having a problem with the following line:

SET @strCommID = @prefix + CONVERT(nvarchar(255),@newCommID) + @suffix
[code]

I do not receive an error, as it is working but returning the wrong result in my table, below are the @prefix and suffix

preffix = 'simon1-'
suffix   ='bye'

Therefore the strCommID value that I am inserting into the table shoul be like so:

simon1-26-bye

However it just seems to insert 's' to the table.

Any help would be great


[ATTACH]3365.vB[/ATTACH]

 

[Menthos]

When you declare @strCommID, make sure you specify a length - the default is 1, hence you only seeing the 's' of 'simon1-26-bye'

example

DECLARE @strCommID nvarchar(255)