October 21, 2008 at 5:34 am
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
October 21, 2008 at 5:42 am
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
Failing to plan is Planning to fail
October 21, 2008 at 5:53 am
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 🙂
October 21, 2008 at 6:07 am
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.
October 21, 2008 at 7:57 am
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
October 22, 2008 at 5:18 am
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
October 23, 2008 at 12:43 pm
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)
October 23, 2008 at 1:00 pm
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.
October 23, 2008 at 1:20 pm
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]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply