Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10062
  • Last Modified:

How to call a stored procedure from a function

I have a function called check_sitrep_tree_func which calls a stored procedure called "Up" (which just displays something). When i execute the stored proc (exec Up) it works fine. But, when I call it through a function, I get the following error

Server: Msg 2812, Level 16, State 62, Procedure check_sitrep_tree_func, Line 8
Could not find stored procedure 'exec Up'. Any help would be greatly appreciated.

Thanks.

=========
ALTER Function check_sitrep_tree_func(@a varchar(20))
returns varchar(20)
as
BEGIN

     DECLARE @str varchar(2000)
     set @str = 'exec Up'  
     exec @str

return @a
END
0
jl1884
Asked:
jl1884
2 Solutions
 
Scott PletcherSenior DBACommented:
You need parentheses around dynamic SQL execution:

EXEC(@str)

However, you will not be able to use dynamic execution or, I think, even EXEC a SP from a function.
0
 
xxg4813Commented:
So sorry to tell u the sad truth that "Only functions and extended stored procedures can be executed from within a function.".

U may rewrite it using stored procedure.


0
 
Eugene ZCommented:
try this one:

create FUNCTION usd_prod( @prodid varchar(10) )
RETURNS @prodTab TABLE

 (
   
   ProductName   nvarchar(40)
   
  )
AS
BEGIN
DECLARE @InsertString VARCHAR(1500)
Daclare @prodid int
SET @InsertString ='osql -STEST2 -E -Q"exec usp_test ' + @prodid + '" -dNorthwind'
exec master..xp_cmdshell  @InsertString
insert into @prodTab
select * from t
  RETURN
END
/*
SELECT *
FROM usd_prod ('1')
*/
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with above, but wanted only to point out a "small" difference:

DECLARE @str varchar(2000)
     set @str = 'exec Up'  
     exec (@str)

DECLARE @str varchar(2000)
     set @str = 'Up'  
     exec (@str)

DECLARE @str varchar(2000)
     set @str = 'Up'  
     exec @str

Both above will work. in fact, Exec(@sql) assumes a SQL, while exec @sql assumes a stored procedure (name)

Now, regarding your problem, you might transform your stored procedure into a function also, which you should be able to use in a function.

CHeers
0
 
Eugene ZCommented:
--comment:
--remove the declaration of @prodid   in the function:

CREATE FUNCTION usd_prod( @prodid varchar(10) )
RETURNS @prodTab TABLE
            (
              ProductName   nvarchar(40)
             )
AS
BEGIN
DECLARE @InsertString VARCHAR(1500)

SET @InsertString ='osql -STEST2 -E -Q"exec usp_test ' + @prodid + '" -dNorthwind'
exec master..xp_cmdshell  @InsertString
insert into @prodTab
select * from t
 RETURN
END
/*
SELECT *
FROM usd_prod ('1')
*/
0
 
CleanupPingCommented:
jl1884:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
monosodiumgCommented:
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: ScottPletcher http:#8129230, angelIII http:#8135250

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now