没有问题呀,做后一个语句当然是错的
select @SelectStr='select count(*) as total from kkk where 检测日期 between '+@DateBegin1+'
and '+@DateEnd1
select @Selectstr
exec(@SelectStr)其它都是对的,结果都是2
select @SelectStr='select count(*) as total from kkk where 检测日期 between '+@DateBegin1+'
and '+@DateEnd1
select @Selectstr
exec(@SelectStr)其它都是对的,结果都是2
到
select @SelectStr='select count(*) as total from kkk where 检测日期 between '+@DateBegin+'
and '+@DateEnd
select @SelectStr
exec(@SelectStr)
就出错退出了,后面的没有执行应该加引号的
if exists(select * from sysobjects where type='p' and name='SP_SQL')
drop procedure SP_SQL
go
CREATE procedure SP_SQL @DateBegin varchar(10),@DateEnd varchar(10)
as
set nocount on
if exists (select * from sysobjects where id = object_id(N'[kkk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)begin
drop table [kkk]
endbegin
create table kkk(id int identity primary key,检测日期 datetime)
insert kkk values('2002-1-1')
insert kkk values('2002-10-1')
select * from kkk
endbegin
declare @DateBegin1 datetime,
@DateEnd1 datetime,
@SelectStr nvarchar(2000)
select @DateBegin1=cast(@DateBegin as datetime)
select @DateEnd1=cast(@DateEnd as datetime)
select @DateBegin1
select @DateEnd1select count(*) from kkk where 检测日期 between @DateBegin and @DateEnd
select count(*) from kkk where 检测日期 between @DateBegin1 and @DateEnd1select @SelectStr=N'select count(*) as total from kkk where 检测日期 between '''+@DateBegin+'''
and '''+@DateEnd+''' '
select @SelectStr
exec(@SelectStr)select @SelectStr=N'select count(*) as total from kkk where 检测日期 between '''+@DateBegin+'''
and '''+@DateEnd+''' '
select @SelectStr
exec(@SelectStr)select @SelectStr=N'select count(*) as total from kkk where 检测日期 between '''+CONVERT(varchar(10),@DateBegin1,120)+'''
and '''+CONVERT(varchar(10),@DateEnd1,120)+''' '
select @SelectStr
exec(@SelectStr)select @SelectStr=N'select count(*) as total from kkk where 检测日期 between '''+CONVERT(varchar(10),@DateBegin1,120)+'''
and '''+CONVERT(varchar(10),@DateEnd1,120)+''''
select @Selectstr
exec(@SelectStr)
end
set nocount off
go
declare @aa datetime,@bb datetime
set @aa=cast('2000-1-1' as datetime)
set @bb=cast('2003-1-1' as datetime)
exec SP_SQL @aa,@bb
是关键
drop procedure SP_SQL
go
CREATE procedure SP_SQL @DateBegin varchar(10),@DateEnd varchar(10)
as
--set nocount on
if exists (select * from sysobjects where id = object_id(N'[kkk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)begin
drop table [kkk]
endbegin
create table kkk(id int identity primary key,检测日期 datetime)
insert kkk values('2002-1-1')
insert kkk values('2002-10-1')
select * from kkk
endbegin
declare @DateBegin1 datetime,
@DateEnd1 datetime,
@SelectStr nvarchar(2000)
select @DateBegin1=cast(@DateBegin as datetime)
select @DateEnd1=cast(@DateEnd as datetime)
select @DateBegin1
select @DateEnd1select count(*) from kkk where 检测日期 between @DateBegin and @DateEnd
select count(*) from kkk where 检测日期 between @DateBegin1 and @DateEnd1select @SelectStr='select count(*) as total from kkk where 检测日期 between '''+@DateBegin+'''
and '''+@DateEnd+''' '
print @SelectStr
exec(@SelectStr)select @SelectStr='select count(*) as total from kkk where 检测日期 between '''+@DateBegin+'''
and '''+@DateEnd+''''
print @SelectStr
exec(@SelectStr)select @SelectStr='select count(*) as total from kkk where 检测日期 between '''+@DateBegin+''' and '''+@DateEnd+''''
print @SelectStr
exec(@SelectStr)select @SelectStr='select count(*) as total from kkk where 检测日期 between '''+@DateBegin+'''
and '''+@DateEnd+''''
print @Selectstr
exec(@SelectStr)
end
--set nocount off
go
exec SP_SQL '2000-1-1','2003-1-1'-------------------------------------------------------
select @SelectStr='select count(*) as total from kkk where 检测日期 between '+@DateBegin1+'
and '+@DateEnd1字符怎么能和日期相加??
所以照你前面两句写就可以了。SQL 会把‘2000-1-1’转换为日期型
1.在SQL中日期不可以当字符串处理吗?
2.动态语句与一般语句是怎么样进行语义分析的?
select count(*) from kkk where 检测日期 between @DateBegin1 and @DateEnd1
3.动态SQL语句中变量的写法—'''+@变量+'''有数据类型的限制吗,是不是只适应于字符串?要不然为什么'''+@DateEnd1+'''不行?
2、写的时候不分析,执行的时候分析。
3、只限于字符串。