Day: December 9, 2014
-
Temp table Local / Global
— Create TEMP Table Local — Disappears when windows of query is closed. CREATE TABLE #tmp_table ( id INT, NAME VARCHAR(20) ) SELECT 1 AS ID, ‘John’ AS NAME — Create And Insert Into Temp Table INSERT INTO #tmp_table SELECT * FROM #tmp_table — Temp Table Global CREATE TABLE ##tmp_global_table — Create TEMP Table ( id INT, NAME VARCHAR(20) ) INSERT INTO ##tmp_global_table VALUES (1,’One’) SELECT * FROM ##tmp_global_table
-
Dynamic SQL – Demo of EXEC and sp_executesql
This demo s pretty self explained, you just copy and paste it into SQL Management studio: DECLARE @cmd NVARCHAR(30) = ‘SELECT * FROM Person.Address’ EXEC (@cmd) go — Using Dynamic SQL With Parameter DECLARE @cmd_param VARCHAR = 1 DECLARE @cmd VARCHAR(100) = N’Select * from employees where employeeid = ‘ + @cmd_param EXEC (@cmd) — DDL – Create Table DECLARE @cmd VARCHAR(100) SET @cmd = ‘Create table TBL (col1 int)’ EXEC (@cmd) SELECT * FROM tbl; — DROP DROP TABLE tbl — sp_executesql — DECLARE @cmd NVARCHAR(100) SET @cmd = ‘SELECT * FROM Person.Address where AddressID = @1 and PostalCode = @2′ EXEC Sp_executesql @cmd, N’@1 int, @2 nvarchar(15)’, 1, ‘98011’ go