March 11, 2008 at 7:24 am
In below 2 procedures are created
i want build a query with Temparary table in 1st SP and pass into another SP(i.e. 2nd SP) and execute the Query.
When i execute 2nd SP geting success result. If i place any select statement or DML statement on Temp Table in 2nd SP getting the object(i.e. Temp Table) is not found or invalid object name
Any one can guide me. because i want to write same way in my task. Two procedure are required.
---------------1st SP------------------
Create PROCEDURE TableQueryCreation(@SqlQuery nvarchar(1000) output)
AS
BEGIN
if object_id('tempdb.dbo.#temptable') is not null
Drop table #TempTable
set @sqlquery=N'Create Table #TempTable(Col1 Varchar(10),Col2 varchar(12),col3 varchar(1000))'
End
go
---------------------------------------
---------2nd SP-----------------------
Create Procedure TableQueryExecution(@a varchar(1))
As
Begin
Declare @sql nvarchar(1000)
execute TableQueryCreation @sqlquery=@sql output
exec master.dbo.sp_executeSql @sql
select * from #TempTable
--insert into #temptable values('K','i','C')
End
Go
----------------------------------------
Exec TableQueryExecution 'a'
With Regards,
Kishore Kk
March 11, 2008 at 7:36 am
Unless query 1 calls query 2 directly, temp tables created in query 1 are not accessible to query 2. Look up temp tables in Books Online and it will explain the rules on them to you.
(I also don't see anything in query 1 that actually creates the temp table. Just something that assigns a variable. I'm assuming, however, that you left out the part where you execute the value of the variable as dynamic SQL.)
If you want two separate procs to access the same temporary table, you have to create it with ## in front of the name, not just one # symbol. The drawback of this, of course, is that if the first proc is called by another user at the same time, it will error out when it tries to create the table, because it already exists. Even if you handle that, all connections active at the same time will have the same data, since a global temporary table works that way. Again, Books Online will give you the full data on the subject.
If query two is executed by query 1, instead of by a separate connection, it will have access to the temporary table created by query 1. Otherwise, it won't.
Edit: Just read query 2 a bit more carefully and realized your dynamic SQL is being executed there. Have to admit, I don't understand the reasoning behind this construct of creating a dynamic SQL command in a query that does nothing but assign a hard-coded string value to an output variable, and then running "dynamic SQL" with a hard-coded string. The whole purpose of dynamic SQL is that it is not exactly the same query each and every time. Why not just create the temp table with a create table command directly in the second query? No dynamic SQL, no call to a separate proc, just a command in the proc?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2008 at 7:46 am
You can't create local temporary tables using sp_executesql
the reason is that local temp tables (with single # sign) are destroyed when current session is disconnected. sp_executesql creates a new session to run the query you pass and destroys it afterwards.
this doesn't work
exec sp_executesql N'create table #t(a int)'
select * from #t
you can create global temporary tables (with ##) but then you have to consider concurrency - all users have access to such tables.
this works:
exec sp_executesql N'create table ##t(a int)'
select * from ##t
drop table ##t
Piotr
...and your only reply is slàinte mhath
March 11, 2008 at 8:14 am
but my staements "create table ...." and "Select ...." are in same procedure.
in 1st procedure sends 'Create table....' script to 2nd procedure.
these 2 staments are executing in same session.
Regars
Kishore KK
March 11, 2008 at 8:25 am
koteswara kishore K. (3/11/2008)
but my staements "create table ...." and "Select ...." are in same procedure.in 1st procedure sends 'Create table....' script to 2nd procedure.
these 2 staments are executing in same session.
Regars
Kishore KK
Actually, they're not. Since it's being created in dynamic SQL, it only lasts as long as the dynamic SQL command is being run. Once that command finishes, the table goes away.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply