Feeds:
Posts
Comments

Posts Tagged ‘temp. table’

You can passs temp table inside a dynamic sql and it will process that for you


declare @s varchar(200)

if object_id('tempdb..#t') is not null 
drop table #t

Select 1 as n
into #t

select * from #t -- first time 

set @s = 
	'update #t 
	set n = 2

	insert into #t
	values (3)
	'

exec (@s)

select * from #t -- seccond time

 
Advertisements

Read Full Post »

-- There two ways of using Temp tables and carry out any "Data Manipulation Language (DML)" operations. 

IF OBJECT_ID('tempdb..#a') is not null 
	drop table #a


create table #a
(
	Magic varchar(10) 
)
insert into #a values ('Change Me') 

select * from #a 



------------- Start of Option 1 (using Insert into Exec) ------------------- 
declare @MySQL varchar(100) 
set @MySQL = 'select case when Magic = ''Change Me'' then ''Option 1'' end 
from #a ' 

insert into #a exec(@MySQL)
delete from #a where Magic = 'Change Me' 
select * from #a
------------- End of Option1 ------------------- 


----------- Start of Option 2 ------------------- 
declare @sql varchar(1000) =
'update #a
set Magic = ''Option 2'';'

exec dbo.executeSQL @sql -- This SP just executes the string and nothing else.  The string needs to be executed in another SP

select * from #a

-- >>  Defintion of dbo.executeSQL: 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.executeSQL') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.executeSQL
GO

CREATE PROCEDURE dbo.executeSQL
	@sql varchar(max)
AS
   exec (@sql) 

GO
----------- End of Option 2 ------------------- 

Read Full Post »