CODEWITHSUNDEEP
sql.netsql-serveruser-defined-functions
EdwinJackson
EdwinJackson

Reputation: 13

Return Bit in UDF

This is more specific to MS SQL (>2008, but mostly version agnostic).

I'm looking to simplify the check for a Stored Procedure in SQL Server. What I would like to end up with is something like this:

IF (dbo.PROC_EXISTS(N'MY_STORED_PROC_NAME') = 1)
    DROP PROCEDURE MY_STORED_PROC_NAME
GO

What I currently have:

SELECT
CAST(
    CASE 
        WHEN EXISTS (
            SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'GET_ALT_SCHEDULE')
                        AND type IN (N'P', N'PC')
        ) 
        THEN 'TRUE' 
        ELSE 'FALSE'
        END 
AS BIT
)

RETURNS 1 -- TRUE
RETURNS 0 -- FALSE

When I place this code inside of a function, it does not work because I cannot return a value that comes from a select statement.

CREATE FUNCTION PROC_EXISTS(@SPName NVARCHAR)
    RETURNS BIT
AS

BEGIN

DECLARE @Answer BIT

SET @Answer = SELECT  -- Incorrect syntax near 'SELECT'
    CAST(
        CASE 
            WHEN EXISTS (
                SELECT  *
                FROM    sys.objects
                WHERE   object_id = OBJECT_ID(@SPName)
                           AND type IN (N'P', N'PC')
            ) 
            THEN 1 
            ELSE 0
            END 
    AS BIT
    )
RETURN @Answer
END

Could someone provide some insight into what's going on? and if you can a solution to how this function could be composed to return what I'm looking for (As per the first code block)

Or perhaps a function is not appropriate for this scenario. But please state why.

Upvotes: 1

Views: 1123

Answers (1)

SqlZim
SqlZim

Reputation: 38033

How about just using object_id()?

if object_id(N'dbo.proc_name') is not null 
drop procedure dbo.proc_name;

To correct your function code:

Your input is specified as nvarchar without a size, so it defaults to a length of 1. Change it to sysname instead.

Remove select as you are using set already. Alternatively, you could remove set and use select @Answer = cast ....

create function proc_exists (@spname sysname)
returns bit as
begin;
  declare @Answer bit;
  set @Answer = case when exists (
      select *
      from sys.objects
      where object_id = object_id(@spname) 
        and type in (N'P', N'PC')
      ) 
      then 1 else 0 end;
  return @Answer;
end;
go

rextester demo: http://rextester.com/EYWVBY20177

Upvotes: 3

Related Questions