quote:

---

Originally posted by jezemine

=http://sommarskog.se/dynamic_sql.html

---

quote:

---

Let's look at an example. Say that in your database, many tables have a column LastUpdated, which holds the time a row last was updated. You want to be able to find out how many rows in each table that were modified at least once during a period. This is not something you run as part of the application, but something you run as a DBA from time to time, so you just keep it as a script that you have a around. Here is how it could look like:

DECLARE @tbl    sysname,
        @sql    nvarchar(4000),
        @params nvarchar(4000),
        @count  int

DECLARE tblcur CURSOR STATIC LOCAL FOR
   SELECT object_name(id) FROM syscolumns WHERE name = 'LastUpdated'
   ORDER  BY 1
OPEN tblcur

WHILE 1 = 1
BEGIN
   FETCH tblcur INTO @tbl
   IF @@fetch_status <> 0
      BREAK

   SELECT @sql =
   N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
   N' WHERE LastUpdated BETWEEN @fromdate AND ' +
   N'                           coalesce(@todate, ''99991231'')'
   SELECT @params = N'@fromdate datetime, ' +
                    N'@todate   datetime = NULL, ' +
                    N'@cnt      int      OUTPUT'
   EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

   PRINT @tbl + ': ' + convert(varchar(10), @count) + ' modified rows.'
END

DEALLOCATE tblcur

.
.
.
You may wonder why I do not pass @tbl as a parameter as well. The answer is that you can't. Dynamic SQL is just like any other SQL. You can't specify a table name through a variable in T-SQL, that's the whole story. Thus, when you need to specify things like table names, column names etc dynamically, you must interpolate them into the string.

---