newid() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

newid()

Author

Topic

connectd.ts
Starting Member

4 Posts

Posted - 2010-09-01 : 11:41:06

I was wondering if newid() value is different even if newid() is used in 2 different places in the same sql statement, i.e, one newid() is select clause and another newid() in where clause

SELECT *, ABS(CAST(
(BINARY_CHECKSUM
(tbl1.col1, NEWID())) as int))
% 100 FROM table1 tbl1
WHERE (ABS(CAST(
(BINARY_CHECKSUM
(tbl1.col1, NEWID())) as int))
% 100) < 20

The reason for my question: The results of the select do not seem to match with the where condition.

Thank you.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-01 : 11:49:48

it will return 2 distinct values, so your query won't work. but you can do this


Declare @newid uniqueidentifier
set @newid = newid()

SELECT	*, ABS(CAST((BINARY_CHECKSUM(tbl1.col1, @newid)) as int)) % 100
FROM	table1 tbl1
WHERE	(ABS(CAST((BINARY_CHECKSUM(tbl1.col1, @newid)) as int)) % 100) < 20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources