How To Create A Stored Procedure at run time using ADOX object

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How To Create A Stored Procedure at run time using ADOX object

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-31 : 11:50:43

Yogesh writes "Dear SqlTeam,

I ahve a problem in creating a stored procedure at run time using ADOX Object. I belive Adox is the only object which you can use to create stored proc in SQL Server. As a sample I am including my VB code for your referance.

Kindly arrange to solve this problem at the earliest.

Thanks & Regards

Sincerly Yours

Yogesh Ajmere

Public Sub CreateProc()

Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim DnsStr As String, NewLine As String, Sql As String

NewLine = Chr(10)

DsnStr = "DSN=yamuna;uid=yogesh;pwd=;"
'*********************************
Sql = "SET QUOTED_IDENTIFIER ON" & NewLine & "GO " & NewLine & "SET ANSI_NULLS ON " & NewLine & "GO "
Sql = Sql & NewLine & "CREATE PROCEDURE FullSoundex123 -- <procedure_name, sysname, proc_test>" & NewLine & "(@strIn CHAR(255))" & NewLine & " AS"
Sql = Sql & NewLine & "DECLARE @OneWord AS VARCHAR(100), @iLn AS INT, @iCount AS INT, @cChr AS CHAR, @ThisSoundex AS VARCHAR(5), @Soundex AS VARCHAR(100)"
Sql = Sql & NewLine & "SET @iCount = 1" & NewLine & "SET @iLn = LEN(@strIn)" & NewLine & "SET @ThisSoundex = ''" & NewLine & "SET @Soundex = ''" & NewLine & "SET @OneWord = ''"
Sql = Sql & NewLine & "WHILE (@iCount <= @iLn)" & NewLine & " BEGIN"
Sql = Sql & NewLine & "IF (@cChr = ' ' )" & NewLine & " BEGIN"
Sql = Sql & NewLine & " SET @ThisSoundex = SOUNDEX (@OneWord)" & NewLine & " SET @Soundex = @Soundex + @ThisSoundex + ';'" & NewLine & " SET @OneWord = ''" & NewLine & " End"
Sql = Sql & NewLine & " Else" & NewLine & " BEGIN" & NewLine & " SET @OneWord = @OneWord + @cChr" & NewLine & " End" & NewLine & " SET @iCount = @iCount + 1" & NewLine & " End"
Sql = Sql & NewLine & " IF (@OneWord <> '')" & NewLine & " BEGIN" & NewLine & " SET @ThisSoundex = SOUNDEX (@OneWord)" & NewLine & " SET @Soundex = @Soundex + @ThisSoundex + ';'" & NewLine & " End"
Sql = Sql & NewLine & " --PRINT @Soundex" & NewLine & "GO" & NewLine & "SET QUOTED_IDENTIFIER OFF" & NewLine & "GO" & NewLine & "SET ANSI_NULLS ON" & NewLine & "GO"

MsgBox Sql

On Error GoTo AdoError
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = Sql
Cmd.CommandType = adCmdStoredProc
Cmd.Execute

AdoError:
Dim errLoop As Error
Dim strError As String

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next

End Sub"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources