Denis Gobo : Functions

How to log when a function is called?

Denis Gobo — Thu, 08 May 2008 17:25:00 GMT

This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb

--Create the table
USE tempdb
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go

--Here is the proc
CREATE PROC prLog
@SomeValue varchar(50)
AS
INSERT LogMeNow (SomeValue) VALUES(@SomeValue)
go

--And here is the function
CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END

Now call the function a couple of times

SELECT dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)

And look inside the table

SELECT * FROM LogMeNow

What if you were to run this?

SELECT dbo.fnBla(4),* FROM sys.sysobjects

See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!

I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(

Functions That Are Not Often Used: SIGN

Denis Gobo — Tue, 12 Feb 2008 19:46:00 GMT

SIGN is one of those function which you almost never see used in code.

Sometimes you are asked by the front-end/middle-tier developers to return a rowcount as well with the result set.
However the developers want you to return 1 if there are rows and 0 if there are none. How do you do such a thing?
Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function

For CASE we will do this

RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0

Using the SIGN function is even easier, all you have to do is this

RETURN SIGN(@@ROWCOUNT)

That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are
So let's see this in action

USE pubs

--Case Proc

CREATE PROCEDURE TestReturnValues

@au_id VARCHAR(49) ='172-32-1176'

SELECT *

FROM authors

WHERE au_id =@au_id

RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

--Sign Proc

CREATE PROCEDURE TestReturnValues2

@au_id VARCHAR(49) ='172-32-1176'

SELECT *

FROM authors

WHERE au_id =@au_id

RETURN SIGN(@@ROWCOUNT)

--Case Proc, 1 will be returned; default value is used

DECLARE @Rowcount int

EXEC @Rowcount = TestReturnValues

SELECT @Rowcount

--Case Proc, 0 will be returned; dummy value is used

DECLARE @Rowcount int

EXEC @Rowcount = TestReturnValues 'ABC'

SELECT @Rowcount

--Sign Proc, 1 will be returned; default value is used

DECLARE @Rowcount int

EXEC @Rowcount = TestReturnValues2

SELECT @Rowcount

--Sign Proc, 0 will be returned; dummy value is used

DECLARE @Rowcount int

EXEC @Rowcount = TestReturnValues2 'ABC'

SELECT @Rowcount

--Help the environment by recycling ;-)

DROP PROCEDURE TestReturnValues2,TestReturnValues

What else can we do with SIGN? How about if we want to indicate with 0 and 1 if there is a row in a table when we do an outer join, if there is a row we want to display 1 and otherwise 0

CREATE TABLE #Table1 (ID INT)

INSERT INTO #Table1 VALUES (1)

INSERT INTO #Table1 VALUES (2)

INSERT INTO #Table1 VALUES (3)

CREATE TABLE #Table2 (ID INT)

INSERT INTO #Table2 VALUES (1)

INSERT INTO #Table2 VALUES (3)

INSERT INTO #Table1 VALUES (3)

You can do this easily with CASE

SELECT CASE WHEN t2.ID IS NULL THEN 0 ELSE 1 END AS HasValue ,t2.ID FROM #Table1 t1