14
\$\begingroup\$

I only want to create this SQL function if the dependent Assembly exists. I can do it using dynamic SQL, but it seems messy and I lose syntax checking (in management studio). This function's dependency (for various reasons) may or may not be available on an individual developers machine, and I don't want to interrupt our local "get latest" process with an error if this is the case.

Is there a better way?

--Attempt 1 (FAILS)
IF  EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SQL_CLR_Functions')
BEGIN
    CREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N',')
    RETURNS TABLE (str nvarchar(4000)) AS EXTERNAL NAME SQL_CLR_Functions.[Library.SQL.CLR.Functions].CLR_CharList_Split
END

--Attempt 2 (FAILS)
BEGIN TRY
    CREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N',')
    RETURNS TABLE (str nvarchar(4000)) AS EXTERNAL NAME SQL_CLR_Functions.[Library.SQL.CLR.Functions].CLR_CharList_Split
END TRY
BEGIN CATCH
END CATCH

--Attempt 3 (FAILS)
IF NOT EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SQL_CLR_Functions')
BEGIN
 GOTO END_CLR;
END
GO
CREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N',')
RETURNS TABLE (str nvarchar(4000)) AS EXTERNAL NAME SQL_CLR_Functions.[Library.SQL.CLR.Functions].CLR_CharList_Split
GO
END_CLR:

--Attempt 4 (WORKS!!!)
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SQL_CLR_Functions')
BEGIN
    EXEC (N'CREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N'','')
            RETURNS TABLE (str nvarchar(4000)) AS EXTERNAL NAME SQL_CLR_Functions.[Library.SQL.CLR.Functions].CLR_CharList_Split')
END
\$\endgroup\$
16
\$\begingroup\$

You could use the SET NOEXEC statement, which controls whether the subsequent statements are executed (OFF) or not (ON). Use it in conjunction with your EXISTS test like this:

IF NOT EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SQL_CLR_Functions')
  SET NOEXEC ON
GO
CREATE FUNCTION dbo.CLR_CharList_Split(@list nvarchar(MAX), @delim nchar(1) = N',')
RETURNS TABLE (str nvarchar(4000)) AS EXTERNAL NAME SQL_CLR_Functions.[Granite.SQL.CLR.Functions].CLR_CharList_Split
GO
SET NOEXEC OFF
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ That's fantastic! Never knew about Set NoExec. \$\endgroup\$ – Brandon Boone Jul 20 '12 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.