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:

[code]
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

[techgnome]

I'm not sure what it is you're trying to do.... but you do realize that this will update EVERY ROW in your table:
SELECT @newCommID = (commID)+1 FROM uniqueIDTbl
UPDATE uniqueIDTbl SET commID=@newCommID

that sets the commID to the same value for each and every row in the table.

Also... normally it's a good idea to specify the length of the string when declaring it:
DECLARE @commID varchar

might be why you're getting just the "s" ....

-tg

[lidds]

tg,

Thanks for your help it was because I was not specifying the length of the nVarChar variable.

With regards to the other point, this is fine as there is only ever one row of data in the table, so the fact it updates all rows is fine.

Once again thanks for the help

Simon

[techgnome]

I know you're not specifying a length that's the issue...if you don't specify a length, by default you only get ONE character...

that's why you get the "s" and nothing else...

declare @Var1 varchar
declare @Var2 varchar(10)

set @Var1 = 'techgnome'
set @Var2 = 'techgnome'

select @Var1, @Var2

Results:

     
---- ----------
t    techgnome

(1 row(s) affected)

See? Since I only specified varchar... that's what I get a CHAR ... no more, no less.

-tg