can we call a procedure from function

Posted by Jameschowdare under Sql Server on 2/11/2010 | Views : 9136 | Status : [Member] | Replies : 5
and how to call procedure from another procedure




Responses

Posted by: Abhi2434 on: 2/11/2010 [Member] [Microsoft_MVP] [MVP] Silver

Up
0
Down
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

Posted by: Neeks on: 2/11/2010 [Member] Bronze

Up
0
Down
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

Posted by: Vuyiswamb on: 2/12/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
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

Posted by: Nishithraj on: 2/12/2010 [Member] Bronze

Up
0
Down
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

Posted by: Bunty_18 on: 4/21/2010 [Member] Starter

Up
0
Down
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

Login to post response