ERR:Only functions and some extended stored proced

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)

ERR:Only functions and some extended stored proced

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-12-29 : 08:05:48

Dear All,

I getting error on my function =>"Only functions and some extended stored procedures can be executed from within a function.". Anyone can assist me??


Alter FUNCTION [dbo].[UplineTreeByMemberPPV] (@sMember AS NVARCHAR(50),@mtableName AS NVARCHAR(20))
--select * from [UplineTreeByMemberPPV]('KSA0039802','MPH200911')
--select * from memberships where membercode='KSA0039802'
--select * from memberships where membershipid='20005'

RETURNS
@retFindReports TABLE (
MemberCode NVARCHAR(50) NOT NULL,
MName NVARCHAR(200) NULL,
DateJoin nvarchar(20) Null,
Ranks nvarchar(20) Null,
Upline nvarchar(20) Null,
PPV nvarchar(20) Null,
pgpv nvarchar(20) Null,
lev int)

AS            
BEGIN 
DECLARE @temp TABLE(  
parent varchar(20), 
uplines varchar(20), 
lev int
)
declare @lvl int
select @lvl = 0
Declare @sSql AS NVarChar(4000) 

insert @temp (parent, uplines, lev)
select m.membershipid, m.uplineid, @lvl
from memberships m
where m.membercode =@sMember


while @@rowcount > 0
begin
set @lvl = @lvl + 1


insert @temp (parent, uplines, lev)
select h.membershipid,h.uplineid, @lvl
from @temp t
join memberships h on h.membershipid= t.uplines and t.lev = @lvl-1


end


Set @sSql = ' INSERT @retFindReports '
Set @sSql = @sSql +' select * from '
Set @sSql = @sSql +' ( '
Set @sSql = @sSql +' 	select Upline=mm.MemberCode,B.Givenname,ms.DateJoin,Ranks,B.MemberCode,mb.ppv,mb.pgpv '
Set @sSql = @sSql +' 	from '
Set @sSql = @sSql +' 	( '
Set @sSql = @sSql +' 	select parent,ab.Givenname,m.membercode,Ranks=ConfigValue,lev from @temp t '
Set @sSql = @sSql +' 	inner join memberships m on m.MembershipID=t.uplines '
Set @sSql = @sSql +' 	inner join memberaddresses mab on mab.memberid=m.memberid and addresstype=''default'' '
Set @sSql = @sSql +' 	inner join addressbook ab on ab.addressid=mab.addressid '
Set @sSql = @sSql +' 	left join geoconfig g on g.configID=m.ranklookupid and configsource=''Rank'' '
Set @sSql = @sSql +' 	)B  '
Set @sSql = @sSql +' 	inner join memberships mm on mm.MembershipID=B.parent '
Set @sSql = @sSql +' 	Inner join '+@mtableName+' mb on mb.membercode = ms.membercode  '
Set @sSql = @sSql +' ) C '
Set @sSql = @sSql +' order by C.lev asc '


Exec SP_EXECUTESQL @sSql  

RETURN

END      

GO

Thank you.

Regards,
Micheale

Sachin.Nand

2937 Posts

Posted - 2009-12-29 : 08:17:23

Why are you using dynamic SQL?I dont see any reason for using it.
Before removing it chnage this Exec SP_EXECUTESQL @sSql to Exec(@sSql) and try.

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 09:13:32

Sorry but there is no way to use dynamic sql in a function.

Functions (at least in 2005) CANNOT CHANGE DATA and one of the restrictions imposed on them to guarantee this is that you can't call extrenal stored procs (most of them) and you can't issue dynamic sql calls.

The code looks to return a result set. Why not just rewrite it as a stored procedure?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 09:16:17

quote:
Originally posted by Idera

Why are you using dynamic SQL?I dont see any reason for using it.
Before removing it chnage this Exec SP_EXECUTESQL @sSql to Exec(@sSql) and try.

PBUH

I imagine because it joins to a passed in table at execution time.

micnie_2020 -- this doesn't look like a good idea. I think you should check out the following link. It's the best advice for dynamic sql I've come across.
http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-12-29 : 20:11:24

Thank you for all.

I have done in Stored Procdures. Would like to share the output with all reader.

CREATE PROC SP_UplineTreeByMemberPPV (@sMember AS NVARCHAR(50),@mtableName AS NVARCHAR(20))    
--    
--SP_UplineTreeByMemberPPV  'KSA0039802','MPH200911'    
as    
set nocount on    
    
create table #retFindReports  (    
MemberCode NVARCHAR(50) NOT NULL,    
MName NVARCHAR(200) NULL,    
DateJoin nvarchar(20) Null,    
Ranks nvarchar(20) Null,    
Upline nvarchar(20) Null,    
PPV nvarchar(20) Null,    
pgpv nvarchar(20) Null,    
lev int)    
    
