动态sql语句基本语法

1 普通sql语句可以用exec执行，如：

SELECT * FROM video

EXEC ('SELECT * FROM video')

EXEC sp_executesql N'SELECT * FROM video' --字符串前一定要加N

2 字段名，表名，数据库名之类作为变量时，必须用动态sql

DECLARE @fname NVARCHAR(20)
SET @fname = 'Name'
SELECT @fname FROM Video
--以上结果为Name,并非所要

可以使用这样动态拼接：

EXEC(' select ' + @fname + ' from video')--注意加号前后的单引号都加上空格

--当然将字符串形式改成变量也可以
DECLARE @sql NVARCHAR(MAX)
DECLARE @fname NVARCHAR(20)
SET @fname = '[SeoFilename]'
set @sql = 'SELECT ' +  @fname + ' FROM Video AS v'
EXEC (@sql)--一定要记得这个小括号 ，这里的@sql是varchar类型可以

注意：也可以使用 exec sp_executesql 形式

EXEC sp_executesql @sql -- 注意：@sql必须是ntext,nchar,nvarchar类型，不可以是varchar类型         

3 输入或者输出参数

  动态查询语句变量：必须为ntext,nchar,nvarchar类型

 设置动态语句中的查询字符串，必须为ntext,nchar,nvarchar类型

declare @input_id int--定义需传入动态语句的参数的值,参数1            
declare @input_name varchar(20)--定义需传入动态语句的参数的值,参数2
 DECLARE @QueryString NVARCHAR(MAX)--动态语句变量
DECLARE @paramstring NVARCHAR(200)--设置动态语句中的动态字符串参数
                            --                              --
set @QueryString='select * from Video  where id=@id and SeoFilename=@name'   --id与name为字段名，@id与@name为要传入的参数            
set @paramstring='@id int,@name varchar(20)' --设置动态语句中参数的定义的字符串,多个参数用"
                                             --
                                             
set @input_id =2  --设置需传入动态语句的参数的值为1            
set @input_name='新增视频一'   --设置需传入动态语句的参数的值为"张三"            
exec sp_executesql @querystring,@paramstring,@id=@input_id,@name=@input_name --请注意参数的顺序

 4 输出参数

DECLARE @num INT,@sql NVARCHAR(MAX)
SET @sql = 'select count(1) from Video'
EXEC(@sql)

如何将总数的结果返回给一个变量呢？

DECLARE @QueryString NVARCHAR(MAX)
DECLARE @ParamString NVARCHAR(200) 
DECLARE @output_result INT --查询结果返回给它

SET @QueryString = ' select @totalcount = count(1) from video '--定义@totalcount为输出结果参数
SET @ParamString = ' @totalcount  int output '

EXEC sp_executesql @QueryString,@ParamString,@totalcount = @output_result OUTPUT
SELECT @output_result AS '返回总数'

另外，动态语句查询的结果集要输出的话，可以使用临时表的方法

先判断临时表是否存在

   IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除            
   drop table #tmp  

　　IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
　　drop table #tmp

　　SELECT * INTO #tmp FROM Video AS v

　　SELECT * FROM #tmp AS t

 

sqlserver数据库中用到的Identity列

当表中被删除了某些数据，自增长的编号就不再是一个连线的数据

可以使用 SET IDENTITY_INSERT Video ON
允许将显式值插入到表的标示列中，当设置为on，这时可能在insert 操作时候手动指定插入到标示列中的序号，，同时必须在完成操作后，将IDENTITY_INSERT 还原成off
否则，下次插入的时候必须指定编号，不然就无法完成Insert操作

当表中的记录被全部删除，但此时标示列的值很大很大，如果不重置，会无休止的增长，这个时候可以用：DBCC CHECKIDENT（Video, [RESEED|NORESEED], [1]) 进行重置

--重置表中标识列中种子值
DBCC CHECKIDENT（Video, [RESEED|NORESEED], [1]) 

--获取当前表中标示列的种子值
DBCC CHECKIDENT(Video,NORESEED)