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

Build and call dynamic statement

 

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>

Create dynamic sql from user name and date

 
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".

Use aggregate function in dynamic sql statement

 

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

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

 
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