CODEWITHSUNDEEP
sql-serverstored-proceduressql-update
SRahmani
SRahmani

Reputation: 359

Update field so that parameter in SQL has 16 character length

I am writing a stored procedure that will dummy some credit card data (please note that this is not live!) It is for internal purposes only. This sp runs, but it is only printing out a subset of 10 character (numeric) lengths and not 16. Does anyone have any insight?

Here is my SProc:

DECLARE @RESULT int

DECLARE @cc as varchar(50) = (SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)))

UPDATE trans

SET trans_CCNUM =(SELECT stuff(@cc,1,LEN(@cc)-4,REPLICATE('x', LEN(@cc)-5)))

where LEN(trans_ccNum) = 16;

PRINT @RESULT

Here are the results

dateCreated trans_CCNUM

2014-09-11 16:55:13.800 xxxx9328

Upvotes: 1

Views: 55

Answers (1)

Sparky
Sparky

Reputation: 15075

If your example above, @cc is only going to be 9 or 10 characters long...

DECLARE @cc as varchar(50) = 
    (SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)))

    SELECT @cc,len(@cc)

    select stuff(@cc,1,LEN(@cc)-4,REPLICATE('x', LEN(@cc)-5))

That is why you are only seeing 9/10 characters

Try changing the INT to BIGINT and you should be OK

Upvotes: 1

Related Questions