How to Call a Stored Proc 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

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 ;

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.

Call a function from Stored Procedure

You can't "create" or define a function inside an SP, no. You'll have to create it on the/a database and then reference the object you created.

If, for some odd reason you don't want the object to remain, you could CREATE it in TempDB, use it, and then DROP it (but this would mean the SP cannot be run by two users simultaneously, as the object would already exist).

Just create it permanently.

Call a stored procedure with OUT parameter within another stored procedure in SAP HANA

I understood from your question, that you have two stored procedures, where one calls the other and both have out parameters. This is a valid constellation. Please find a minimum example, which works on SAP HANA Cloud:

CREATE OR REPLACE PROCEDURE TEST_INNER (OUT i INTEGER)
AS
BEGIN
    SELECT COUNT(*) INTO i FROM DUMMY;
END;

CREATE OR REPLACE PROCEDURE TEST_OUTER (OUT another_i INTEGER)
AS
BEGIN
    DECLARE outparam INTEGER;
    CALL TEST_INNER(:outparam);
    another_i = :outparam;
END;

CALL TEST_OUTER(?);

If this does not solve your issue, you need to provide more details as well as minimal code snippet similar to the one above to reproduce the issue.

---

Related Topics