Extended SQL flexibility-static T_ SQL, dynamic SQL, and sqlt_ SQL

Extended SQL flexibility-static T_ SQL, dynamic SQL, and sqlt_ SQL

In fact, I think this blog is best written in web-based application development, because at present we seldom implement the sorting and filtering functions on the client side, and many development uses a lightweight two-layer model, therefore, there is a lack of dedicated data caching and filtering services. Some may say that we can create stored procedures or nested complex control process blocks? However, I would like to say that these solutions can only solve some problems, but they bring about much more problems than I want to solve.

Problem

This problem occurs when we work with the data center to concatenate strings through the business logic layer to optimize the D-Layer Code. However, this problem occurs: I have written all the fields in the update table, but in fact, the input object value is generally only a few of the fields, which requires the flexibility of our SQL.

Solution Process

I am responsible for layer D in cooperation, but I still have no idea how to solve this problem. Later, after a long query of materials and questions from the Forum, I learned about static SQL and dynamic SQL.

1. Static T_ SQL solution:

update card set cardName=isnull(@cardName,cardName),date=isnull(@date,date),Time=isnull(@Time,time),status=isnull(@status,status) where cardID=@cardID

From the above code, we can see that you only need to add an isnull () to judge. Of course, the method is not just one, for example, the control flow judgment, but the control flow judgment still has many potential problems, you can also use the case expression, I think this method is used to optimize the control flow so that if cardname is empty, this field will keep the original data and will not be changed.

The preceding statement has poor readability.

You can use the case expression to optimize it:

update card set cardName=case when @cardName is null then cardName else @cardName end,date=case when @date is null then date else @date end,Time=case when @Time is null then Time else @Time end,status=case when @status is null then status else @status endwhere cardID=@cardID

In this way, the CASE expression is used to replace the dynamic SQL statement in SQL Server.

2. dynamic SQL Solution

Principle: concatenated SQL statements are stored.

Note:

 

Therefore, dynamic SQL is generally not recommended.

exec sp_executesql N'update card set cardName=isnull(@cardName,cardName),date=isnull(@date,date),Time=isnull(@Time,Time),status=isnull(@status,status) where cardID=@cardID',N'@cardName varchar(100),@date date,@Time time,@status varchar(30),@cardID int','test',null,null,null,'001'

Then exec (SQL) is enough.

Perception

I don't know much about dynamic SQL, so I don't dare to explain it easily. I will write down my understanding later; second, this is a new kind of knowledge for me. It takes time to digest and absorb it. More discussions are required to determine whether to use dynamic SQL.

In addition, I had a new understanding of SQL Server through this issue. I thought I had learned the view and the trigger was almost done, however, even a small SQL statement has such a college question, but I think it is very interesting because of the rich nature that I feel new about the knowledge I have learned every day. I don't think SQL server will finish learning. It will always be learning, and it will always be learning.