Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Trying to programmatically create a stored procedure with one user-defined table-type parameter

Answered Trying to programmatically create a stored procedure with one user-defined table-type parameter

  • Wednesday, February 06, 2013 7:46 PM
     
     
    Sign In to Vote
    0

    So far I have programmatically created many stored procedures, which accepted one or more SQL Server native data-type parameters such as nVarchar, Int, Decimal, etc, in stand-alone Windows forms VB 2010 apps. I have accomplished this by using the SMO classes.

    Now, I am trying to programmatically create a stored procedure that has one user-defined table-type parameter. The user-defined table-type (dbo.TSNsUDTT) already exists in the database. Here is the VB 2010 code that creates the stored procedure:

    Private Sub CreateStoredProcedure()
            Try
                Dim Server As Microsoft.SqlServer.Management.Smo.Server = New Microsoft.SqlServer.Management.Smo.Server("PROMETHEUS")
                Server.ConnectionContext.AutoDisconnectMode = Microsoft.SqlServer.Management.Common.AutoDisconnectMode.NoAutoDisconnect
                Server.ConnectionContext.Connect()

                If Server.Databases("Test").StoredProcedures.Contains("TestProc", "dbo") Then Server.Databases("Test").StoredProcedures("TestProc", "dbo").Drop()
                Dim SPROC As New Microsoft.SqlServer.Management.Smo.StoredProcedure(Server.Databases("Test"), "TestProc", "dbo")
                SPROC.TextMode = False
                SPROC.AnsiNullsStatus = True
                SPROC.QuotedIdentifierStatus = True
                SPROC.ImplementationType = Microsoft.SqlServer.Management.Smo.ImplementationType.TransactSql
                SPROC.Parameters.Add(New Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(SPROC, "@TSNs", Microsoft.SqlServer.Management.Smo.DataType.UserDefinedTableType("TSNsUDTT", "dbo")))
                SPROC.TextBody = "BEGIN" & vbCrLf & _
                                 "Select * From @TSNs;" & vbCrLf & _
                                 "END;"
                SPROC.Create()

                Server.ConnectionContext.Disconnect()
                Server = Nothing
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbCrLf & vbCrLf & ex.StackTrace, "Application Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub

    When I run the method above the SPROC.Create() statement fails with a message: Create failed for StoredProcedure 'dbo.TestProc'.

    Has anyone participating in this forum had a similar problem ? Do you know what the solution is ? Thank you very much in advance.

     

All Replies

  • Wednesday, February 06, 2013 9:24 PM
     
     Answered Has Code
    Sign In to Vote
    0

    I doubt anyone has done that.  Most people would just spit the CREATE PROCEDURE TSQL.

    Anyway, my guess is that you are somehow missing the READONLY qualifier on the parameter. 

    But more importantly, you should be able to figure this out by creating a sample stored procedure manually, and then using SMO to load it up and examine its parameters collection.

    And Powershell is your friend here, since it's simple to grab the SMO object for anything in a database:

    PS SQLSERVER:\SQL\DBROWNE0\DEFAULT> $proc = get-item databases\YourDatabaseName\storedprocedures\dbo.YourProcName
PS SQLSERVER:\SQL\DBROWNE0\DEFAULT> $proc.Parameters | Format-List -Property *

    David

    David http://blogs.msdn.com/b/dbrowne/




     
  • Thursday, February 07, 2013 11:30 AM
     
     
    Sign In to Vote
    0

    Thanks, David. I will try it following your suggestions. Best Regards.

    I have just changed the statement that creates the parameter into:

    SPROC.Parameters.Add(New Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(SPROC, "@TSNs", Microsoft.SqlServer.Management.Smo.DataType.UserDefinedTableType("TSNsUDTT", "dbo")) With {.IsReadOnly = True})

    and it worked fine. Thank you again for calling my attention to the fact that user-defined table-type parameters must be READONLY.

    • Edited by Marco Auday Thursday, February 07, 2013 11:50 AM
    •