Sp_executesql
Execute statements or batch processing that can be reused multiple times or dynamically generated. Transact-SQL statements or batch processing can contain embedded parameters.
[For details, refer to SQL Server online help]
Here is a specific application. You can use this statement to obtain the content of a field from the table name with a variable. Previously, I used cursors. Example: declare @ tabn varchar (30), @ count int
Declare @ nsql nvarchar (1000) -- Note that the data type must be nvarchar.
Set @ tabn = 'authors '-- use the pubs Database
Set @ nsql = 'select @ COUNT = count (1) from' + @ tabn
Exec sp_executesql @ nsql, n' @ count int output', @ count output
Select @ count
In this way, the value of @ count can be obtained.
Implement with a cursor:
Declare @ tabn varchar (30), @ count int
Declare @ SQL varchar (1000)
Set @ tabn = 'authors '-- use the pubs Database
Set @ SQL = 'Clare cur_1 cursor for select count (1) from' + @ tabn
Exec (@ SQL)
Open cur_1
Fetch next from cur_1 into @ count
Close cur_1
Deallocate cur_1
Select @ count
Because the cursor consumes too much resources, use the cursor as little as possible.