-按条件查询Test表信息
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where
id = object_id(N'[dbo].[proc_GetTestInfoByCondition]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_GetTestInfoByCondition]
GO
create proc [proc_GetTestInfoByCondition]
(
@id char(12),
@name varchar(10),
@date datetime
)
as
declare @sql nvarchar(1024),@tdate nvarchar(20),@tname nvarchar(10)
set @sql='select * from test_info where 1=1 '
set @tdate=convert(nvarchar(20),@date)
if @id<>''
begin
set @sql=@sql+'and id='''+@id+''''
end
if @name<>''
begin
set @sql=@sql+'and name='''+@name+''''
end
if @tdate<>''
begin
set @sql=@sql+'and date='''+@tdate+''''
end
print @sql
exec sp_executesql @sql
go
--exec [proc_GetTestInfoByCondition] '','123',''
--select * from test_info where name='admin'
只要加上时间字符串就不行 不能查出记录 这是为什么
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where
id = object_id(N'[dbo].[proc_GetTestInfoByCondition]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_GetTestInfoByCondition]
GO
create proc [proc_GetTestInfoByCondition]
(
@id char(12),
@name varchar(10),
@date datetime
)
as
declare @sql nvarchar(1024),@tdate nvarchar(20),@tname nvarchar(10)
set @sql='select * from test_info where 1=1 '
set @tdate=convert(nvarchar(20),@date)
if @id<>''
begin
set @sql=@sql+'and id='''+@id+''''
end
if @name<>''
begin
set @sql=@sql+'and name='''+@name+''''
end
if @tdate<>''
begin
set @sql=@sql+'and date='''+@tdate+''''
end
print @sql
exec sp_executesql @sql
go
--exec [proc_GetTestInfoByCondition] '','123',''
--select * from test_info where name='admin'
只要加上时间字符串就不行 不能查出记录 这是为什么
--exec sp_executesql @sql
看看语句
@tdate值
测试的时候不能用 print @sql
exec sp_executesql @sql
exec [proc_GetTestInfoByCondition] '','123',''
只能
exec [proc_GetTestInfoByCondition] '','123',''
直接出结果 所以不知道该怎么样打印sql语句
请参阅
DateDiff 函数 | DatePart 函数
print @sql
--exec sp_executesql @sql
看看语句
@tdate值关键还是不会输出sql语句
==>
set @tdate=convert(nvarchar(20),@date,23)
set @sql=@sql+'and name='''+@name+''''
为什么要'''呢?给你例子:create procedure [dbo].[ProcForPager](@p_PageStart int,@p_PageEnd int,@p_SqlSelect nvarchar(4000),@p_OrderBy nvarchar(4000))
as
begin
declare @sqls nvarchar(4000)
set @sqls=N'SELECT * FROM (SELECT tt.*,Row_Number() over('+@p_OrderBy+') as rownum from ('+@p_SqlSelect+') tt) Where rownum between '+@p_PageStart+' and '+@p_PageEnd;
exec sp_executesql @sqls
end;
是的,我写的store procedures传入时间都是用这种格式,从来没有出错过
問題:@tdate 你傳的值是多少? 記住你這個@tdate是匹配到了時分的。數據庫是否有這樣的值
数据库里的时间是自动默认认的 GetDate()如果我想把时间的参数变成 yyyy-MM-dd 存储过程应该怎样处理?