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
TorrwinAsked:
Who is Participating?
 
HainKurtConnect With a Mentor 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;

Open in new window

0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
try

SELECT sign(count(1)) FROM TABLE WHERE COLUMN1 = PARAMATER1 AND COLUMN2 = PARAMETER2 AND COLUMN3 = PARAMETER3
 
0
 
dwkorCommented:
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

0
 
HainKurtSr. 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
 
itcoupleCommented:
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

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.

All Courses

From novice to tech pro — start learning today.