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
"Wie" <Wi*@discussions.microsoft.com> wrote in message
news:5D**********************************@microsof t.com...
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.