动态sql和静态sql有什么区别？ [英] what is difference beteen dynamic sql and static sql ?

问题描述

我谷歌互联网的答案，但它有点令人困惑..



i有两个查询...



1）从客户中选择*，其中cust_id = @ id



2）选择*来自客户凡cust_id = 1





是（1）是动态sql语句和（2）是静态sql语句



或动态不是这个......



请回复...

Thanx提前

i google internet for answer ,, but it's a little confusing ..

i have two query ...

1) select * from customer where cust_id=@id

2) select * from customer where cust_id=1


is (1) is dynamic sql statement and (2) is static sql statement

or dynamic is something other than this....

Pls respond ...
Thanx in advance

推荐答案

动态Sql查询是根据用户输入而改变的事情。

动态查询可以执行使用执行（@SqlStmt）或 exec（@SqlStmt）或 sp_executesql

请查看下面的动态数据透视表。

A Dynamic Sql Query is some thing that changes according to the user input.
Dynamic Queries can be executed using Execute(@SqlStmt) or exec(@SqlStmt) or sp_executesql
Check this below dynamic pivot for example..

Declare @Id varchar(4), @Sql varchar(400),@Cols varchar(40)
Create Table #Temp
		(
		Order_Id Int,
		InventoryId	 Int,
		ItemType Varchar(40),
		total Int
		)

Insert into #Temp
Values(1,5,'Orange',5000),(1,4,'Apple',3000),(2,1,'Mango',3400),(2,5,'Orange',1700)

Set @Id=2

Set @Cols= Stuff((Select Distinct '],['+ItemType From #Temp 
    Where Order_Id=@Id for Xml Path(''),Type).value('.','VARCHAR(Max)'), 1, 2,'')+']'

Set @Sql= 'Select '+@Cols + 'From (Select ItemType,total From #Temp 
    Where Order_Id= '+ @Id +')SourceTable pivot (Sum(total) For ItemType in ('
		   + @Cols + '))PivotTable'
Exec(@Sql)

Drop Table #Temp 

当用户更改Id时，为枢轴选择的项目类型将被更改..

当使用TableNames作为变量等时它会派上用场......

As the user changes the Id the Item Type picked for the pivot will be changed..
It will come in handy while using TableNames as variables etc...

动态SQL通常是在运行时构建整个查询的地方。

因此，动态SQL将涉及将查询插入变量，然后将该变量作为SQL语句执行。

Dynamic SQL is generally where the entire query is built at runtime.
So dynamic SQL would involve inserting the query into a variable then executing the variable as an SQL statement.

这篇关于动态sql和静态sql有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！