CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
Learner
Learner

Reputation: 571

Add some logic while using CLR stored procedure

I am trying to write validation in stored procedure. This stored procedure uses CLR code to create CLR stored procedure:

CREATE OR ALTER PROCEDURE SayHelloVoice    
    @messageText NVARCHAR(MAX),
    @procedureName NVARCHAR(MAX)     
AS     
    EXTERNAL NAME say.[CLRProcedures.SayHi].Voice
GO

The above code works perfectly. However, I need to add some validation:

CREATE OR ALTER PROCEDURE SayHelloVoice    
    @messageText NVARCHAR(MAX),
    @procedureName NVARCHAR(MAX)     
AS
    IF @messageText IS NULL
       RAISERROR('Custom text', 16,16)  
    -- some other logic here

    EXTERNAL NAME say.[CLRProcedures.SayHi].Voice
GO

But SSMS shows an error:

Incorrect syntax near the keyword 'EXTERNAL'.

How can I add some logic while using CLR stored procedure?

Upvotes: 0

Views: 59

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could wrap it:

CREATE OR alter PROCEDURE SayHelloVoice_sub   
  @messageText NVARCHAR(MAX)     
, @procedureName nvarchar(MAX)     
AS     
    EXTERNAL NAME say.[CLRProcedures.SayHi].Voice
GO

and

CREATE OR alter PROCEDURE SayHelloVoice    
  @messageText NVARCHAR(MAX)     
, @procedureName nvarchar(MAX)     
AS

    IF @messageText IS NULL
       RAISERROR('Custom text', 16,16)  
    -- some other logic here

    EXEC SayHelloVoice_sub @messageText,@procedureName;
GO

EDIT:

If it is about handling NULL only then RETURNS NULL ON NULL INPUT is the way to go.

CREATE FUNCTION

If RETURNS NULL ON NULL INPUT is specified in a CLR function, it indicates that SQL Server can return NULL when any of the arguments it receives is NULL, without actually invoking the body of the function. If the method of a CLR function specified in already has a custom attribute that indicates RETURNS NULL ON NULL INPUT, but the CREATE FUNCTION statement indicates CALLED ON NULL INPUT, the CREATE FUNCTION statement takes precedence. The OnNULLCall attribute cannot be specified for CLR table-valued functions.

Upvotes: 1

Related Questions