CREATE procedure p_CW_yyreport
@l_qsrq nvarchar(10),
@l_zzrq nvarchar(10),
@t_bname nvarchar(100)
as
-- 判断数据表是否存在
if exists (select * from sysobjects where id = object_id(N'[dbo].['+@t_bname+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Execute('Drop table '+@t_bname)
declare @sql nvarchar(2000)
set @sql = ' SELECT 单据编号, 操作日期, 仓库名称, 商品类别, 销售数量, 销售金额, 进货金额,
退货数量, 退货金额, 利润金额, 营业金额 into '+@t_bname + ' FROM dbo.v_xsumx where 操作日期 >= cast( ' +@l_qsrq+ ' as varchar) and 操作日期 <= cast(' +@l_zzrq + ' as varchar) ' print(cast(@l_qsrq as varchar))
exec(@sql)
exec('select * from ' + @t_bname)
GO我想查询两个"操作日期" 间的数据 ,由于操作日期 字段是VARCHAR型的 我现在数据库里有我要查的数据 可是用了这个过程后就没有查询到数据这是为什么???
@l_qsrq nvarchar(10),
@l_zzrq nvarchar(10),
@t_bname nvarchar(100)
as
-- 判断数据表是否存在
if exists (select * from sysobjects where id = object_id(N'[dbo].['+@t_bname+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Execute('Drop table '+@t_bname)
declare @sql nvarchar(2000)
set @sql = ' SELECT 单据编号, 操作日期, 仓库名称, 商品类别, 销售数量, 销售金额, 进货金额,
退货数量, 退货金额, 利润金额, 营业金额 into '+@t_bname + ' FROM dbo.v_xsumx where 操作日期 >= cast( ' +@l_qsrq+ ' as varchar) and 操作日期 <= cast(' +@l_zzrq + ' as varchar) ' print(cast(@l_qsrq as varchar))
exec(@sql)
exec('select * from ' + @t_bname)
GO我想查询两个"操作日期" 间的数据 ,由于操作日期 字段是VARCHAR型的 我现在数据库里有我要查的数据 可是用了这个过程后就没有查询到数据这是为什么???
@l_qsrq nvarchar(10),
@l_zzrq nvarchar(10),
@t_bname nvarchar(100)
as
-- 判断数据表是否存在
if exists (select * from sysobjects where id = object_id(N'[dbo].['+@t_bname+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Execute('Drop table '+@t_bname)
declare @sql nvarchar(2000)
set @sql = ' SELECT 单据编号, 操作日期, 仓库名称, 商品类别, 销售数量, 销售金额, 进货金额,
退货数量, 退货金额, 利润金额, 营业金额 into '+@t_bname + ' FROM dbo.v_xsumx where 操作日期 >= cast( ' +@l_qsrq+ ' as varchar(10)) and 操作日期 <= cast(' +@l_zzrq + ' as varchar(10)) ' print(cast(@l_qsrq as varchar))
exec(@sql)
exec('select * from ' + @t_bname)
GO
既然你在变量里面都指定了10
那么你在语句中也指定10吧---try
set @sql =
' SELECT
单据编号, 操作日期, 仓库名称, 商品类别, 销售数量, 销售金额, 进货金额,
退货数量, 退货金额, 利润金额, 营业金额
into
'+@t_bname + ' FROM dbo.v_xsumx
where
操作日期 >= convert(varchar(10) ,' +@l_zzrq + ',120))
and
操作日期 <= convert(varchar(10) ,' +@l_zzrq + ',120)) '
DECLARE @l_qsrq nvarchar(10)
DECLARE @l_zzrq nvarchar(10)
DECLARE @t_bname nvarchar(100)
SELECT @l_qsrq = N'2009-01-01'
SELECT @l_zzrq = N'2009-09-09'
SELECT @t_bname = N't_cw_ywmx'
EXEC @RC = [ls200906].[dbo].[p_CW_yyreport] @l_qsrq, @l_zzrq, @t_bname
DECLARE @PrnLine nvarchar(4000)
PRINT '存储过程: ls200906.dbo.p_CW_yyreport'
SELECT @PrnLine = ' 返回代码 = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
后 是返回:
2009-01-01(所影响的行数为 0 行)
(所影响的行数为 0 行)存储过程: ls200906.dbo.p_CW_yyreport
返回代码 = 0问题 还是没有值
@l_qsrq nvarchar(10),
@l_zzrq nvarchar(10),
@t_bname nvarchar(100)
as
-- 判断数据表是否存在
if exists (select * from sysobjects where id = object_id(@t_bname) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Execute('Drop table '+@t_bname)
declare @sql nvarchar(2000)
set @sql = ' SELECT 单据编号, 操作日期, 仓库名称, 商品类别, 销售数量, 销售金额, 进货金额,
退货数量, 退货金额, 利润金额, 营业金额 into '+@t_bname + ' FROM dbo.v_xsumx
where convert(varchar(10),操作日期,120) between ''' +@l_qsrq+ ''' and ''' +@l_zzrq + ''''
exec(@sql)
exec('select * from ' + @t_bname)
GO
@l_qsrq nvarchar(10),
@l_zzrq nvarchar(10),
@t_bname nvarchar(100)
as
-- 判断数据表是否存在
if exists (select * from sysobjects where id = object_id(N'[dbo].['+@t_bname+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Execute('Drop table '+@t_bname)
declare @sql nvarchar(2000)
set @sql = ' SELECT 单据编号, 操作日期, 仓库名称, 商品类别, 销售数量, 销售金额, 进货金额,
退货数量, 退货金额, 利润金额, 营业金额 into '+@t_bname + ' FROM dbo.v_xsumx where cast(操作日期 as datetime)>= cast( ' +@l_qsrq+ ' as datetime) and cast(操作日期 as datetime) <= cast(' +@l_zzrq + ' as datetime) ' print(cast(@l_qsrq as varchar))
exec(@sql)
exec('select * from ' + @t_bname)
GO
--cast( ' +@l_qsrq+ ' as varchar) and 操作日期 <= cast(' +@l_zzrq + ' as varchar) '
关键是@l_qsrq和@l_zzrq前后的单引号
单据编号, 操作日期, 仓库名称, 商品类别, 销售数量, 销售金额, 进货金额,
退货数量, 退货金额, 利润金额, 营业金额
into
t_cw_yymx FROM dbo.v_xsumx
where
操作日期 >= convert(varchar(10) ,2009-11-11,120)
and
操作日期 <= convert(varchar(10) ,2009-11-11,120) (所影响的行数为 0 行)
(所影响的行数为 0 行)存储过程: ls200906.dbo.p_CW_yyreport
返回代码 = 0
select * from v_xsumx where 操作日期 >='2009-01-02' and 操作日期 <='2009-11-11' 是没有问题的 .当用 EXEC()后 字符型的变量 是没有单引号的 可是怎么能解决这样的问题呢 应怎么写呢