CHECKSUM_AGG(checksum()) returns stars (**********)

Question

I am calling

id int ,
tableid int,
seid int,
ptid int,
VISID NVARCHAR(50),
Tname AS SYSNAME ,
ColumnValue NVARCHAR(50),
ColumnKey NVARCHAR(50),
@HashValue NVARCHAR(50) OUTPUT

select @HashValue = CHECKSUM_AGG(checksum(id,tableid, seid, ptid, VISID, Tname, ColumnKey, ColumnValue))  from #FDATA 

When #FDATA contains:

17 3028 100 100003 SCRN form.LG_AE 320 InvInit

17 3028 100 100003 SCRN form.LG_AE 321 AuthIn

......... (6 rows in total with similar data, no null values)

it returns **********

But when #FDATA contains:

17 3019 101 101001 SCRN form.LG_AE 320 InvInit

17 3019 101 101001 SCRN form.LG_AE 321 AUTHIn

...... (65 rows in total with similar data, no null values)

It returns an integer as expected

Answer 1

According to this and comments, function STR returns * because the number, which is stored in @HashValue variable, exceeds the specified length (default 10). Instead of STR use CAST, for example:

PRINT CAST(@HashValue as varchar(20))

or just print value without casting it:

PRINT @HashValue

or use function STR, but with length parameter:

PRINT STR(@HashValue,20)