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.

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

<URL:news://news.microsoft.com/microsoft.public.dotnet.framework.adonet>

Thank you for your advice. 


Show quoteHide quote

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



Show quoteHide quote

2D array of string
2GB memory limit
Installer isn't removing previous version
How do I set a property which is an object?
Binding combo box
Bitmap and MemoryStream
Command Line Compiling with license file
DateTimePicker
how to pin object so the gc will not move it in memory ?
True or 1 ?