Torrwin
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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:
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
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.
Open in new window