exec a query in stored proc - 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 2000 Forums

SQL Server Development (2000)

exec a query in stored proc

Author

Topic

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-11 : 11:05:32

Hi all,
May be a quite stupid q.

In my situtaion, i would like to dyanmically general a query, then exec it and then put result into table variable.

How to do the code in "< >"?

Thx

*************************

declare @pquery nvarchar(4000)
declare @PrimaryResult table (
NewPK int identity(1,1),
JobID int,
Score float
)

select @pquery = 'select JobID from Jobs'

Insert into @PrimaryResult
<? How to run the query ?>

be a hardworking people!!

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-08-11 : 11:13:31

exec (@pquery)

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-11 : 11:22:51

Error message

"EXECUTE cannot be used as a source when inserting into a table variable."

Any idea?

be a hardworking people!!

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-08-11 : 13:32:45

wrap all the statments into the string and after that execute it with exec()

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-11 : 17:01:51

Thx, but can you explain how to "wrap" ?

be a hardworking people!!

nadejda18
Starting Member

7 Posts

Posted - 2005-08-11 : 18:17:11

You can "create the table variable and perform all processing inside the EXEC statement" - it's an advice from Microsoft (http://support.microsoft.com/default.aspx?scid=kb;en-us;305977).
Or you can use a temporary table instead of a variable.

anitha
Starting Member

2 Posts

Posted - 2005-08-12 : 13:19:56

declare @p nvarchar (255) ,@x nvarchar(255) ,@com nvarchar(255)

set @p = 'select top 1* from sqlforum'

insert into temp exec(@p)

i think you could write a stored proc in <>
I am a new Sql user , I hope my answer does not sound stupid ..ha!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-13 : 01:06:17

If the target table is table variable then it not possible
It is better to avoid this kind of dynamic queries
Read more on Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

>>I am a new Sql user , I hope my answer does not sound stupid ..ha!!

I hope you are not new to SQL

Madhivanan

Failing to plan is Planning to fail

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-14 : 11:47:33

Thx !

be a hardworking people!!

scmarcel
Starting Member

1 Post

Posted - 2006-07-26 : 10:19:08

I was haveing the same problem and had to use a temp table because a table variable would not work.
I know your query is simpler but this is what I came up with:

declare @SomeVar int
set @SomeVar = SomeValue

create table #tempTable (Col1 int,Col2r int,Col3 int)

insert #tempTable
exec StoredProcedure @SomeVar

select * from #tempTable
union
select Col1, Col2, Col3
from SomeTable
where SomeCol = @SomeVar

drop table #tempTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources