в 13:46, 26 мая 2010

2010-05-26T13:46:06Z

Admin: 1 версия

2010-05-26T10:20:06Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL/Transact SQL/Dynamic SQL - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия