How to create several stored procedure using SQL

Question

Hi,

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

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.

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:

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

Answer 1

Hi,

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.

Make sense?

Cheers, John

Answer 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.

Answer 3

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.

Answer 4

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.

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

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.

Answer 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.

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.

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.