Problem in function

Question

Problem in function

carlos.tapadinhas

Ten Centuries

Points: 1058
More actions
October 21, 2008 at 5:34 am

#192359

Hi to all,
I need a function that run a simple sp, but i need to insert into a table the result... but i dont know how to fix this problem...
ALTER FUNCTION [dbo].[fn_Contact_Types] (@uids NVARCHAR(MAX))
/*
CREATED :
DATE :
DESCRIPTION :
*/
RETURNS @contact_final TABLE( im_contact_uid NVARCHAR(40))
AS
BEGIN
INSERT INTO @contact_final
EXEC [im_get_fn_contact_types] @uids = @uids
RETURN
END
Msg 443, Level 16, State 14, Procedure fn_Contact_Types, Line 13
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.
Can anyone help me please?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

carlos.tapadinhas (10/21/2008)
Hi to all,
I need a function that run a simple sp, but i need to insert into a table the result... but i dont know how to fix this problem...
ALTER FUNCTION [dbo].[fn_Contact_Types] (@uids NVARCHAR(MAX))
/*
CREATED :
DATE :
DESCRIPTION :
*/
RETURNS @contact_final TABLE( im_contact_uid NVARCHAR(40))
AS
BEGIN
INSERT INTO @contact_final
EXEC [im_get_fn_contact_types] @uids = @uids
RETURN
END
Msg 443, Level 16, State 14, Procedure fn_Contact_Types, Line 13
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.
Can anyone help me please?
Thanks

You cannot execute a procedure or dynamic sql inside a function

Madhivanan

Failing to plan is Planning to fail

carlos.tapadinhas Ten Centuries Points: 1058 More actions · Answer 2

Hello,

Thanks for the quick answer...but i created the sp because i need to use temporary tables, and in functions is impossible, so my option was create the sp and then trying to insert into a variable the rows that the execution of sp send me...

I analyse my problem, not this one, but the masterproblem that make me create a function, and this is the only way to solve the problem...

So i need more ideas

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You cannot do this.

Perhaps you could post what you are actually trying to do and someone can help you design a solution that will work.

carlos.tapadinhas Ten Centuries Points: 1058 More actions · Answer 4

Hi Michael, my sp does the next...

CREATE PROCEDURE [dbo].[im_get_fn_contact_types]

@uids NVARCHAR(MAX)

AS

SET NOCOUNT ON

CREATE TABLE #tbl_registers (uid UNIQUEIDENTIFIER)

INSERT INTO #tbl_registers

SELECT data FROM dbo.Split(@uids,',')

DECLARE @query NVARCHAR(MAX)

DECLARE @query_clause NVARCHAR(MAX)

DECLARE @query2 NVARCHAR(MAX)

SET @query2 = ''

SET @query = ' SELECT c.im_contact_uid FROM im_contact c

LEFT JOIN im_contact_type_detail ctd ON ctd.im_contact_uid = c.im_contact_uid

AND ctd.im_contact_type_uid NOT IN (SELECT uid FROM #tbl_registers)

WHERE ctd.im_contact_uid IS NULL '

DECLARE csr CURSOR FOR

SELECT ' AND c.im_contact_uid IN (SELECT im_contact_uid FROM im_contact_type_detail WHERE im_contact_type_uid = ''' + CONVERT(VARCHAR(40),uid) + ''' ) ' FROM #tbl_registers

OPEN csr

FETCH NEXT FROM csr

INTO @query_clause

WHILE @@FETCH_STATUS = 0

BEGIN

SET @query2 = @query2 + ' ' + @query_clause

FETCH NEXT FROM csr

INTO @query_clause

END

CLOSE csr

DEALLOCATE csr

SET @Query = @query + @query2

EXEC (@Query)

SET NOCOUNT OFF

what i have to change to make this in a function

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 5

Hi,

according to Ken Simmons from mssqltips.com you might do something like the following:

SELECT * FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',

'set fmtonly off exec [dbo].[im_get_fn_contact_types]')

http://www.mssqltips.com/tip.asp?tip=1551

regards

karl

Best regards
karl

sgmunson SSC Guru Points: 110640 More actions · Answer 6

I'm not sure I see why you need to bother calling a function to execute a stored procedure. It's just another level of abstraction. Why not just let the stored procedure return the result set? Why the need for a TVF (table-valued function) ?

Steve

(aka smunson)

:):):)

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

PW-201837 SSC-Insane Points: 20805 More actions · Answer 7

It's amazing how much procedural code you can write when you're trying to avoid thinking in sets:

CREATE TABLE #tbl_registers (uid UNIQUEIDENTIFIER)

INSERT INTO #tbl_registers

SELECT data FROM dbo.Split(@uids,',')

SELECT c.im_contact_uid

FROM im_contact c

LEFT JOIN im_contact_type_detail ctd

ON ctd.im_contact_uid = c.im_contact_uid AND

ctd.im_contact_type_uid NOT IN (SELECT uid FROM #tbl_registers)

)

WHERE ctd.im_contact_uid IS NULL

AND c.im_contact_uid IN (

SELECT dtl.im_contact_uid

FROM im_contact_type_detail AS dtl

INNER JOIN #tbl_registers AS t

ON ( t.im_contact_type_uid = CONVERT(VARCHAR(40),t.uid) )

)

That of course is completely un-optimized and could be re-factored further, but the point is, don't write cursors & dynamic SQL to avoid construction of simple, efficient set-based SQL operations.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

carlos.tapadinhas (10/21/2008)
...but i created the sp because i need to use temporary tables, and in functions is impossible, so my option was create the sp and then trying to insert into a variable the rows that the execution of sp send me...

Although it is true that you cannot create or write to temporary tables in a function, you can use table variables just fine.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Problem in function

Cookies on SQLServerCentral