********************V2版本:表固定为Tbl_TrandFlow****************************
USE [PhoenixCard]
GO
/****** Object: StoredProcedure [dbo].[pro_analyse_tsedu] Script Date: 11/29/2013 08:49:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[pro_analyse_tsedu]
-- Add the parameters for the stored procedure here
@startDate nvarchar(10),
@startTime nvarchar(5),
@endDate nvarchar(10),
@endTime nvarchar(5),
@bucode nvarchar(6)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT A.PosNum as PosNum,A.CardPrintNum as CardPrintNum,convert(decimal(18,2),A.Amount) as Amount,B.Remarks as remarks from Tbl_TransFlow as A
inner join Tbl_Pos as B on A.PosNum = B.PosNum
where A.TrDate>=@startDate and A.TrTime>=@startTime and A.TrDate<=@endDate and A.TrTime <=@endTime
and A.BuCode = @bucode and A.Type = '消费'
END
*************************V3版本:表名通过传递参数传到存储过程中[新学到的]***********************************
USE [PhoenixCard]
GO
/****** Object: StoredProcedure [dbo].[pro_analyse_bu] Script Date: 12/02/2013 09:36:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[pro_analyse_bu]
@TableName VARCHAR(50),
@startDate nvarchar(10),
@starttime nvarchar(5),
@endDate nvarchar(10),
@endtime nvarchar(5),
@bucode nvarchar(6),
@type nvarchar(10)
AS
declare @Sql NVARCHAR(MAX)
SET @sql = '
SELECT A.PosNum as PosNum,A.CardPrintNum as CardPrintNum,convert(decimal(18,2),A.Amount) as Amount,B.Remarks as remarks
FROM Tbl_TransFlow A
inner join Tbl_Pos B on A.PosNum=B.PosNum
where Convert(datetime,A.TrDate)>= ''#BeginDate#''
and Convert(datetime,A.TrDate)<= ''#EndDate#''
and A.TrTime>=''#begintime#''
and A.TrTime<=''#endtime#''
and A.BuCode=''#BuCode#''
and A.Type=''#Type#''
'
set @Sql=REPLACE(@Sql,'#BeginDate#',@startDate)
set @Sql=REPLACE(@Sql,'#begintime#',@starttime)
set @Sql=REPLACE(@Sql,'#EndDate#',@endDate)
set @Sql=REPLACE(@Sql,'#endtime#',@endtime)
set @Sql=REPLACE(@Sql,'#BuCode#',@bucode)
set @Sql=REPLACE(@Sql,'#Type#',@type)
print(@sql)
EXEC(@sql);
exec pro_analyse_bu 'Tbl_TransFlow','2012/02/19','00:00','2012/02/19','23:59','940001','消费'
C#调用存储过程:
//调用存储过程。
public static DataSet getProResult(string querystr1,string querystr2,string querystr3,string querystr4,string querystr5,string querystr6){
SqlConnection conn = new SqlConnection(connectionString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand mycommand = new SqlCommand("pro_analyse_bu", conn);
mycommand.CommandType = CommandType.StoredProcedure;
mycommand.Parameters.Add("@TableName", SqlDbType.VarChar, 50).Value = querystr6;
mycommand.Parameters.Add("@startDate", SqlDbType.NVarChar,10).Value = querystr1;
mycommand.Parameters.Add("@startTime", SqlDbType.NVarChar, 5).Value = querystr2;
mycommand.Parameters.Add("@endDate", SqlDbType.NVarChar,10).Value = querystr3;
mycommand.Parameters.Add("@endTime", SqlDbType.NVarChar, 5).Value = querystr4;
mycommand.Parameters.Add("@bucode", SqlDbType.NVarChar,6).Value = querystr5;
mycommand.Parameters.Add("@type", SqlDbType.NVarChar,10).Value = "消费";
conn.Open();
da.SelectCommand = mycommand;
DataSet myds = new DataSet();
da.Fill(myds, "tableName");
return myds;
}
catch (Exception e)
{
throw e;
}
finally {
//关闭连接
conn.Close();
}
}