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

Source: Internet
Author: User
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:

(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 ==================



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'


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.