SQL Server dynamic query (table name or field dynamic), and get the desired return value results

SQL Server dynamic query (table name or field dynamic), and get the desired return value result (Exec sp_execute Key words:

In the preparation of SQL statements or stored procedures, it is inevitable that the table name or a field name in the statement is not sure. In this case, we should consider using the sp_executesql function provided by SQL ,, he can perform the dynamic modification, deletion, and query functions. For detailed explanations of this function, you can view it in the SQL help document. Below I will give a dynamic query column, you are not sure about the table name and the fields you want to query:

Tab:
(Id int) (a numeric (9,2) (B numeric (9,2 ))
========================================================== ========
1 20.30 33.12
2 34.32 22.66
3 45.54 99.19
========================================================== ========

Complete the task:

When you are not sure about the table name and query field, you can obtain the corresponding returned results based on the input table name, field, and query condition:
1. seq Conditions
2. Field name (A or B)

3. Table Name

The function return value type is required to be numeric (9, 2). If the column is returned based on the value of the corresponding field of input parameter 2

The procedure is as follows:

Declare @ tab varchar (10), @ rowname varchar (10), @ seq int
Declare @ SQL nvarchar (1000)

Declare @ v numeric (9, 2)
Set @ rowname = 'a ';
Set @ seq = 2;

Set @ tab = 'tab ';

Set @ SQL = 'select @ A = '+ @ rowname + 'from' + @ tab + 'where id =' + rtrim (@ SEQ)
Exec sp_executesql @ SQL, n' @ a numeric (9,2) output', @ V output

Select @ v

================ Result ==================

34.32

Reminder:

It is very simple to use dynamic table names and fields, but there are certain rules for using dynamic statements to obtain results. The above column sub-names must be known as nvarchar, And then when executing dynamic statements, add n before declaring the variables in the dynamic statement, for example, n' @ a numeric (9, 2) output'