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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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')
*/
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
--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')
*/
--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.
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
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
U may rewrite it using stored procedure.