# 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
LVL 13
###### Who is Participating?

Sr. System AnalystCommented:
function
``````create function is_valid(
@parameter1 as string, @parameter2 as string, @parameter3 as string
) returns int as
begin
declare @v int;
SELECT @v = sign(count(1)) FROM myTABLE WHERE COLUMN1 = @parameter1 AND COLUMN2 = @parameter2 AND COLUMN3 = @parameter3;
return @v;
end;
``````
0

Sr. System AnalystCommented:
try

SELECT sign(count(1)) FROM TABLE WHERE COLUMN1 = PARAMATER1 AND COLUMN2 = PARAMETER2 AND COLUMN3 = PARAMETER3

0

Commented:
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
``````
0

Sr. System AnalystCommented:
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
0

Commented:
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
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.