0

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

Community
  • 1
  • 1
aggicd
  • 727
  • 6
  • 28
  • Sorry you don't give us enough information to recreate your problem, please provide a minimally complete question, include table structures and enough data that shows your problem. – Steve Ford Jul 26 '17 at 09:06
  • According to documentation ([here](https://learn.microsoft.com/en-us/sql/t-sql/functions/checksum-agg-transact-sql)) it returns `int`. What type is variable `@HashValue`? – Rokuto Jul 26 '17 at 09:07
  • DECLARE @HashValue int – aggicd Jul 26 '17 at 09:08
  • And when you are selecting `@HashValue` it returns stars? Could you provide more information (maybe full data if it is possible)? – Rokuto Jul 26 '17 at 09:12
  • @Rokuto some times it returns stars and some times returns integer . I cant post full data, they are similar with ones I provided. the only thing that differs is the number of the rows – aggicd Jul 26 '17 at 09:17
  • 1
    I've found similar problem [here](https://stackoverflow.com/questions/9124055/why-cast-convert-from-int-returns-an-asterisk). Check it. How are you selecting `@HashValue`? Are you casting it to `varchar`? – Rokuto Jul 26 '17 at 09:21
  • I print it like this: print str(@HashValue) and there I see the stars or the correct value – aggicd Jul 26 '17 at 09:28

1 Answers1

0

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)
Rokuto
  • 814
  • 1
  • 11
  • 16