using INSERT INTO with EXEC() - 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)

using INSERT INTO with EXEC()

Author

Topic

cesko45
Starting Member

4 Posts

Posted - 2008-12-03 : 10:46:54

I have a dymanic query that i can only get to run using the EXEC() function. i'm trying to insert the results into a memory table. SQL gives a syntax error on the WITH line - is there a better way?

DECLARE @searchText varchar(100),
@tmpSQL varchar(1000)

DECLARE @myTable table (
ID int IDENTITY(1,1) NOT NULL,
itemSYSid int,
itemRank int )

BEGIN

SET @searchText = 'test'
SET @tmpSQL = 'and ( ref01 = ''test'' ) '

INSERT INTO @myTable
EXEC(' select itemSYSid, rank
from catalog WITH (NOLOCK)
where priceType = 1 ' + @tmpSQL + ' order by rank ' )

WITH ( itemSYSid int '@itemSYSid'
itemRank int '@rank' )

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 10:49:04

Are you using Microsoft SQL Server?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 11:04:14

whats the purpose of dynamic sql here?

cesko45
Starting Member

4 Posts

Posted - 2008-12-03 : 13:09:50

quote:
Originally posted by Peso

Are you using Microsoft SQL Server?

thanks for the fast response
yes - I'm using MS SQL 2005

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

cesko45
Starting Member

4 Posts

Posted - 2008-12-03 : 13:13:48

quote:
Originally posted by visakh16

whats the purpose of dynamic sql here?

This is part of a stored procedure. I had to go with dynamic SQL and EXEC() because i write part of the SQL statement in another app and pass it to the SP as a variable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 13:17:36

quote:
Originally posted by cesko45

quote:
Originally posted by visakh16

whats the purpose of dynamic sql here?

This is part of a stored procedure. I had to go with dynamic SQL and EXEC() because i write part of the SQL statement in another app and pass it to the SP as a variable

Not at all a good approach. this is prone to sql injection attack.

cesko45
Starting Member

4 Posts

Posted - 2008-12-03 : 15:06:02

passing SQL is the only way i can make this thing work. the SQL transferred is private in the program, there is no way for a user to alter it.
I figured it out (finally :) I don't need the WITH statement- i tried this before and didn't get a return, but it was because of something else wrong in the SP

DECLARE @searchText varchar(100),
@tmpSQL varchar(1000)

DECLARE @myTable table (
ID int IDENTITY(1,1) NOT NULL,
itemSYSid int,
itemRank int )

BEGIN

SET @searchText = 'test'
SET @tmpSQL = 'and ( ref01 = ''test'' ) '

INSERT INTO @myTable
EXEC(' select itemSYSid, rank
from catalog WITH (NOLOCK)
where priceType = 1 ' + @tmpSQL + ' order by rank ' )

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources