UDF Problem - 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 2000 Forums

SQL Server Development (2000)

UDF Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-12 : 08:27:33

Tony writes "I have an UDF that should return a simple 0 or 1 depending upon the result of the SQL; REGARDLESS of whether the condition is met the function returns 0. Please advise/help...

I'm new to UDF's so I would appreciate any help - UDF is as follows:

CREATE FUNCTION fnTSCreated
(@Peoplesoft_Id nvarchar)
RETURNS int
AS
BEGIN
--// 0 - Not Created
--// 1 - Created

DECLARE @Created int --Created or Not

If (Select Count(*) FROM TS_Timesheet_Header WHERE Peoplesoft_Id =@Peoplesoft_Id) > 0
-- Created
SET @Created = 1
ELSE
-- Not Created
SET @Created = 0

RETURN (@Created)
END"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-12 : 08:44:52

s.b.
If exists (Select * FROM TS_Timesheet_Header WHERE Peoplesoft_Id =@Peoplesoft_Id)

but that won't make any difference.
Are there any trainking spaces?

try the query manually and check the parameter passed in.

What you have looks OK.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-12 : 08:48:21

[code]
CREATE FUNCTION dbo.fnTSCreated
(@Peoplesoft_Id nvarchar)
RETURNS int
AS
BEGIN
--// 0 - Not Created
--// 1 - Created

DECLARE @Created int --Created or Not

SELECT @Created = CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
FROM TS_Timesheet_Header
WHERE Peoplesoft_Id =@Peoplesoft_Id

RETURN @Created
END
[/code]

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-12 : 09:54:34

Looks to me like any of these UDFs should work. I'm thinking the WHERE is FALSE for some reason.

Don't UDFs have owners too? Should you be creating dbo.fnTSCreated to avoid ambiguity?

Try executing in Query Analyzer

Print dbo.fnTSCreated(N'MyValue')

Where MyValue should return a known 1

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources