SQLServer创建存储过程，表名动态传入。并用C#调用存储过程。

********************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,>
-- =============================================
CREATE  PROCEDURE [dbo].[pro_analyse_bu]
        @TableName VARCHAR(50),@startDate nvarchar(10),@starttime nvarchar(5),@endtime nvarchar(5),@bucode nvarchar(6),@type nvarchar(10)
	
AS
	declare @Sql NVARCHAR(MAX)

SET @sql = '
SELECT A.PosNum as PosNum,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','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",5).Value = querystr2;
                 mycommand.Parameters.Add("@endDate",10).Value = querystr3;
                 mycommand.Parameters.Add("@endTime",5).Value = querystr4;
                 mycommand.Parameters.Add("@bucode",6).Value = querystr5;
                 mycommand.Parameters.Add("@type",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();
           }
        }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!