SQL Server/T-SQL/Transact SQL/Dynamic SQL

Материал из SQL эксперт
Перейти к: навигация, поиск

Build and call dynamic statement

   <source lang="sql">

8> CREATE TABLE authors( 9> au_id varchar(11), 10> au_lname varchar(40) NOT NULL, 11> au_fname varchar(20) NOT NULL, 12> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 13> address varchar(40) NULL, 14> city varchar(20) NULL, 15> state char(2) NULL, 16> zip char(5) NULL, 17> contract bit NOT NULL 18> ) 19> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 11> GO 1> 2> 3> 4> CREATE PROC add_author 5> @au_id char(11), 6> @au_lname varchar(20), 7> @au_fname varchar(20), 8> @tabname varchar(30) AS 9> 10> BEGIN 11> DECLARE @insert_stmt varchar(255) 12> SELECT @insert_stmt="INSERT " + @tabname + " (au_id, 13~ au_lname, au_fname, contract) VALUES (""" + @au_id + 14> """,""" + @au_lname + """,""" + @au_fname + """, 1)" 15> 16> EXECUTE (@insert_stmt) 17> END 18> GO 1> 2> 3> EXEC add_author "999-99-1234", "Pike", "Neil", "authors" 4> GO 1> 2> 3> drop PROC add_author; 4> GO 1> 2> 3> drop table authors; 4> GO 1> 2>

</source>
   
  


Create dynamic sql from user name and date

   <source lang="sql">

3> 4> DECLARE @sqlstring varchar(100) 5> SELECT @sqlstring = "SELECT * FROM " + LTRIM(user_name()) + 6> CONVERT(char(2),DATEPART(dd, GETDATE())) 7> EXEC (@sqlstring) 8> GO Msg 208, Level 16, State 1, Server J\SQLEXPRESS, Line 1 Invalid object name "dbo17".

</source>
   
  


Use aggregate function in dynamic sql statement

   <source lang="sql">

4> CREATE TABLE Orders ( 5> OrderID int IDENTITY (1, 1) NOT NULL , 6> CustomerID nchar (5) NULL , 7> EmployeeID int NULL , 8> OrderDate datetime NULL , 9> RequiredDate datetime NULL , 10> ShippedDate datetime NULL , 11> ShipVia int NULL , 12> Freight money NULL DEFAULT (0), 13> ShipName nvarchar (40) NULL , 14> ShipAddress nvarchar (60) NULL , 15> ShipCity nvarchar (15) NULL , 16> ShipRegion nvarchar (15) NULL , 17> ShipPostalCode nvarchar (10) NULL , 18> ShipCountry nvarchar (15) NULL 19> ) 20> GO 1> 2> 3> DECLARE 4> @schemaname AS NVARCHAR(128), 5> @tablename AS NVARCHAR(128), 6> @colname AS NVARCHAR(128), 7> @sql AS NVARCHAR(805); 8> 9> SET @schemaname = N"dbo"; 10> SET @tablename = N"Orders"; 11> SET @colname = N"CustomerID"; 12> SET @sql = N"SELECT COUNT(DISTINCT " 13> + QUOTENAME(@colname) + N") FROM " 14> + QUOTENAME(@schemaname) 15> + N"." 16> + QUOTENAME(@tablename) 17> + N";"; 18> 19> EXEC(@sql); 20> GO


         0

1> 2> drop table orders; 3> GO

</source>
   
  


Using a dynamic sql statement in "Insert...select"

   <source lang="sql">

2> 3> CREATE TABLE Orders ( 4> OrderID int IDENTITY (1, 1) NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> 3> DECLARE 4> @schemaname AS NVARCHAR(128), 5> @tablename AS NVARCHAR(128), 6> @colname AS NVARCHAR(128), 7> @sql AS NVARCHAR(805), 8> @cnt AS INT; 9> 10> SET @schemaname = N"dbo"; 11> SET @tablename = N"Orders"; 12> SET @colname = N"CustomerID"; 13> SET @sql = N"SELECT COUNT(DISTINCT " 14> + QUOTENAME(@colname) + N") FROM " 15> + QUOTENAME(@schemaname) 16> + N"." 17> + QUOTENAME(@tablename) 18> + N";"; 19> 20> CREATE TABLE #T(cnt INT); 21> INSERT INTO #T 22> EXEC(@sql); 23> SET @cnt = (SELECT cnt FROM #T); 24> SELECT @cnt; 25> GO


         0

1> 2> DROP TABLE #T; 3> GO 1> 2> drop table orders; 3> GO

</source>