Link to home
Start Free TrialLog in
Avatar of Torrwin
TorrwinFlag for United States of America

asked on

Create Simple Boolean Function

Hello,

I need help creating a function in MS SQL Server.  I could easily code this in .NET, but I've never written a SQL Server function before, and I couldn't find a good example to work off of.  Basically, the function would be called "Is_Valid", needs to accept 3 parameters, and return 1 or 0. (1 = True, 0 = False)

The logic is like so:
If the following query returns a record then return 1, otherwise return 0:
SELECT * FROM TABLE WHERE COLUMN1 = PARAMATER1 AND COLUMN2 = PARAMETER2 AND COLUMN3 = PARAMETER3
SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well, you for sure can create the function but technically, you don't need it. Something like below would work.

CASE WHEN exists(SELECT * FROM TABLE WHERE COLUMN1 = PARAMATER1 AND COLUMN2 = PARAMETER2 AND COLUMN3 = PARAMETER3) THEN 1 ELSE 0 END.

If you decide to use functions, do not create multi-statement function for that especially if you plan to use it in the queries. This is performance hit. Think about .Net - about overhead when you call the method for every row. Use Inline one (even if syntax is more cryptic) - those are working similarly to inline functions in C++ - and be embed into the plan rather than use external call.


create function dbo.MyFunc
(
	-- use correct data types
	@PARAM1 varchar(32) 
	,@PARAM2 varchar(32) 
	,@PARAM3 varchar(32) 
)
returns table
as
return
(
	select 
		(
			CASE 
				WHEN exists (SELECT * FROM TABLE1 WHERE COLUMN1 = @PARAM1 AND COLUMN2 = @PARAM2 AND COLUMN3 = @Param3)
				THEN 1
				ELSE 0
			END
		) as Result
)
go

-- usage - for 1 row
if exists(select * from dbo.MyFunc(@P1, @P2, @P3) where Result = 1)
...
-- usage -- will return only rows from Table2 where corresponding rows exists in Table1
select --
from 
	dbo.Table2 t2 cross apply
		dbo.MyFunc(t2.Col1, t2.Col2, t2.Col3) F
where 
	f.Result = 1

Open in new window

sign(count(1)) usage without creating a function

select * from myOthertabl a
where (select sign(count(1)) from mytable b where b.col1=a.col1 and b.col2=a.col2 and b.col3=a.col3) = 1

with above function

select * from myOthertabl a
where is_valid(col1,col2,col3) = 1
Hi

If I understand correctly you want:
 
CREATE FUNCTION is_valid
(
	@PARAM1 varchar(32) 
	,@PARAM2 varchar(32) 
	,@PARAM3 varchar(32) 

)
RETURNS bit
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result as bit

	-- Add the T-SQL statements to compute the return value here
	IF (SELECT COUNT(*) FROM TABLE1 WHERE COLUMN1 = @PARAM1 AND COLUMN2 = @PARAM2 AND COLUMN3 = @Param3) > 0
		SET @Result = 1
	ELSE
		SET @Result = 0
		
	-- Return the result of the function
	RETURN @Result

END
GO

Open in new window