SQL sp_executesql详细使用

大家好，又见面了，我是你们的朋友全栈君。

详情请查看microsoft：

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

CREATE TABLE [dbo].[Test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Num] [nvarchar](50) NULL,
	[CreateTime] [datetime] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into [Test] 
select '张三','2','2019-01-01'
union
select '张三','4','2019-02-01'
union
select '李四','3','2019-02-01'
union
select '李四','3','2019-03-01'

如果我需要查询name=‘李四’   and  num=‘4’ 的数据 并且知道总共有多少条数据

先不说其他的写法怎么实现  现在使用sp_executesql实现。

---------情况一-------------
declare @SQLOne nvarchar(500)
declare @countOne int
declare @NameOne nvarchar(50)
declare @NumOne nvarchar(50)

set @NameOne=N'李四'
set @NumOne=N'3'

set @SQLOne=N'select @count=count(*) from test where name=@name and num=@num';

--顺序一致
exec sp_executesql @SQLOne,N'@name nvarchar(50),@num nvarchar(50),@count int output ', @NameOne,@NumOne,@countOne output
--使用@name = value 并且顺序一致
exec sp_executesql @SQLOne,N'@name nvarchar(50),@num nvarchar(50),@count int output ', @name=@NameOne,@num=@NumOne,@count=@countOne output
--使用@name = value 顺序不一致
exec sp_executesql @SQLOne,N'@name nvarchar(50),@num nvarchar(50),@count int output ', @num=@NumOne,@name=@NameOne,@count=@countOne output


select @countOne,@NameOne,@NumOne

/*
第一:字符串必须是 Unicode 常量或 Unicode 变量. 如:N'select name from test'
第二:每个参数定义由参数名称和数据类型组成,可传递多个参数
第三:[ OUT | OUTPUT ]指示参数是输出参数
第四:动态Sql的参数列表与外部提供值的参数列表顺序必需一致
第五:一旦使用了 '@name = value' 形式之后，所有后续的参数就必须以 '@name = value' 的形式传递。
*/

如果我需要实现根据Name（非必填项）和CreateTime时间范围（非必填项）进行查询列表和总条数（为什么总需要总条数呢，如果查询分页的话可以参考这个需求+ROW_NUMBER()实现）

CREATE proc [dbo].[SP_test2]
@Name nvarchar(50),
@timeStart nvarchar(50),
@timeEnd nvarchar(50),
@totalCount int output
as
begin
declare @where nvarchar(500)
set @where=N' where 1=1 '
if @Name<>''
begin
set @where+=' and name='+''''+@Name+''''
end
if @timeStart<>''
begin
set @where+=' and CreateTime>='+''''+@timeStart+''''
end
if @timeEnd<>''
begin
set @where+=' and CreateTime<='+''''+@timeEnd+''''
end

declare @SQL nvarchar(500)
declare @SQL_count nvarchar(500)

/*查询totalCount*/
set @SQL_count=N'select @count=count(*) from test '+@where;
exec sp_executesql @SQL_count,N'@count int output',@count=@totalCount output

/*查询列表*/
set @SQL=N'select name,num,CreateTime from test  '+@where;
exec sp_executesql @SQL

end

declare @totalCount int

exec dbo.[SP_test2] '','2019-02-01',null,@totalCount output

select @totalCount as '总条数'