INSERT EXEC error - 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)

INSERT EXEC error

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-28 : 09:44:15

I have a view which takes way too long to process so I'm converting it into a table function. Within the function, I have a few temp tables which I'm using. I'm also now trying to pass parameters into the functions to make it more efficient. Here is what I have:

INSERT INTO @tab1
SELECT * FROM t1

What I'd like to do is append a string parameter to the table query. I'm trying the following, but getting a "Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function" error:

DECLARE @cmd = NVARCHAR(4000)
SET @cmd = 'SELECT * FROM t1 WHERE ' + @where

INSERT INTO @tab1
EXEC @cmd

Thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-28 : 10:14:39

Have you tried:
DECLARE @cmd = NVARCHAR(4000)
SET @cmd = 'INSERT INTO @tab1 SELECT * FROM t1 WHERE ' + @where

EXEC @cmd

No, you're never too old to Yak'n'Roll if you're too young to die.

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-28 : 10:19:45

I just tried that and now I get the following error:

Msg 203, Level 16, State 2, Line 30
The name 'insert into @tab1 SELECT DISTINCT ......' is not a valid identifier.

upzone
Starting Member

2 Posts

Posted - 2009-09-28 : 10:35:29

Pls notice that @tab1 is one variable, you must use string concat.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 10:45:06

The error is that you haven't put @cmd inside paranthesises...

declare @tab1 table (i int)

DECLARE @cmd VARCHAR(4000)
SET @cmd = 'SELECT top 50 number FROM tallynumber'

INSERT INTO @tab1
EXEC (@cmd)

select * from @tab1

N 56°04'39.26"
E 12°55'05.63"

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-28 : 10:52:44

declare @tab1 TABLE
(
PRTCPNT_ID nvarchar(50),
NBR_SSN_RCPT nvarchar(50),
[FIRST] nvarchar(50),
[LAST] nvarchar(50),
CDE_PROJ nvarchar(50),
CDE_PROVIDER nvarchar(50),
DTE_PROJ_REFL nvarchar(50),
DTE_PROJ_BEGIN nvarchar(50),
DTE_PROJ_END nvarchar(50),
CDE_PROJ_TERM nvarchar(50),
JobsiteCode nvarchar(50),
FS_ONLY nvarchar(50)
)

declare @cmd varchar(4000)
set @cmd = 'SELECT DISTINCT ....'
insert into @tab1
exec (@cmd)

I still get the error on the insert into line...
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 10:55:06

Well, we have to see the full query then...

N 56°04'39.26"
E 12°55'05.63"

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-28 : 10:59:19

declare @cmd varchar(4000)
set @cmd = 'SELECT DISTINCT dbo.MGS_FY_RESULT_ENROLLMENT_TBL.PRTCPNT_ID, dbo.MGS_FY_RESULT_ENROLLMENT_TBL.NBR_SSN_RCPT,
dbo.MGS_FY_RESULT_ENROLLMENT_TBL.FIRST, dbo.MGS_FY_RESULT_ENROLLMENT_TBL.LAST,
dbo.MGS_FY_RESULT_ENROLLMENT_TBL.CDE_PROJ, dbo.MGS_FY_RESULT_ENROLLMENT_TBL.CDE_PROVIDER,
dbo.MGS_FY_RESULT_ENROLLMENT_TBL.DTE_PROJ_REFL, dbo.MGS_FY_RESULT_ENROLLMENT_TBL.DTE_PROJ_BEGIN,
dbo.MGS_FY_RESULT_ENROLLMENT_TBL.DTE_PROJ_END, dbo.MGS_FY_RESULT_ENROLLMENT_TBL.CDE_PROJ_TERM,
dbo.MGS_FY_RESULT_ENROLLMENT_TBL.JobsiteCode, dbo.MGS_FY_RESULT_ENROLLMENT_TBL.FS_ONLY
FROM dbo.MGS_FY_RESULT_SUBPROJECTS_TBL INNER JOIN
dbo.MGS_FY_RESULT_ENROLLMENT_TBL ON
dbo.MGS_FY_RESULT_SUBPROJECTS_TBL.PRTCPNT_ID = dbo.MGS_FY_RESULT_ENROLLMENT_TBL.PRTCPNT_ID AND
dbo.MGS_FY_RESULT_SUBPROJECTS_TBL.DTE_PROJ_BEGIN = dbo.MGS_FY_RESULT_ENROLLMENT_TBL.DTE_PROJ_BEGIN
WHERE dbo.MGS_FY_RESULT_SUBPROJECTS_TBL.DTE_BEGIN_PROJ_SUB < dbo.Fiscal_Year_Begin()'
insert into @tab1
exec (@cmd)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-28 : 11:01:56

Is this a statement inside a FUNCTION?

07/06/2007 12:35:18 spirit1 wrote:
you can't use dynmaic sql in a function. period.

No, you're never too old to Yak'n'Roll if you're too young to die.

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-28 : 11:03:56

Yes, all of this code is part of a table-valued function.

upzone
Starting Member

2 Posts

Posted - 2009-09-28 : 12:09:51

Yes, don't forget ( and )

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-28 : 14:32:02

So what is the issue? Am I unable to do what I'm trying to through SQL?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-28 : 14:45:58

Your issue is that dynamic SQL is not allowed in functions, so yes you can't do that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-30 : 08:43:32

Can you offer any suggestions how to resolve my issue? I'm trying to pass the WHERE string into a SELECT query to insert into a table variable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 08:46:53

How does a typical WHERE clause look like?

N 56°04'39.26"
E 12°55'05.63"

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-30 : 08:56:06

It can have any number of conditions. That is the whole problem. If it was one or two conditions, I'd pass them as parameters into the function, but that's not the case.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 09:01:43

Yes it is.

WHERE (Col1 = @Param1 OR @Param1 IS NULL)
AND (Col2 = @Param2 OR @Param2 IS NULL)

N 56°04'39.26"
E 12°55'05.63"

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-30 : 09:09:05

So if I didn't want to constrain a certain column and passed NULL, "WHERE (Col1 = NULL OR NULL IS NULL)" wouldn't cause an error?

Also, what happens if I need Col1 to equal 2 things? Or 3 things? etc?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 09:23:38

When using parametrized queries, the WHERE clause is never evaluated as a string. Is just compares values.
If you need some columns be be checked for a number of possibilities, use a string split function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

WHERE (Col1 = @Param1 OR @Param1 IS NULL)
AND Col2 IN (SELECT Data FROM dbo.fnSplitString(@Param2, ','))

N 56°04'39.26"
E 12°55'05.63"

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-09-30 : 09:25:52

What about if I want this:

Col1 LIKE 'X%' AND Col1 NOT LIKE 'Y%Z'?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 09:50:41

I'd rewrite the logic.

Col1 LIKE 'X%' AND Col1 NOT LIKE 'Y%Z'?
equals
Col1 LIKE 'X%'

If you meant

Col1 LIKE 'X%' AND Col1 NOT LIKE 'X%Z'?
equals
Col1 LIKE 'X%[^Z]'

FROM Table1 AS t1
WHERE (t1.Col1 LIKE @Param1 OR @Param1 IS NULL)
AND EXISTS (SELECT * FROM dbo.fnSplitString(@Param2, ',') AS d WHERE t1.Col1 LIKE d.Data)

N 56°04'39.26"
E 12°55'05.63"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources