Calling SP from a function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calling SP from a function

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2007-07-08 : 03:03:14

Hi
When ever i exec a stored procedure inside a function and then select that function i get this error
Only functions and extended stored procedures can be executed from within a function.

example
_________________________________________________

CREATE PROCEDURE [dbo].[prc_test]
AS
BEGIN
SELECT 'Column' , 1
END

_________________________________________________

CREATE FUNCTION dbo.fnc_test()
RETURNS int
AS
BEGIN
EXEC prc_test
return 1
END
_________________________________________________

SELECT dbo.fnc_test()

when i run this line i get this error

Msg 557, Level 16, State 2, Line 1
Only functions and extended stored procedures can be executed from within a function.

_______________________________________________________

How to fix it, turn around it, any ideas?!!
it has to be Stored Procedure and Function

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-08 : 03:14:52

You can't SQL Server does not allow calling of stored procedure from function.

What are you trying to do with the function ?

KH
[spoiler]Time is always against us[/spoiler]

nice123ej
Starting Member

48 Posts

Posted - 2007-07-08 : 07:03:24

I have 2 tables

Products (ProdID int, ProdQty int, ProdEquation varchar(8000))
Customers (CustID int, CustCityID int, CustBusinessID int, CustTarget int)

Products.ProdEquation stores an SQL Statements e.g.
________________________________________________________________________________________________________________________
1001 500 'select count(*) from customers where CustCityID in (17,25,28) and CustTarget>77'
1002 750 'select count(*) from customers where CustCityID not in (31,55) and CustBusinessID=4'
1003 280 'select count(*) from customers where CustTarget >499'
________________________________________________________________________________________________________________________

I want to find all products that has execution of its ProdEquation returns 1 customer or more

so i was thinking of something like this

SELECT ProdID FROM Products WHERE FNC_TOTALCUST(ProdEquation) > 0

hope someone can help me with this
thanks
1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 11:49:44

1) You must turn the function into a stored procedure
2) You must use dynamic SQL

Peter Larsson
Helsingborg, Sweden

nice123ej
Starting Member

48 Posts

Posted - 2007-07-08 : 19:51:54

will you help me to do it?!
how to use SP in the where statement?!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-08 : 21:55:34

quote:
Originally posted by nice123ej

will you help me to do it?!
how to use SP in the where statement?!

What Peter said is don't use Function at all. Write it as a Stored Procedure

KH
[spoiler]Time is always against us[/spoiler]

nice123ej
Starting Member

48 Posts

Posted - 2007-07-08 : 22:32:16

how to use SP in the where statement?
if i wrote this

create procedure prc_TOTALCUST
@SQLString varchar(4000)
as
begin
exec(@SQLString )
end

then i will not be able to use it in the select statement
i.e. I cant do this

SELECT ProdID FROM Products WHERE (EXEC prc_TOTALCUST ProdEquation) > 0

any help?!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 00:34:00

quote:
how to use SP in the where statement?

You can't. You better read this before going into Dynamic SQL path. http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

nice123ej
Starting Member

48 Posts

Posted - 2007-07-09 : 02:20:35

Good Article, but still did not find the answer :(
Anyone has an idea how to do it?!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources