Solved

# Create Simple Boolean Function

Posted on 2011-10-07
276 Views
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
0
Question by:Torrwin

LVL 51

Assisted Solution

try

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

0

LVL 51

Accepted Solution

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

LVL 13

Expert Comment

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

LVL 51

Expert Comment

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

LVL 10

Expert Comment

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

## Featured Post

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.