September 27, 2007 at 8:27 am
I tried to do something like this
CREATE Function Foo (params...)
Returns bit as
begin
RETURN (exists(somequery))
end
but this turns out to be tricky. apparently the EXISTS(FUNCTION) only works WHERE clauses as all the examples I find of EXISTS are in WHERE clauses.
I just wonder whether there is an elegant solution to this, or at least perhaps alternatively,
CREATE Function Foo (params...)
Returns bit as
begin
declare @YN bit
set @YN = 0
IF (exists(somequery)) SET @YN = 1
RETURN @YN
end
September 27, 2007 at 8:46 am
CREATE Function Foo (params...)
Returns bit as
begin
declare @count
select @count = count(*) from ............
if @count >= 1
begin
return 1
end
else
begin
return 0
end
end
September 27, 2007 at 8:47 am
I do this as you have shown in second example.
Here is a good example function written by Simon Sabin.
Create FUNCTION [dbo].[fn_FileExist]
/*******************************************************************************
Written By : Simon Sabin
Date : 12 November 2002
Description : Checks if a file/directory exists
: The property value can take the values
: IsFile = Will return 1 if the File passed in is a file
: IsDir = Will return 1 if the File passed in is a directory
: HasParentDir = Will return 1 if parent directory of the file
passed in exists
History
Date Change
------------------------------------------------------------------------------
12/11/2002 Created
*******************************************************************************/
(
@file text
,@property varchar(100)
)
RETURNS bit
AS
BEGIN
DECLARE @status bit
EXEC @status = dbo.usp_FileExist @file, @property
RETURN @status
END
September 28, 2007 at 7:32 pm
hope this helps 😉
CREATE Function Foo (params...)
AS
IF (exists(somequery))
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
GO
Cheers,
Jon
September 28, 2007 at 7:54 pm
Why do you want to do this in a function? I've never had to do that. There's always a better way to do this kind of thing. If you post the circonstances of the problem, then maybe we can figure out a better way to do this!
October 2, 2007 at 2:11 pm
Well the if/then or Case method is fine.
I was just curious about the EXISTS() function. Aparrently the ways in which you can use it are limited by sql syntax. for example, using it as an expression
RETURN myfunc(@something) -- ok
RETURN Exists(somequery) -- not ok
I guess I wish T-SQL was more like C
?;^)7
October 2, 2007 at 2:21 pm
I see what you mean, but I don,t understand why you'd need to do this. Can you post the problem you are trying to solve with this solution?
October 3, 2007 at 2:04 am
1) SELECT * FROM INFORMATION_SCHEMA.ROUTINES
2) SELECT OBJECT_ID('...')
3) SELECT * FROM sysobjects where...
N 56°04'39.16"
E 12°55'05.25"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply