Mega Code Archive
Use aggregate function in dynamic sql statement
<![CDATA[
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
]]>