?
Solved

Create Simple Boolean Function

Posted on 2011-10-07
5
Medium Priority
?
280 Views
Last Modified: 2012-06-21
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
Comment
Question by:Torrwin
  • 3
5 Comments
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 2000 total points
ID: 36933782
try

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

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 36933805
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
 
LVL 13

Expert Comment

by:dwkor
ID: 36933911
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 36933942
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

by:itcouple
ID: 36942346
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question