sql puzzle: (yelworcm 's UDF) 2

[maswien]

I think this a interesting question and pretty much buried in the original thread. It's posted by yelworcm:

What function returns 0, if called first thing after connecting to SQL Server, but never returns 0 after that initial call.

I can think of a way using procedure to achieve it, better let someone else change it to a UDF, following is my procedure

CREATE procedure getst
@cnt int output
AS
BEGIN
set nocount on 
if not exists(select * from tempdb..sysobjects where name like '##ses%')
 create table ##ses(ses_id binary(128), cnt int)  

declare @ses_id as binary(128)
set @ses_id = ( select context_info from sysprocesses where spid = @@spid)

update ##ses set cnt = cnt + 1 
   where ses_id = @ses_id  
if (@@rowcount = 0)
insert into ##ses values(@ses_id, 0 )
 
select @cnt = cnt from ##ses

set nocount off
END

to test it:

declare @test int
exec getst @test output
select @test

 

[SQLDenis]

Not to be a pain in the rearend but what about

declare @test int
exec getst @test output
select @test

truncate table ##ses

declare @test int
exec getst @test output
select @test

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[yelworcm]

Credit still goes to an unknown (actually unremembered) poster from DBForums.com. In other words, I had to look this up in BOL. So there is a hint for you, the function is listed in BOL.

 

[vongrunt]

In other words: this is built-in function that returns 0 only once - and only when called first in session?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[maswien]

I assume this is a SQL puzzle, so someone need write a UDF, if it's a builtin function, then I would like to say it's a BOL puzzle

 

[druer]

select @@rowcount will return 0 only if you call it as the first thing when establishing a new connection. After that it will always return a value greater than 0.

 

[SQLDenis]

druer,

You are right hahahaha
star from me and to yelworcm for such a tricky question





Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Ooops not true

select @@ROWCOUNT

select * from pubs..authors where 1 = 0

select @@ROWCOUNT

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

ooooops. I shall "yield" my start back. I'll go back to the drawing board.

 

[yelworcm]

Actually, @@rowcount, and its brother rowcount_big() was the function that I had in mind. If you simply call @@rowcount, you get 1 row returned. I don't think the original poster of the question thought of the possiblity of calling it in a more "complex" query than select @@rowcount.

 

[vongrunt]

set datefirst 7
select @@rowcount

Same thing.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[maswien]

vongrunt ans SQLDennis point to the same issue of the function, we need to find a way : no matter what happened, as long as the user stay in the connection, this function won't return 0.

 

[druer]

Thanks for posting back yelworcm. I was going crazy trying to find a function that did as the question asked, and this was the closest I could find (excluding functions that were in regard to server restarting). At least I can go to sleep without feeling guilty that Denis had yielded me a star.

 

[vongrunt]

> I was going crazy trying to find a function that did as the question asked, and this was the closest I could find

... and I almost decompiled bloody BOL .chm files

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

vongrunt,

No need to decompile BOL chm, the files are available on MSDN since chm is nothing but compiled html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/