Updating a SQL Stored Procedure through my code...

Question

Hi Guys, Here is my problem. I need to update an already excisting stored proc from my code and commit it. How can this be achieved through the System.Data.SQLClient if possible? Thanks,

Vasanth · Answer

Can you please use this code for create stored procedure dynamically. If you want to update stored procedure dynamically please update ALTER instead of create. Sub CreateSP() Using conn As New System.Data.SqlClient.SqlConnection( " " ) conn.Open() Using cmd As New SqlClient.SqlCommand( "select count(*) from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME='spFindUser'" , conn) If CType (cmd.ExecuteScalar, Integer ) = 0 Then cmd.CommandText = "CREATE PROCEDURE spFindUser ( " & vbCrLf cmd.CommandText &= "@Userid int, " & vbCrLf cmd.CommandText &= "@Username Varchar(100)=Null ) " & vbCrLf cmd.CommandText &= "AS" & vbCrLf cmd.CommandText &= "BEGIN " & vbCrLf cmd.CommandText &= "DECLARE @strQuery varchar(max) " & vbCrLf cmd.CommandText &= "SET @strQuery = 'Select * from tbl_MyTable WHERE Userid = @Userid" cmd.CommandText &= "EXEC (@strQuery)" & vbCrLf cmd.CommandText &= "END" Try cmd.ExecuteNonQuery() Catch ex As Exception MsgBox( "Error" ) conn.Close() End Try End If End Using End Using End Sub If its helps you , please mark as answer.

Aaron Morgan · Answer

The only way I know how to do is it create a "Update Query" to your Dataset.Xsd then use the correct code in your project to run that Update Command,(I guess you could call it),Sorry if this was unhelpful but,Hope it helped!

Updating a SQL Stored Procedure through my code...

Answers (2)