怎么改了一下就不对了呢?
declare @sql nvarchar(max)
set @sql = ''
select @sql = @sql +',sum(case when b.BillDate='''+b.BillDate+''' then b.SalesAmount else 0 end) as ['+b.BillDate+']'from PlazaStoreInfo_ZCZL a left join POSSalesPayDetail b on a.StoreID=b.StoreID where b.BillDate >='2014-01-01' and b.BillDate<='2014-01-02'group by b.BillDate
set @sql ='select a.storename' + @sql + ' from PlazaStoreInfo_ZCZL a left join POSSalesPayDetail b on a.StoreID=b.StoreID where b.BillDate >=''2014-01-01'' and b.BillDate<=''2014-01-02'' group by a.Storename'
exec(@sql)
---------------------------------------
上面这段SQL执行没有什么问题,但是改成带参数的存储过程之后,就查询不出数据了,这是什么原因呢,哪里写错了?
ALTER procedure [dbo].[usp_c1] @rq1 nvarchar(20),@rq2 nvarchar(20)
as
declare @sql nvarchar(max)
set @sql = ''
select @sql = @sql +',sum(case when b.BillDate='''+b.BillDate+''' then b.SalesAmount else 0 end) as ['+b.BillDate+']'from PlazaStoreInfo_ZCZL a left join POSSalesPayDetail b on a.StoreID=b.StoreID where b.BillDate >=@rq1 and b.BillDate<=@rq2 group by b.BillDate
set @sql ='select a.storename' + @sql + ' from PlazaStoreInfo_ZCZL a left join POSSalesPayDetail b on a.StoreID=b.StoreID where b.BillDate >='+@rq1+' and b.BillDate<='+@rq2+' group by a.Storename'
exec(@sql)
exec usp_c1 '2014-01-01','2014-01-03'
------解决方案--------------------就是在拼接语句的时候,对于字符串,要加上引号,而且是写两次'',也就是:
' and b.BillDate<='''+ @rq2 ------解决方案--------------------把exec改成print看看语句对不对