CODEWITHSUNDEEP
sqlsql-server
DamnGroundHog
DamnGroundHog

Reputation: 67

Creating an SQL function to return a bit when it does a check for data

I wrote an SQL query that works great, but am having a hard time converting into a function and could use some advice.

here is the SQL query

SELECT
CASE WHEN COUNT(EXPORT_DATE) >=1 AND COUNT(IMPORT_DATE) >=1 AND COUNT(LOCATION)>=1 THEN
CAST (1 AS BIT)
ELSE
CAST (0 AS BIT)
END AS ISTHERE
FROM data_tables
WHERE EXPORT_DATE = '1/1/2020' AND IMPORT_DATE = '1/5/2020' AND LOCATION = 'ENTER LOCATION HERE'

This returns a 1 or 0 if all those conditions are met in a column called ISTHERE

I am trying to turn this into a function so I can call it and am getting stuck

CREATE FUNCTION duplicate_data (@exportdate date, @importdate date, @location varchar(255))
RETURNS BIT
AS 
BEGIN
SELECT
CASE WHEN COUNT(EXPORT_DATE) >=1 AND COUNT(IMPORT_DATE) >=1 AND COUNT(LOCATION)>=1 THEN
CAST (1 AS BIT)
ELSE
CAST (0 AS BIT)
END AS ISTHERE
FROM data_tables
WHERE EXPORT_DATE = '1/1/2020' AND IMPORT_DATE = '1/5/2020' AND LOCATION = 'ENTER LOCATION HERE'
RETURN ISTHERE
END

I get 2 errors

  1. Select Statements included within a function cannot return data to a client
  2. invalid column name = ISTHERE

Still new to writing functions, where is my syntax incorrect?

This is for Microsoft SQL Server and all 3 conditions must be met.

many thanks

DamnGroundHog

Upvotes: 1

Views: 952

Answers (2)

Thom A
Thom A

Reputation: 95657

Both errors are telling you the problem here:

Select Statements included within a function cannot return data to a client

You have a scalar function, so, as the error tells you, you can't have a SELECT that returns data to the client.

Invalid column name = ISTHERE

in the statement RETURN ISTHERE the column ISTHERE has no context, so hence the error.

Scalar functions work with variables for returning, so what you actually want is:

CREATE FUNCTION dbo.duplicate_data (@exportdate date, @importdate date, @location varchar(255))
RETURNS BIT
AS 
BEGIN
    DECLARE @IsThere bit;
    SET @IsThere = (SELECT CASE WHEN COUNT(EXPORT_DATE) >=1 AND COUNT(IMPORT_DATE) >=1 AND COUNT(LOCATION)>=1 THEN CAST(1 AS BIT)  
                                                                                                              ELSE CAST(0 AS BIT)
                           END
                    FROM dbo.data_tables
                    WHERE EXPORT_DATE = @exportdate
                      AND IMPORT_DATE = @importdate
                      AND LOCATION = @location)
    RETURN @IsThere;
END;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Because of your where conditions, you can drastically simplify the query to:

select (case when count(*) >= 1 then 1 else 0 end)
from dbo.data_tables
where EXPORT_DATE = @exportdate and
      IMPORT_DATE = @importdate and
      LOCATION = @location;

Then you can write this as a scalar inline function:

CREATE FUNCTION duplicate_data (
    @exportdate date,
    @importdate date,
    @location varchar(255)
)RETURNS BIT
AS 
    RETURN (select convert(bit, (case when count(*) >= 1 then 1 else 0 end))
            from dbo.data_tables
            where EXPORT_DATE = @exportdate and
                  IMPORT_DATE = @importdate and
                  LOCATION = @location
           );

Upvotes: 1

Related Questions