Create Stored Procedure Dynamically 1

[Halliarse]

Morning All

I have an application which allows the user to select a range of records based on a search criteria. I need the ability to update all of those records based on single or multiple field value changes. The form has c40 fields that are amendable.

I have have determined which fields have been amended but need to be able to update an SQL database only with the fields that have been amended! As far as I can determine, I need to be able to create a stored procedure dynamically within the application, apply the update and then remove the stored procedure.

I have found an example of how to do this directly from Microsoft but it's creating some errors which I cannot resolve! The code is as below:

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
Dim sp As StoredProcedure
sp = New StoredProcedure(db, "GetLastNameByEmployeeID")
'Set the TextMode property to false and then set the other object properties.
sp.TextMode = False
sp.AnsiNullsStatus = False
sp.QuotedIdentifierStatus = False
'Add two parameters.
Dim param As StoredProcedureParameter
param = New StoredProcedureParameter(sp, "@empval", DataType.Int)
sp.Parameters.Add(param)
Dim param2 As StoredProcedureParameter
param2 = New StoredProcedureParameter(sp, "@retval", DataType.NVarChar(50))
param2.IsOutputParameter = True
sp.Parameters.Add(param2)
'Set the TextBody property to define the stored procedure.
Dim stmt As String
stmt = " SELECT @retval = (SELECT LastName FROM Person.Person AS p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID AND e.BusinessEntityID = @empval )"
sp.TextBody = stmt
'Create the stored procedure on the instance of SQL Server.
sp.Create()
'Modify a property and run the Alter method to make the change on the instance of SQL Server.
sp.QuotedIdentifierStatus = True
sp.Alter()
'Remove the stored procedure.
sp.Drop()

I have imported the Microsoft.SQLServer namespace but the first line containing 'Server' generates a BC30182 type expected error. Similarly, the database and storedprocedure lines are also causing issues!

Can someone please advise what I am missing, are there further namespaces I need or references I need to include....I've spent hours on this and not getting anywhere!

Many, many thanks

Steve

 

[Andrzejek]

>The form has c40 fields that are amendable.
Are those c40 fields of one record?
Or, do you have c40 fields per record and if you allow to update 10 records at the same time, that would be about c400 fields on the form?



---- Andy

There is a great need for a sarcasm font.

 

[Halliarse]

The user can search records initially and that may return say 10 records which the user can view one after the other. The user can also amend any of the fields on say the first record and can either click an update button which will update the current record, or click an update all button which needs to update all 10 records with the data from any of the fields that have changed!

 

[Andrzejek]

> update the current record, or click an update all button
Can’t you just run a simple, one Update statement?
Something like:
[tt]
Update MyTable Set
Field1 = 123,
…,
Field40 = 876
Where ID IN(1, 5, 7)
[/tt]
So the only difference between updating 1 record vs updating 10 records would be in [tt]IN( )[/tt] part of your [tt]Where[/tt] statement.

[tt]Where ID IN (1750)[/tt] - only the record with ID of 1750 will be updated
[tt]Where ID IN (1750, 123, 6754)[/tt] - records with IDs of 1750, 123, and 6754 will be updated


---- Andy

There is a great need for a sarcasm font.

 

[Halliarse]

Thanks for the responses.

I think I may not have explained clearly enough.

The main issue is that I cannot have a fixed stored procedure because the actual data fields that may require updating will vary, so I need to create a stored procedure on the fly, process it for the 10 records and then delete it.

For example, first search I have 10 records, 1 to 10 and the user decides to update say a notes field. I know that only one field had data changed so I need to create that stored procedure with just 1 parameter and then process the update on the 10 records.

Second search I only have 6 records, but 3 fields have been changed, so the stored procedure needs 3 parameters and then process the update on the 10 records.

 

[Andrzejek]

What I don’t really get is – why are you so ‘committed’ to the stored procedure way?
You can do the Updates in the VB.NET code.

And if you really need to go with the stored procedure, write one that accepts the string (your Update statement) and simply execute it.



---- Andy

There is a great need for a sarcasm font.

 

[Halliarse]

Ok, 2 things here:

1. The original point of the post was that I was unable to write the stored procedure to SQL in order to run it

2. Can you give me an example or a link to how I can update within the code?

Thanks

 

[Andrzejek]

2. update within the code

(This code connects to Oracle DB)

Imports System.Data.OleDb

Private Sub btnGo_Click(sender As Object, e As EventArgs) Handles btnGo.Click
Dim strSQL As String

strSQL = "Data Source=XYZ;Provider=OraOLEDB.Oracle;" &
        "Persist Security Info=True;User ID=xxxx;Password=pppp"

Using Cn As New OleDbConnection(strSQL)
    Cn.Open()
    Dim cmd As OleDbCommand = Cn.CreateCommand
    Dim trans As OleDbTransaction = Cn.BeginTransaction
    cmd.Connection = Cn
    cmd.Transaction = trans

    Try[blue]
        strSQL = " Update MyTable Set " & vbNewLine _
            & " SomeField = " & txtField & vbNewLine _
            & " WHERE ID IN (" & strIDs & ")"

        cmd.CommandText = strSQL
        cmd.ExecuteNonQuery()[/blue]

        trans.Commit()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        trans.Rollback()
    End Try
End Using

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[Auguy]

A bit late, but I pass the parameters to a SQL procedure and build my dynamic procedures in SQL.
And then use EXEC sp_executesql to execute the procedure.
Passing the parameters will help stop some bad things from happening with the passed parameters.

Auguy
Sylvania/Toledo Ohio

 

[SaltyTheFrog]

This works...I put it together quick as a proof of concept. I wouldn't use the reader in "real" like. You could drop it first if it exists.

Private Sub btnExecuteQueryReturnRows_Click(sender As Object, e As EventArgs) Handles btnExecuteQueryReturnRows.Click

Dim i As Integer = 0
Dim mSQL As String = "CREATE PROCEDURE #TestIt(@test int) as select 'Test'"

Using mSQLConnection = New SqlConnection(strConnectionString)
mSQLConnection.Open()
Using mSQLCommand = New SqlCommand(mSQL, mSQLConnection)
mSQLCommand.CommandTimeout = 0
Using SQLReader = mSQLCommand.ExecuteReader()

End Using
End Using
End Using

End Sub

I edited this to change the stored procedure name to "#TestIt". That makes it a temporary stored procedure that will be dropped after execution. That way there is no cleanup.