如何使用SQL创建多个存储过程

I have a vb.net function that creates several Stored Procedures based on parameters being passed into the function.

我有一个vb.net函数,它根据传递给函数的参数创建几个存储过程。

I want to move this vb.Net into a single SQL file (for maintenance reasons) but I am not sure how I can re-create it in SQL without creating 7 separate stored procedures.

我想将此vb.Net移动到单个SQL文件中(出于维护原因)但我不确定如何在不创建7个单独的存储过程的情况下在SQL中重新创建它。

I create a total of 20 Stored Procedures and I don't really want to create this many in a SQL file as maintenance will be a nightmare. I am wondering if there is a solution similar to how I have done it VB.Net below:

我创建了总共20个存储过程,我真的不想在SQL文件中创建这么多,因为维护将是一场噩梦。我想知道是否有类似于我如何做到的解决方案VB.Net如下:

Private Sub CreateStoredProcedures()

        CreateSP("SummaryNone", "YEAR([sale].[DateTime])")
        CreateSP("SummaryUser", "[sale].[User]")
        CreateSP("Summarysale", "[sale].[sale]")
        CreateSP("SummaryBatch", "[sale].[Batch]")
        CreateSP("SummaryDay", "dbo.FormatDateTime([sale].[DateTime], 'yyyy-mm-dd')")
        CreateSP("SummaryMonth", "dbo.FormatDateTime(dbo.DateSerial(YEAR([sale].[DateTime]), MONTH([sale].[DateTime]), 1), 'yyyy-mm-dd')")
        CreateSP("SummaryYear", "Year([sale].[DateTime])")

        Return
    End Sub


    Private Sub CreateSP(ByVal vName As String, ByVal vGroup As String)

        Dim CommandText As String = _
                        "CREATE PROCEDURE " & vName _
                        & " @StartDate varchar(50)," _
                        & " @EndDate varchar(50)" _
                        & " AS " _
                        & " SELECT " & vGroup & "                   AS GroupField," _
                        & " Sum([Project].[NumProject])                AS TotalProject," _
                        & " Sum([Project].[Title])                  AS SumTitle," _
                        & " Sum([Project].[Duration])               AS SumDuration," _
                        & " Sum([Project].[Info])                AS SumInfo," _
                        & " Sum([Customer].[NumCustomer]) AS TotalNumCustomer," _
                        & " Sum([Orders].[NumOrders])   AS TotalNumOrders," _
                        & " Sum([OrderInspection].[NumInspects])          AS TotalNumInspects," _
                        & " Sum([OrderInspection].[NumFails])             AS TotalNumFails," _
                        & " Sum([CustomerInspection].[NumInspects])    AS TotalNumCustomerInspectionInspects," _
                        & " Sum([CustomerInspection].[NumFails])       AS TotalNumCustomerInspectionFails," _
                        & " Sum([Measurements].[NumMeasurements]) AS TotalNumMeasurementss" _
                        & " FROM ((((((sale LEFT JOIN Project   ON [sale].[saleId]=[Project].[saleId])" _
                            & " LEFT JOIN Customer   ON [Project].[PrintId]=[Customer].[PrintId])" _
                            & " LEFT JOIN Orders    ON [Project].[PrintId]=[Orders].[PrintId])" _
                            & " LEFT JOIN OrderInspection       ON [Project].[PrintId]=[OrderInspection].[PrintId])" _
                            & " LEFT JOIN CustomerInspection ON [Project].[PrintId]=[CustomerInspection].[PrintId])" _
                            & " LEFT JOIN Measurements ON [Project].[PrintId]=[Measurements].[PrintId])" _
                        & " WHERE [sale].[DateTime] BETWEEN dbo.FormatDateTime((@StartDate), 'yyyy-mm-dd')" _
                        & " AND dbo.FormatDateTime((@Enddate),'yyyy-mm-dd')" _
                        & " GROUP BY " & vGroup & "" _
                        & " ORDER BY " & vGroup & ";"

        SqlExecuteNonQuery(CommandText)

        return
    End Sub

I look forward to your comments and replies.

我期待着您的意见和回复。

Thanks

5 个解决方案

#1

You could store a template in a text file as an embedded resource in your .NET DLL. Have some placeholders for your dynamic bits. Which would make your current solution a lot more maintainable. Then you load the stream from the DLL and keep your current implementation.

您可以将模板存储在文本文件中作为.NET DLL中的嵌入式资源。为动态位添加一些占位符。这将使您当前的解决方案更易于维护。然后从DLL加载流并保持当前的实现。

Editing the text file is easier than that big chunk of SQL which is embedded in your C# file.

编辑文本文件比嵌入在C#文件中的大块SQL更容易。

You will probably get a performance hit if you move this to a single proc, you may be happy with it but keep in mind that the proc will also have some maintenance issues. We usually like to avoid dynamic SQL in stored procs. And a 7 way IF branch is a maintenance nightmare.

如果将其移动到单个proc,您可能会受到性能影响,您可能会对此感到满意,但请记住,proc也会有一些维护问题。我们通常喜欢在存储过程中避免使用动态SQL。 7路IF分支是维护的噩梦。

#2

You could create a single stored procedure called Summary and then pass in the name and column as additional parameters. Then you create dynamic SQL and execute that.

您可以创建一个名为Summary的存储过程,然后将名称和列作为附加参数传递。然后,您创建动态SQL并执行它。

#3

I would recommend that you create a single stored procedure that you call.

我建议您创建一个您调用的存储过程。

Then use dynamic SQL to build the appropriate select statement to be used within the single stored procedure.

然后使用动态SQL构建要在单个存储过程中使用的相应select语句。

Make sense?

#4

Building the statement dynamically and then executing it (in T-SQL) is your best bet, in my opinion. I would also get away from creating stored procs in code if all pissoble. The biggest advantage to having stored porcs is that their executions plans are cached on the sever. If you drop and re-create them often those plans are removed since the proc is dropped.

在我看来,动态构建语句然后执行它(在T-SQL中)是你最好的选择。如果所有的pissoble,我也会远离在代码中创建存储过程。存储porcs的最大优点是它们的执行计划缓存在服务器上。如果您放弃并重新创建它们,那么这些计划将被删除,因为删除了proc。

#5

The advantage of an SP is that it is pre-compiled and an execution plan has already been created keeping in mind the data that is present at the time of creation of the SP.

SP的优点在于它是预编译的,并且已经创建了执行计划,同时记住在创建SP时存在的数据。

So a dynamic SQL would have performance implications because sql server cannot find out beforehand which indexes to use(or to even use them or not). Since currently you are creating the sp after the substitution , the query plan is created correctly. That will not be the case after you switch to dynamic sql.

因此,动态SQL会产生性能影响,因为sql server无法预先找出要使用的索引(或者甚至不使用它们)。由于您目前在替换后创建sp,因此可以正确创建查询计划。切换到动态sql后不会出现这种情况。

You can also create an sp with if then condition to take care of the various scenarios. However , that requires maintainence in case more parameters are added later.

您还可以创建一个带if if条件的sp来处理各种场景。但是,如果以后添加更多参数,则需要维护。

#1