SQL Server : Only functions and certain extended stored procedures can be executed from a function

Question

I am an Oracle expert but not SQL Server, I try to translate my code to use in SQL Server.

My Oracle code :

create or replace
FUNCTION CheckAndCreateTable
(
  TBL_NAME IN VARCHAR2 ,
  STMNT in varchar2
) RETURN number AS

 Cnt Pls_Integer;

begin
 select COUNT (TABLE_NAME) into CNT from USER_TABLES where UPPER(TABLE_NAME)= UPPER(TBL_NAME);
      if (CNT = 0 )      then
          execute immediate STMNT;
          DBMS_OUTPUT.PUT_LINE ('Création de la table ' || TBL_NAME );
          return 1;
        else
           DBMS_OUTPUT.PUT_LINE ('La Table ' || TBL_NAME || ' existe déjà');
            return 0;
      End If ;
  RETURN NULL;
END CheckAndCreateTable;

I have translated it to:

if object_id('CheckAndCreateTable', 'fn') is not null
    drop function CheckAndCreateTable;
go

CREATE FUNCTION CheckAndCreateTable
    (@TBL_NAME VARCHAR(4000),
     @STMNT varchar(4000)) 
RETURNS float 
AS
BEGIN
    DECLARE @Cnt integer;

    SELECT @CNT = COUNT(TABLE_NAME) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = @TBL_NAME ;

    IF (@Cnt = 0)      
    BEGIN
        EXECUTE sp_executesql @STMNT;

        RETURN 1;
    END
    ELSE BEGIN
        RETURN 0;
    END

    RETURN NULL;
END ;

I try to execute this in SQL Server:

SELECT CheckAndCreateTable('MyTable', 'select * from DUAL') 
FROM DUAL;

It's working in SQL Developer for ORACLE, but not in SQL Server.

Only functions and certain extended stored procedures can be executed from a function

I try create procedure too in ORACLE, ok is working

create or replace
PROCEDURE STAT_CREERTABLESSTRUCT
as
    STMNT varchar2(32767 );
    RET   number;
begin
    DBMS_OUTPUT.PUT_LINE('=====================================================');
    DBMS_OUTPUT.PUT_LINE(' STAT_CREERTABLESSTRUCT ');
    STMNT := 'CREATE TABLE PcaBat
    (
        CodBat      VARCHAR2(12) NOT NULL,
        LibBat      VARCHAR2(42),
        CodEtb      VARCHAR2(12),
        Dispo       CHAR(1)
    )';

And for SQL Server

PROCEDURE STAT_CREERTABLESSTRUCT
AS
BEGIN
    DECLARE @STMNT varchar(max);
    DECLARE @RET   float;

    SET NOCOUNT ON;

    PRINT '=====================================================';
    PRINT ' STAT_CREERTABLESSTRUCT ';

    SET @STMNT = 'CREATE TABLE PcaBat
    (
        CodBat      VARCHAR2(12) NOT NULL,
        LibBat      VARCHAR2(42),
        CodEtb      VARCHAR2(12),
        Dispo       CHAR(1)
    )';

    SET @RET =  DBO.CHECKANDCREATETABLE('PcaBat', @STMNT);

END;

but code is not working in SQL Server:

exec STAT_CREERTABLESSTRUCT

I always get the same message error

Only functions and certain extended stored procedures can be executed from a function.

It seems in SQL Server you can't execute SQL code from function or call procedure...

Is this true? How can you do this?

The error is telling you the truth. There are only a few stored procedures you can use inside a function, `sp_executesql` is not one of them. The fact that you want to run a dynamic statemeny inside a function feels like this is an [XY Problem](http://xyproblem.info). — Thom A, Apr 19 '20 at 16:56

score 1 · Accepted Answer · answered Apr 19 '20 at 19:25

In this simple case, where a return value is 0 or 1 (or any integer), you can also use CREATE PROCEDURE instead of CREATE FUNCTION.

The return value of the procedure's RETURN statement can be read using the EXEC @result = mySP syntax.

From MS docs section "Returning Data Using a Return Code":

DECLARE @result int;  
EXECUTE @result = my_proc;

score -1 · Answer 2 · answered Apr 19 '20 at 18:48

-1

Bonjour, In SQL Sever you can call a Function in a Stored Procedure , but not a Stored Procedure Inside a Function.

Execute Stored Procedure from a Function

Merci, Kalyan

answered Apr 19 '20 at 18:48

Allam Kalyana

21
5

SQL Server : Only functions and certain extended stored procedures can be executed from a function

2 Answers2