July 2, 2014 at 4:35 pm
I can't seem to find any syntax problems - can someone please advise?
The Select Count ... forward works great in a query window.
The Check code returns
Msg 156, Level 15, State 1, Procedure Rule_DoesWellNameContainUNIT, Line 6
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure Rule_DoesWellNameContainUNIT, Line 13
Incorrect syntax near the keyword 'END'
USE MyDBName
GO
CREATE FUNCTION dbo.Rule_DoesWellNameContainUNIT(@ID_Wells int) RETURNS int
AS
BEGIN;
DECLARE @Result int;
SET @Result = SELECT COUNT(*) AS "SELECT Well_Name FROM Wells WHERE (ID_Wells = @idWells) AND (Well_Name LIKE N'%Unit%');"
IF @Result = 0
Return 0; -- False
else
Return 1; -- True
end;
END;
go
July 2, 2014 at 4:58 pm
Quick pointer, the query is only a character string in the code and it never gets executed. Change the code to include the query not using a string.
😎
July 2, 2014 at 5:17 pm
I'm not sure where to start. Your variable assignment is completely wrong.
In other order of ideas, you could change your scalar function into an inline table valued function which should work faster.
I'm leaving both options for you to test.
USE MyDBName
GO
CREATE FUNCTION dbo.Rule_DoesWellNameContainUNIT(@ID_Wells int)
RETURNS int
AS
BEGIN;
DECLARE @Result int;
SELECT @Result = SIGN( COUNT(*) )
FROM Wells
WHERE (ID_Wells = @idWells) AND (Well_Name LIKE N'%Unit%');
Return @Result
END;
go
--Alternate version using an iTVF
CREATE FUNCTION dbo.tRule_DoesWellNameContainUNIT(@ID_Wells int)
RETURNS table
AS
RETURN
SELECT Result = SIGN( COUNT(*) )
FROM Wells
WHERE (ID_Wells = @idWells)
AND (Well_Name LIKE N'%Unit%');
go
For more information on how to make scalar functions run faster, check the following article:
July 3, 2014 at 8:19 am
Thank you very much!
The use of this is calling only one ID_Wells at a time.
The data set is very small, about 50,000 total records in the main Wells table.
It is used to index regulatory process.
The common use of this, the client will call only one Id_Well at at time and expect a single result.
With such a small DB it is difficult form me to determine which one is faster.
Use MyDBname
CREATE FUNCTION dbo.Rule_DoesWellNameContainUNIT(@ID_Wells int)
RETURNS int
AS
BEGIN;
DECLARE @Result int;
SELECT @Result = SIGN( COUNT(*) )
FROM Wells
WHERE (ID_Wells = @id_Wells) AND (Well_Name LIKE N'%Unit%');
-- changed from idWells
Return @Result
END;
go
select dbo.Rule_DoesWellNameContainUNIT(53)
Example Used in a View:
SELECT ID_Wells, Well_Name, dbo.Rule_DoesWellNameContainUNIT(ID_Wells) AS Fed_Requirements
FROM dbo.Wells
July 3, 2014 at 3:51 pm
I'm not sure that you need a function.
Here's an example using the over clause to solve the problem and at the same time the iTVF option. I strongly suggest you to read the article that I recommended to understand how scalar UDFs affect performance.
Note how I posted the sample data. You're expected to do it that way to get better help (and you should know it after all your visits).
CREATE TABLE dbo.Wells(
ID_Wells int,
Well_Name varchar(50))
INSERT INTO dbo.Wells
VALUES
(1, 'Something'),
(1, 'Something with Unit'),
(1, 'Something with another Unit'),
(2, 'One Unit Well'),
(2, 'Group Well'),
(3, 'Last Well')
GO
CREATE FUNCTION dbo.tRule_DoesWellNameContainUNIT(@ID_Wells int)
RETURNS table
AS
RETURN
SELECT Result = SIGN( COUNT(*) )
FROM Wells
WHERE (ID_Wells = @ID_Wells)
AND (Well_Name LIKE N'%Unit%');
GO
SELECT ID_Wells,
Well_Name,
SIGN( COUNT(CASE WHEN Well_Name LIKE '%Unit%' THEN 1 END) OVER( PARTITION BY ID_Wells)) AS Fed_Requirements,
r.Result AS Fed_RequirementsFunction
FROM dbo.Wells
CROSS APPLY dbo.tRule_DoesWellNameContainUNIT( ID_Wells) r
GO
DROP TABLE Wells
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply