Execute Stored Procedure from a Function

Execute Stored Procedure from a Function

EDIT: I haven't tried this, so I can't vouch for it! And you already know you shouldn't be doing this, so please don't do it. BUT...

Try looking here: http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx

The key bit is this bit which I have attempted to tweak for your purposes:

DECLARE @SQL varchar(500)

SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec dbName..sprocName "'

EXEC master..xp_cmdshell @SQL

How to execute procedure in function SQL Server

It seems you want to execute stored procedure someProcedure from within function test. That's also what you explicitly ask.

That is not allowed, so you cannot do that. Functions in SQL Server are never allowed to have side effects.

As stated in Microsoft's documentation regarding User-Defined Functions:

The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

Note

If a CREATE FUNCTION statement produces side effects against resources that do not exist when the CREATE FUNCTION statement is issued, SQL Server executes the statement. However, SQL Server does not execute the function when it is invoked.

So it seems you have to find another strategy to do what you want.

Call a Stored Procedure or Function from Dynamic SQL - with Nullable Parameter

Just escape the NULL value with an explicit literal NULL, making sure that the quotes are only included when the value is not NULL.

DECLARE @myParameter VARCHAR(10) = 'ABC'

DECLARE @dynamicQuery VARCHAR(MAX)

SET @dynamicQuery = 
   'select * from [qlik].udf_getStatistic(' + ISNULL('''' + @myParameter + '''', 'NULL') + ')'


SELECT @dynamicQuery -- select * from [qlik].udf_getStatistic('ABC')

SET @myParameter = NULL

SET @dynamicQuery = 
   'select * from [qlik].udf_getStatistic(' + ISNULL('''' + @myParameter + '''', 'NULL') + ')'

SELECT @dynamicQuery -- select * from [qlik].udf_getStatistic(NULL)

You might want to escape additional single quotes that might be on your variable, replacing them with double single quotes, so it doesn't break your dynamic build.

Execute a stored procedure inside user defined function

That is not possible. Stored Procedures can be invoked from a client only, not from a user defined function.

You cannot chain multiple Stored procedure executions in the same transactional scope either.

The only alternative, depending on what your stored procedures do, would be to use Transactional Batch, so define a group of item operations as a single transaction unit, the limitation is that these are operations that cannot be feedback from one another (you cannot put a Read operation with a condition to perform a Write operation in the same batch, batches do not support conditions), so it really depends on the logic of your stored procedures. Transactional batch cannot be invoked from user defined functions, only from the client.

MySQL Call procedure inside function for count

Yes, a MySQL FUNCTION can call a MySQL PROCEDURE.

But... the operations the procedure performs will be limited to the operations allowed by a function. (We can't use a procedure to workaround the limitations placed on a function.)

"is not working" is so nebulously vague as to be practically useless in debugging the issue. What exact behavior is being observed?

My suspicion is that the SQL statements shown are failing, because there is no override for the default statement delimiter.

Also, parent(11) is not a valid datatype.

Be aware that when an identifier for a column in a SQL statement in a MySQL stored program matches an identifier used for an argument or local variable, MySQL follows a rule about which (the column name or the variable) that is being referenced.

Best practice is to adopt a naming convention for arguments and local variables that do not match column names, and to qualify all column references with a table name or table alias.

Personally, I use a prefix for arguments and local variables (a for argument, l for local, followed by a datatype i for integer, d for date/datetime, n for decimal, ...

DELIMITER $$

DROP PROCEDURE IF EXISTS relatives$$

CREATE PROCEDURE relatives(IN ai_parent INT(11),OUT ai_counted INT(11))
BEGIN
  SELECT COUNT(*)
    INTO ai_counted 
    FROM category c
   WHERE c.related = ai_parent
  ;
END$$

DROP FUNCTION IF EXISTS relatives_count$$

CREATE FUNCTION relatives_count(ai_parent INT(11))
RETURNS INT(11)
BEGIN
   DECLARE li_counted INT(11);
   CALL relatives(ai_parent,li_counted);
   RETURN li_counted;
END$$

DELIMITER ;

---

Please identify the exact behavior you observe. Error message when creating the procedure? Error message when executing the function? Unexpected behavior. That's much more precise and informative than telling us something "is not working".

MySQL Call a Stored Procedure inside a Function

Thank you @Barmar for the answer. Yes I needed to use a cursor in my procedure to declare my function appropriately.

drop procedure if exists build_regionUtil_proc;
delimiter //
create procedure build_regionUtil_proc(in search int, inout result int)
    begin
        declare v_finished integer default 0;
        declare v_list int default 0;
        declare region_cursor cursor for
            select t1.emp_id from (
            select employees.emp_id from branch
            inner join department
            on department.br_id = branch.br_id
            inner join employees
            on employees.dep_id = department.dep_id
            where branch.reg_id = search) as t1;
        declare continue handler
            for not found set v_finished = 1;
        open region_cursor;
        get_results: loop
            fetch region_cursor into v_list;
            if v_finished = 1 then leave get_results;
            end if;
            set result = result + 1;
        end loop get_results;
        close region_cursor;
    end //
delimiter ;

---

Related Topics