Stored Procedure creation in vb.net

[Guest]

I need help.

How do I create a stored procedure with SQLCommand? I have tried following
string, but it doesn't work. I am using vs.net 2003 professional now.

dbCommand.CommandText = _
"CREATE PROCEDURE GetID" & vbCrLf & _
"@ID int out," & vbCrLf & _
"@Name varchar(20)" & vbCrLf & _
"AS" & vbCrLf & _
"select @ID = ID from Person where Name = @Name"

Thanks a lot.

 

[Herfried K. Wagner [MVP]]

How do I create a stored procedure with SQLCommand?

Notice that a separate group for .NET+database questions is available. You
will more likely get an answer there:

<URL:

 

[harry]

Here's a snippet of some code for deleting/creating/using a couple of small
stored procedures

Hope it helps


'A) Create Table Proc

'remove old if exists first
SB.Remove(0, SB.Length)
SB.Append("IF EXISTS (SELECT ROUTINE_NAME")
SB.Append(" FROM INFORMATION_SCHEMA.ROUTINES")
SB.Append(" WHERE ROUTINE_TYPE = 'PROCEDURE' AND")
SB.Append(" ROUTINE_NAME = 'udp_CreateTable')")
SB.Append(" DROP PROCEDURE udp_CreateTable")
cmd.CommandText = SB.ToString
cmd.ExecuteNonQuery()

'Now Re/Create

SB.Remove(0, SB.Length)
SB.Append("CREATE PROCEDURE udp_CreateTable")
SB.Append(" @TableName nvarchar(50)")
SB.Append(" AS")
SB.Append(" Declare @SQL VarChar(1000)")
SB.Append(" SELECT @SQL = 'Create Table ' + @TableName + ' ('")
SB.Append(" SELECT @SQL = @SQL + ' [RecNum] int NOT NULL Primary
Key (RecNum))'")
SB.Append(" Exec (@SQL)")
cmd.CommandText = SB.ToString
cmd.ExecuteNonQuery()



'B) Create Insert RecNum Proc

'remove old if exists first
SB.Remove(0, SB.Length)
SB.Append("IF EXISTS (SELECT ROUTINE_NAME")
SB.Append(" FROM INFORMATION_SCHEMA.ROUTINES")
SB.Append(" WHERE ROUTINE_TYPE = 'PROCEDURE' AND")
SB.Append(" ROUTINE_NAME = 'udp_InsertRecNum')")
SB.Append(" DROP PROCEDURE udp_InsertRecNum")
cmd.CommandText = SB.ToString
cmd.ExecuteNonQuery()

'Now Re/Create
SB.Remove(0, SB.Length)
SB.Append("CREATE PROC udp_InsertRecNum")
SB.Append(" @TableName nvarchar(50),")
SB.Append(" @RecNum nvarchar(20)")
SB.Append(" AS")
SB.Append(" Declare @SQL VarChar(1000)")
SB.Append(" SELECT @SQL = 'INSERT INTO ' + @TableName + '
(RecNum)'")
SB.Append(" SELECT @SQL = @SQL + ' Values (' + @RecNum + ')'")
SB.Append(" Exec (@SQL)")
cmd.CommandText = SB.ToString
cmd.ExecuteNonQuery()


'use these stored procedures

'this creates table with single column: [RecNum] int NOT NULL

Private Sub udp_CreateTable(ByVal DataBaseNameForNewTable As String, ByVal
TableName As String)

cmdStats.CommandText = "EXEC udp_CreateTable [" &
DataBaseNameForNewTable & ".." & TableName & "]"
cmdStats.ExecuteNonQuery()

End Sub

'insert recnum to table
Private Sub udp_InsertRecNum(ByVal DataBase As String, ByVal TableName
As String, ByVal RecNum As Integer)

cmdStats.CommandText = "EXEC udp_InsertRecnum [" & DataBase & ".." &
TableName & "], " & RecNum
cmdStats.ExecuteNonQuery()

End Sub

 

[Guest]

Thank you for your advice.

Notice that a separate group for .NET+database questions is available. You
will more likely get an answer there:

<URL: