ALTER procedure [dbo].[QueryPaydetialMent] --未成功的订单
@ExtraDiskID int = 0,--空间还是套餐.0套餐,1空间
@ServiceType int = 0,--套餐,空间类别
@OfferCodeID int = 0,
@StartDate date = null,
@EndDate date = null,
@TotalCount int = 0 output,
@pagesize int=20,
@pagecuree int=1
as
declare @tem table( id int identity primary key, oerderNum varchar(80),buy_type nvarchar(50),service_start_time datetime,UserEmail varchar(200),AccountName varchar(100),User_Type int,paytime datetime, Phone varchar(50),Mobile varchar(50) )
declare @sql varchar(3200)
if @OfferCodeID<>0
set @sql=N' and ac.OfferCodeId= '+ CAST( @OfferCodeID as varchar(20))
else
set @sql=N'' ;
if @ExtraDiskID=0 --选取套餐
begin
if @ServiceType<>0
set @sql=@sql+N' and ice.package_type = '+ CAST(@ServiceType as varCHAR(10)) ;
insert @tem
select ice.order_number,ec.name ,ice.service_start_time,ice.customer_email,ac.AccountName,sub.UserType, ice.created_time as paytime,aa.Phone,aa.Mobile from ecommerce.dbo.ec_invoices ice
join Carbonite_Shmi.dbo.Shmi_Subscribers sub on sub.AccountUID=ice.user_group_id
join Carbonite_Shmi.dbo.Shmi_Account ac on sub.AccountUID=ac.AccountUID
join Carbonite_Shmi.dbo.Shmi_AccountAddress aa on aa.AccountUID=ac.AccountUID
join ecommerce.dbo.ec_invoice_items ec on ec.invoice_id=ice.id
where ice.payment_status=2 and CAST( ice.created_time as DATE) between CAST( @StartDate AS varchar(32)) and CAST ( @EndDate as varchar(32)) +@sql;
end
else --空间表
if @ServiceType<>0 -- 如果空间的类别不为0
set @sql=N' and ec.item_id='+CAST(@ServiceType as CHAR);
insert @tem
select ice.order_number,ec.name ,ice.service_start_time,ice.customer_email,ac.AccountName,sub.UserType, ice.created_time as paytime,aa.Phone,aa.Mobile from ecommerce.dbo.ec_invoices ice
join Carbonite_Shmi.dbo.Shmi_Subscribers sub on sub.AccountUID=ice.user_group_id
join Carbonite_Shmi.dbo.Shmi_Account ac on sub.AccountUID=ac.AccountUID
join Carbonite_Shmi.dbo.Shmi_AccountAddress aa on aa.AccountUID=ac.AccountUID
join ecommerce.dbo.ec_invoice_items ec on ec.invoice_id=ice.id
where ice.payment_status=2 and CAST( ice.created_time as DATE) between CAST( @StartDate AS varchar(32)) and CAST ( @EndDate as varchar(32)) +@sql;
select @TotalCount=COUNT (*) from @tem
if @pagecuree=1 --第一页直接显示
begin
select * from @tem tt where tt.id <= @PageSize
end
else
begin
select * from @tem tt where tt.id > (@pagecuree-1)*@PageSize and tt.id <= (@pagecuree*@PageSize);
end
---传参数进去...
USE [Carbonite_CDMNew]
GODECLARE @return_value int,
@TotalCount intEXEC @return_value = [dbo].[QueryPaydetialMent]
@StartDate = N'2012-01-01',
@EndDate = N'2012-07-01',
@ServiceType=2,
@TotalCount = @TotalCount OUTPUTSELECT @TotalCount as N'@TotalCount'SELECT 'Return Value' = @return_valueGO------消息241,级别16,状态1过程QueryPaydetialMent,第22行从字符串转换日期和/或时间时,转换失败。@ServiceType 如果不传的这个参数就不会出错...求大仙解释.在线等...
@ExtraDiskID int = 0,--空间还是套餐.0套餐,1空间
@ServiceType int = 0,--套餐,空间类别
@OfferCodeID int = 0,
@StartDate date = null,
@EndDate date = null,
@TotalCount int = 0 output,
@pagesize int=20,
@pagecuree int=1
as
declare @tem table( id int identity primary key, oerderNum varchar(80),buy_type nvarchar(50),service_start_time datetime,UserEmail varchar(200),AccountName varchar(100),User_Type int,paytime datetime, Phone varchar(50),Mobile varchar(50) )
declare @sql varchar(3200)
if @OfferCodeID<>0
set @sql=N' and ac.OfferCodeId= '+ CAST( @OfferCodeID as varchar(20))
else
set @sql=N'' ;
if @ExtraDiskID=0 --选取套餐
begin
if @ServiceType<>0
set @sql=@sql+N' and ice.package_type = '+ CAST(@ServiceType as varCHAR(10)) ;
insert @tem
select ice.order_number,ec.name ,ice.service_start_time,ice.customer_email,ac.AccountName,sub.UserType, ice.created_time as paytime,aa.Phone,aa.Mobile from ecommerce.dbo.ec_invoices ice
join Carbonite_Shmi.dbo.Shmi_Subscribers sub on sub.AccountUID=ice.user_group_id
join Carbonite_Shmi.dbo.Shmi_Account ac on sub.AccountUID=ac.AccountUID
join Carbonite_Shmi.dbo.Shmi_AccountAddress aa on aa.AccountUID=ac.AccountUID
join ecommerce.dbo.ec_invoice_items ec on ec.invoice_id=ice.id
where ice.payment_status=2 and CAST( ice.created_time as DATE) between CAST( @StartDate AS varchar(32)) and CAST ( @EndDate as varchar(32)) +@sql;
end
else --空间表
if @ServiceType<>0 -- 如果空间的类别不为0
set @sql=N' and ec.item_id='+CAST(@ServiceType as CHAR);
insert @tem
select ice.order_number,ec.name ,ice.service_start_time,ice.customer_email,ac.AccountName,sub.UserType, ice.created_time as paytime,aa.Phone,aa.Mobile from ecommerce.dbo.ec_invoices ice
join Carbonite_Shmi.dbo.Shmi_Subscribers sub on sub.AccountUID=ice.user_group_id
join Carbonite_Shmi.dbo.Shmi_Account ac on sub.AccountUID=ac.AccountUID
join Carbonite_Shmi.dbo.Shmi_AccountAddress aa on aa.AccountUID=ac.AccountUID
join ecommerce.dbo.ec_invoice_items ec on ec.invoice_id=ice.id
where ice.payment_status=2 and CAST( ice.created_time as DATE) between CAST( @StartDate AS varchar(32)) and CAST ( @EndDate as varchar(32)) +@sql;
select @TotalCount=COUNT (*) from @tem
if @pagecuree=1 --第一页直接显示
begin
select * from @tem tt where tt.id <= @PageSize
end
else
begin
select * from @tem tt where tt.id > (@pagecuree-1)*@PageSize and tt.id <= (@pagecuree*@PageSize);
end
---传参数进去...
USE [Carbonite_CDMNew]
GODECLARE @return_value int,
@TotalCount intEXEC @return_value = [dbo].[QueryPaydetialMent]
@StartDate = N'2012-01-01',
@EndDate = N'2012-07-01',
@ServiceType=2,
@TotalCount = @TotalCount OUTPUTSELECT @TotalCount as N'@TotalCount'SELECT 'Return Value' = @return_valueGO------消息241,级别16,状态1过程QueryPaydetialMent,第22行从字符串转换日期和/或时间时,转换失败。@ServiceType 如果不传的这个参数就不会出错...求大仙解释.在线等...
select ice.order_number,ec.name ,ice.service_start_time,ice.customer_email,ac.AccountName,sub.UserType, ice.created_time as paytime,aa.Phone,aa.Mobile from ecommerce.dbo.ec_invoices ice
join Carbonite_Shmi.dbo.Shmi_Subscribers sub on sub.AccountUID=ice.user_group_id
join Carbonite_Shmi.dbo.Shmi_Account ac on sub.AccountUID=ac.AccountUID
join Carbonite_Shmi.dbo.Shmi_AccountAddress aa on aa.AccountUID=ac.AccountUID
join ecommerce.dbo.ec_invoice_items ec on ec.invoice_id=ice.id
where ice.payment_status=2 and CAST( ice.created_time as DATE) between CAST( @StartDate AS varchar(32)) and CAST ( @EndDate as varchar(32)) +@sql;1、红色部分,已经是日期型了为什么还要转字符串呢?
2、这个真的执行呢?
select ...
where ... + 'and ec.item_id = ' + CAST(@ServiceType as CHAR)楼主自己觉得这样的SQL语句是正确的语法么?