create  TABLE #temp (      
parent varchar(20),     
uplines varchar(20),     
lev int    
)    
declare @lvl int    
select @lvl = 0    
Declare @sSql AS NVarChar(4000)     
    
insert #temp (parent, uplines, lev)    
select m.membershipid, m.uplineid, @lvl    
from memberships m    
where m.membercode =@sMember    
    
    
while @@rowcount > 0    
begin    
set @lvl = @lvl + 1    
    
    
insert #temp (parent, uplines, lev)    
select h.membershipid,h.uplineid, @lvl    
from #temp t    
join memberships h on h.membershipid= t.uplines and t.lev = @lvl-1    
    
    
end    
    
    
Set @sSql = ' INSERT INTO #retFindReports '    
Set @sSql = @sSql +' select * from '    
Set @sSql = @sSql +' ( '    
Set @sSql = @sSql +'  select Upline=mm.MemberCode,B.Givenname,mm.DateJoin,Ranks,B.MemberCode,mb.ppv,mb.pgpv,lev '    
Set @sSql = @sSql +'  from '    
Set @sSql = @sSql +'  ( '    
Set @sSql = @sSql +'  select parent,ab.Givenname,m.membercode,Ranks=ConfigValue,lev from #temp t '    
Set @sSql = @sSql +'  inner join memberships m on m.MembershipID=t.uplines '    
Set @sSql = @sSql +'  inner join memberaddresses mab on mab.memberid=m.memberid and addresstype=''default'' '    
Set @sSql = @sSql +'  inner join addressbook ab on ab.addressid=mab.addressid '    
Set @sSql = @sSql +'  left join geoconfig g on g.configID=m.ranklookupid and configsource=''Rank'' '    
Set @sSql = @sSql +'  )B  '    
Set @sSql = @sSql +'  inner join memberships mm on mm.MembershipID=B.parent '    
Set @sSql = @sSql +'  Inner join '+@mtableName+' mb on mb.membercode = mm.membercode  '    
Set @sSql = @sSql +' ) C '    
Set @sSql = @sSql +' order by C.lev asc '    
    
--print @sSql    
EXEC SP_EXECUTESQL @sSql      
select * from #retFindReports    
    
set nocount off

Thank you & God Bless all sqlteam.com reader.

Regards,
Micheale

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-30 : 05:07:02

Don't know if you are still reading this but I think you can ditch both the temp tables and use a CTE instead.

That way you want have query plan recompiles every time you run the code.

Does this work?


CREATE PROC SP_UplineTreeByMemberPPV (@sMember AS NVARCHAR(50),@mtableName AS NVARCHAR(20))    
--    
--SP_UplineTreeByMemberPPV  'KSA0039802','MPH200911'    
AS    
SET NOCOUNT ON      

DECLARE @sSql AS NVARCHAR(MAX)         
    
SET @sSql = '
; WITH CTE (
	[parent]
	, [uplines]
	, [lev]
	)
AS (
	-- Anchor Definition
	SELECT
		m.[membershipId]
		, m.[uplineID]
		, 0
	FROM
		memberships m
	WHERE
		m.memberCode = @sMember

	-- Recursive Definition
	SELECT
		m.membershipId
		, m.uplineID
		, c.lev + 1
	FROM
		memberships m
		JOIN CTE c ON c.uplines = h.membershipId
	)
SELECT
	*
FROM
	(    
	SELECT
		Upline = mm.MemberCode
		, B.Givenname
		, mm.DateJoin
		, Ranks
		, B.MemberCode
		, mb.ppv
		, mb.pgpv
		, lev
	FROM
		(
		SELECT
			parent
			, ab.Givenname
			, m.membercode
			, Ranks=ConfigValue
			, lev
		FROM
			CTE t
			INNER JOIN memberships m ON m.MembershipID=t.uplines
			INNER JOIN memberaddresses mab ON mab.memberid=m.memberid AND addresstype=''dEFAULT''
			INNER JOIN addressbook ab ON ab.addressid=mab.addressid
			LEFT JOIN geoconfig g ON g.configID=m.ranklookupid AND configsource=''RANK''
			)
			B
		INNER JOIN memberships mm ON mm.MembershipID=B.parent
		INNER JOIN ' + QUOTENAME(@mtableName) + ' mb ON mb.membercode = mm.membercode
	) C
ORDER BY
	C.lev ASC'

--print @sSql    
EXEC SP_EXECUTESQL @sSql
    
SET NOCOUNT OFF

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources