Passing a Variable to Varchar(#)
-
I have the following code, and what I would like to be able to do is pass a variable to the varchar () statement. I get an error that says "incorrect syntax near @OcSqNoLength"
Any ideas? Thanks.
DECLARE @OcSqNoLength INT
SET @OcSqNoLength = (SELECT MAX (LEN(OCCUSEQNO)) FROM OCCUPANTHEADER)
--PRINT @OcSqNoLength
SELECT top 10
--(SELECT MAX (LEN(OCCUSEQNO)) FROM OCCUPANTHEADER)
B.PROPERTYID
, B.UNITID
, B.OCCUSEQNO
, B.PROPERTYID+'-'+B.UNITID+'-'+CAST(B.OCCUSEQNO AS VARCHAR(@OcSqNoLength))
FROM LEASEWITHSTATUS A
INNER JOIN OCCUPANTHEADER B ON A.PROPERTYID=B.PROPERTYID AND A.BLDGID=B.BLDGID
AND A.UNITID=B.UNITID AND A.RESIID=B.RESIID
AND A.CALCDATE = CONVERT(VARCHAR(10), GETDATE(),101)
AND A.OCCUSTATUS IN ('C','N')
-
You cannot use a variable to define the length of a varchar() datatype. A varchar() is variable in length by nature. What datatype is B.OCCUSEQNO? Define your varchar large enough to hold the maximum length of that datatype. I have a feeling that there is more to this than meets the eye. What are you trying to accomplish with this? Is this a formatting issue?
-
What's a point?
If B.OCCUSEQNO is a number then CAST(B.OCCUSEQNO AS VARCHAR(9)) will return absolutely the same string as CAST(B.OCCUSEQNO AS VARCHAR(1009))
_____________
Code for TallyGenerator -
Thanks, was more of science experiment than anything else. Figured I would have to simply use varchar(10).....occuseqno is an int currently.
Thanks all.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply