Link to home
Start Free TrialLog in
Avatar of jl1884
jl1884

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of xxg4813
xxg4813

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.


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')
*/
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
--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')
*/
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.
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