Dynamic SQL (stitching)

Q1: What is dynamic SQL ?

A1: The first is the SQL statement, which is based on the conditions to stitch the SQL

Q2: Why use dynamic SQL?

A2: Because an OR occurs in a conditional where , the index is not used, resulting in a significant difference in efficiency.

For example:1,2,

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/73/58/wKiom1X6iFfTDVLYAAGJJWK1tFk580.jpg "title=" Picture 1.png "alt=" Wkiom1x6ifftdvlyaagjjwk1tfk580.jpg "/>

Diagram ( 1 )

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/73/55/wKioL1X6ipjirzWlAAHHZ3bYiHc483.jpg "title=" Picture 2.png "alt=" Wkiol1x6ipjirzwlaahhz3byihc483.jpg "/>

Diagram ( 2 )

Q3: How do I use dynamic SQL?

A3:

Stored Procedures proc_test is not used in splicing:

CREATE PROC proc_test (@ name varchar (10), @ Course varchar (10), @ score int,@ total int output) asbegin--take total select @ Total =count (1) from Testwhere (@ name = ' or name [email protected] name) and (@ course = ' or course [email protected] course) and (@ score = ' or ' score >[email Protec Ted]--FETCH data SELECT * FROM Testwhere (@ name = ' or name [email protected] name) and (@ course = ' or course [email protected] course) and (@ Score =0 or fraction >[email protected] score) END

after stitching can be changed into Proc_test1

Create proc proc_test1 (@ name  varchar (10), @ Course  varchar (10), @ score  int,@ Total  int  Output) asbegindeclare  @sqls1  nvarchar (max) declare  @sqls2  nvarchar (max) declare  @sqls3  nvarchar (max) set  @sqls1 = ' from testwhere id>1 '/* If there are no conditions that must be set, you need to add 1=1*/if @ name <> '/* parameter filter */begin  set @[email protected]+ '  AND  name = ' [email protected] name + ' '/*and front to add space */endif @ course <> ' begin  set @[email protected]+ '  AND  course = ' [email protected] Course + "* * for the @ course with quotation marks, special treatment. Three quotation marks: one of them is quoted, used for string representations, and the other two are escape characters in quotation marks. */endif @ score >0begin  set @[email protected]+ '  AND  score >= ' +cast (@ score   AS&NBSP;NVARCHAR)/* Non-string argument to convert to string */end--total set  @sqls2 = ' select @ total =count (1) ' [email protected]-- Fetch data set  @sqls3 = ' select *  ' [email protected]exec sp_executesql  @sqls2, N ' @ total   Int out ', @ Total  out/* n required before statement */exec (@sqls3)  end 

Execution Plan :

EXEC proc_test ', ', 0,0exec proc_test1 ', ', 0,0

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/73/58/wKiom1X6iG6z6CI_AAF-45e6zIE662.jpg "title=" Picture 3.png "alt=" Wkiom1x6ig6z6ci_aaf-45e6zie662.jpg "/>

This article is from the "Sukun" blog, make sure to keep this source http://sukunwu.blog.51cto.com/10453116/1695774

Dynamic SQL (stitching)