will the udf return null if even spcify datatype

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)

will the udf return null if even spcify datatype

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-03-19 : 22:00:58

Hai,
I have a question. i have written the udf, which returns the bit value.
the logic, i check the recordcnt if the count is 0, set the reuslt =0 or result =1,
i return 1..
but in the application when i check, it is return null,if there is no record found..will the udf, return null, if there is no record found..
if so, how can i handle this.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-19 : 22:56:50

You'll need to post your code.

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-03-19 : 23:04:20

quote:
Originally posted by robvolk

You'll need to post your code.

here is the code..
CREATE FUNCTION [dbo].[udfOTRpt_Chkcnt](@sid VARCHAR(15),@sname VARCHAR(40))

RETURNS bit

AS
BEGIN

DECLARE @RESULT bit
declare @cnt int
SET @RESULT =''
set @cnt =0

select @cnt =count(*) FROM student
WHERE sid = @sid AND sname =@sname
if @cnt =0
set @result = 0
else
set @result = 1

RETURN cast(@RESULT as bit)

END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-19 : 23:31:59

Try this instead:

CREATE FUNCTION [dbo].[udfOTRpt_Chkcnt](@sid VARCHAR(15),@sname VARCHAR(40))
RETURNS bit AS BEGIN 
DECLARE @RESULT bit

if exists(select * FROM student WHERE sid = @sid AND sname =@sname)
set @result=1
else
set @result=0
RETURN @RESULT
END

A few points:

- Don't do a count(*) if you're not going to use the value. EXISTS is much faster, especially on large tables.
- SET @RESULT='' is meaningless on a bit datatype.
- I don't see the need for this to be a function. If you're calling this function in a query, you can easily end up with a bad nested loop join if you have a lot of rows. Basically bad cursor performance. It's much better to include the Student table in a join or correlated subquery and use a CASE expression to return the 1 or 0. Even that isn't particularly useful, especially if it's only used in a WHERE clause.

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-03-20 : 00:00:19

no luck..same error..

quote:
Originally posted by robvolk

Try this instead:
CREATE FUNCTION [dbo].[udfOTRpt_Chkcnt](@sid VARCHAR(15),@sname VARCHAR(40))
RETURNS bit AS BEGIN
DECLARE @RESULT bit

if exists(select * FROM student WHERE sid = @sid AND sname =@sname)
set @result=1
else
set @result=0
RETURN @RESULT
END
A few points:

- Don't do a count(*) if you're not going to use the value. EXISTS is much faster, especially on large tables.
- SET @RESULT='' is meaningless on a bit datatype.
- I don't see the need for this to be a function. If you're calling this function in a query, you can easily end up with a bad nested loop join if you have a lot of rows. Basically bad cursor performance. It's much better to include the Student table in a join or correlated subquery and use a CASE expression to return the 1 or 0. Even that isn't particularly useful, especially if it's only used in a WHERE clause.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-20 : 00:05:29

hi sarakumar,
robvolk given function is working fine
it is returning the value 0 if it not exists else 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 09:56:05

quote:
Originally posted by Sarakumar

no luck..same error..

quote:
Originally posted by robvolk

Try this instead:
CREATE FUNCTION [dbo].[udfOTRpt_Chkcnt](@sid VARCHAR(15),@sname VARCHAR(40))
RETURNS bit AS BEGIN
DECLARE @RESULT bit

if exists(select * FROM student WHERE sid = @sid AND sname =@sname)
set @result=1
else
set @result=0
RETURN @RESULT
END
A few points:

- Don't do a count(*) if you're not going to use the value. EXISTS is much faster, especially on large tables.
- SET @RESULT='' is meaningless on a bit datatype.
- I don't see the need for this to be a function. If you're calling this function in a query, you can easily end up with a bad nested loop join if you have a lot of rows. Basically bad cursor performance. It's much better to include the Student table in a join or correlated subquery and use a CASE expression to return the 1 or 0. Even that isn't particularly useful, especially if it's only used in a WHERE clause.

where are you calling this function from? can you show calling code?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources