can we call a procedure from function

Same how you would have called the procedure. Simple Syntax :

Exec usp_MyProc @param1, @param2 ...

:)

www.abhisheksur.com

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

We cannot call Procedure from the Function

We can call the Procedure From the Procedure as
EXEC ProcedureName
@Parameter1 = "Para1"
@Parameter2 = "Para2"
@Parameter3 = @Val OUTPUT (Use when there is Output in the Procedure)

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Lets say you have Procedure 1 that return a hello world string defined like this

Create Proc sp_Proc_1

as

SELECT 'Hello World'

and another Stored Procedure that uses the First Stored Procedure defined like this

Create Proc sp_Proc_2

as

exec sp_Proc_1

and if you run the second sp you will find the Results

 Hello World

Thank you for Posting at dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

Jameschowdare

You can't directly call a sp from a function(udf). Only functions and extended stored procedures can be executed from within a function.

But you can use openquery & sp_executesql within the function. Following is an example for that

CREATE function [dbo].[createstring](@sqlstr nvarchar(32), @sql nvarchar(max))

returns nvarchar(max)

with execute as caller

as begin

        declare @out_sql nvarchar(max)

        return 'select * from openquery('+@sqlstr +', '''+REPLACE(@sql,'''','''''')+''')'

end





declare @a nvarchar(512)

set @a = (select [dbo].[createstring](N'SRV', @query))

exec sp_executesql @a

I hope this is what you are expecting. Mark as answer if it is.

Mark this as answer, if it is.....

With regards
Nishithraj Narayanan

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator

No,You can't call a procedure from a function.

The syntax for calling a procedure inside another procedure is a s follows,

CREATE PROCEDURE <procname>
<parameterlist>
AS
BEGIN

<set of sql statements>

EXEC <procedurename> <listof parameter>

END

Jameschowdare, if this helps please login to Mark As Answer. | Alert Moderator