VB 6 procedure to SQL SP

26 Jun 2009 4:50 PM

vovan

I have VB6 procedure which inserts data from 2 tables into the third one.
I need to have a stored procedure with the same functionality. This is my
VB6 code:

Private Sub TestPermissionInsert()
Dim rsDBLogin As ADODB.Recordset
Dim rsPermission As ADODB.Recordset
Dim strSQL As String
'Create DBLogin recordset
    strSQL = "SET NOCOUNT ON; SELECT DBLoginID FROM dbo.DBLogin " & _
        "WHERE (RowDeleted IS NULL OR RowDeleted = 0) ORDER BY DBLoginID"
    Set cndTemp = New ADODB.command
    Set cndTemp.ActiveConnection = objConn
    cndTemp.CommandType = adCmdText
    cndTemp.CommandText = strSQL
    Set rsDBLogin = cndTemp.Execute

'Create Permission recordset
    strSQL = "SET NOCOUNT ON; SELECT PermissionID FROM
dbo.UserExtraPermission " & _
        "WHERE (RowDeleted IS NULL OR RowDeleted = 0) ORDER BY PermissionID"
    Set cndTemp = New ADODB.command
    Set cndTemp.ActiveConnection = objConn
    cndTemp.CommandType = adCmdText
    cndTemp.CommandText = strSQL
    Set rsPermission = cndTemp.Execute

'Move on start positions
    If rsDBLogin.RecordCount > 0 Then
        rsDBLogin.MoveFirst
    Else
        Exit Sub
    End If
    If rsPermission.RecordCount > 0 Then
        rsPermission.MoveFirst
    Else
        Exit Sub
    End If

'Now insert all combinations of DBLoginID and PermissionID into
UserAssignedPermission table
    Do While rsDBLogin.EOF = False
        rsPermission.MoveFirst
        Do While rsPermission.EOF = False
            strSQL = "INSERT INTO UserAssignedPermission(DBLoginID,
PermissionID, Assigned) VALUES(" & _
                rsDBLogin.Collect("DBLoginID") & "," &
rsPermission.Collect("PermissionID") & "," & 0 & ")"
            Set cndTemp = New ADODB.command
            Set cndTemp.ActiveConnection = objConn
            cndTemp.CommandType = adCmdText
            cndTemp.CommandText = strSQL
            cndTemp.Execute
            rsPermission.MoveNext
        Loop
        rsDBLogin.MoveNext
    Loop
End Sub

Thank you
vovan

26 Jun 2009 5:07 PM

Tom Moreau

Try:

create proc MyProc
as
SET NOCOUNT ON

INSERT INTO UserAssignedPermission(DBLoginID, PermissionID, Assigned)
SELECT
    DBLoginID
,    PermissionID
,    0
FROM
    dbo.UserExtraPermission
CROSS JOIN
    dbo.DBLogin
WHERE (RowDeleted IS NULL OR RowDeleted = 0)
AND (RowDeleted IS NULL OR RowDeleted = 0)
go

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"vovan" <v@v.com> wrote in message
news:e0xVU5n9JHA.4944@TK2MSFTNGP02.phx.gbl...

I have VB6 procedure which inserts data from 2 tables into the third one.
I need to have a stored procedure with the same functionality. This is my
VB6 code:

Private Sub TestPermissionInsert()
Dim rsDBLogin As ADODB.Recordset
Dim rsPermission As ADODB.Recordset
Dim strSQL As String
'Create DBLogin recordset
    strSQL = "SET NOCOUNT ON; SELECT DBLoginID FROM dbo.DBLogin " & _
        "WHERE (RowDeleted IS NULL OR RowDeleted = 0) ORDER BY DBLoginID"
    Set cndTemp = New ADODB.command
    Set cndTemp.ActiveConnection = objConn
    cndTemp.CommandType = adCmdText
    cndTemp.CommandText = strSQL
    Set rsDBLogin = cndTemp.Execute

'Create Permission recordset
    strSQL = "SET NOCOUNT ON; SELECT PermissionID FROM
dbo.UserExtraPermission " & _
        "WHERE (RowDeleted IS NULL OR RowDeleted = 0) ORDER BY PermissionID"
    Set cndTemp = New ADODB.command
    Set cndTemp.ActiveConnection = objConn
    cndTemp.CommandType = adCmdText
    cndTemp.CommandText = strSQL
    Set rsPermission = cndTemp.Execute

'Move on start positions
    If rsDBLogin.RecordCount > 0 Then
        rsDBLogin.MoveFirst
    Else
        Exit Sub
    End If
    If rsPermission.RecordCount > 0 Then
        rsPermission.MoveFirst
    Else
        Exit Sub
    End If

'Now insert all combinations of DBLoginID and PermissionID into
UserAssignedPermission table
    Do While rsDBLogin.EOF = False
        rsPermission.MoveFirst
        Do While rsPermission.EOF = False
            strSQL = "INSERT INTO UserAssignedPermission(DBLoginID,
PermissionID, Assigned) VALUES(" & _
                rsDBLogin.Collect("DBLoginID") & "," &
rsPermission.Collect("PermissionID") & "," & 0 & ")"
            Set cndTemp = New ADODB.command
            Set cndTemp.ActiveConnection = objConn
            cndTemp.CommandType = adCmdText
            cndTemp.CommandText = strSQL
            cndTemp.Execute
            rsPermission.MoveNext
        Loop
        rsDBLogin.MoveNext
    Loop
End Sub

Thank you
vovan

26 Jun 2009 5:15 PM

vovan

Thanks a lot, Tom

Show quoteHide quote

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:OaKNeCo9JHA.200@TK2MSFTNGP05.phx.gbl...
> Try:
>
> create proc MyProc
> as
> SET NOCOUNT ON
>
> INSERT INTO UserAssignedPermission(DBLoginID, PermissionID, Assigned)
> SELECT
>    DBLoginID
> ,    PermissionID
> ,    0
> FROM
>    dbo.UserExtraPermission
> CROSS JOIN
>    dbo.DBLogin
> WHERE (RowDeleted IS NULL OR RowDeleted = 0)
> AND (RowDeleted IS NULL OR RowDeleted = 0)
> go
>
> --
>   Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON   Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "vovan" <v@v.com> wrote in message
> news:e0xVU5n9JHA.4944@TK2MSFTNGP02.phx.gbl...
> I have VB6 procedure which inserts data from 2 tables into the third one.
> I need to have a stored procedure with the same functionality. This is my
> VB6 code:
>
> Private Sub TestPermissionInsert()
> Dim rsDBLogin As ADODB.Recordset
> Dim rsPermission As ADODB.Recordset
> Dim strSQL As String
> 'Create DBLogin recordset
>    strSQL = "SET NOCOUNT ON; SELECT DBLoginID FROM dbo.DBLogin " & _
>        "WHERE (RowDeleted IS NULL OR RowDeleted = 0) ORDER BY DBLoginID"
>    Set cndTemp = New ADODB.command
>    Set cndTemp.ActiveConnection = objConn
>    cndTemp.CommandType = adCmdText
>    cndTemp.CommandText = strSQL
>    Set rsDBLogin = cndTemp.Execute
>
> 'Create Permission recordset
>    strSQL = "SET NOCOUNT ON; SELECT PermissionID FROM
> dbo.UserExtraPermission " & _
>        "WHERE (RowDeleted IS NULL OR RowDeleted = 0) ORDER BY
> PermissionID"
>    Set cndTemp = New ADODB.command
>    Set cndTemp.ActiveConnection = objConn
>    cndTemp.CommandType = adCmdText
>    cndTemp.CommandText = strSQL
>    Set rsPermission = cndTemp.Execute
>
> 'Move on start positions
>    If rsDBLogin.RecordCount > 0 Then
>        rsDBLogin.MoveFirst
>    Else
>        Exit Sub
>    End If
>    If rsPermission.RecordCount > 0 Then
>        rsPermission.MoveFirst
>    Else
>        Exit Sub
>    End If
>
> 'Now insert all combinations of DBLoginID and PermissionID into
> UserAssignedPermission table
>    Do While rsDBLogin.EOF = False
>        rsPermission.MoveFirst
>        Do While rsPermission.EOF = False
>            strSQL = "INSERT INTO UserAssignedPermission(DBLoginID,
> PermissionID, Assigned) VALUES(" & _
>                rsDBLogin.Collect("DBLoginID") & "," &
> rsPermission.Collect("PermissionID") & "," & 0 & ")"
>            Set cndTemp = New ADODB.command
>            Set cndTemp.ActiveConnection = objConn
>            cndTemp.CommandType = adCmdText
>            cndTemp.CommandText = strSQL
>            cndTemp.Execute
>            rsPermission.MoveNext
>        Loop
>        rsDBLogin.MoveNext
>    Loop
> End Sub
>
>
> Thank you
> vovan
>
>

26 Jun 2009 5:20 PM

Nobody

Show quote Hide quote

"vovan" <v@v.com> wrote in message
news:e0xVU5n9JHA.4944@TK2MSFTNGP02.phx.gbl...
>I have VB6 procedure which inserts data from 2 tables into the third one.
> I need to have a stored procedure with the same functionality. This is my
> VB6 code:
>
> Private Sub TestPermissionInsert()
> Dim rsDBLogin As ADODB.Recordset
> Dim rsPermission As ADODB.Recordset
> Dim strSQL As String
> 'Create DBLogin recordset
>    strSQL = "SET NOCOUNT ON; SELECT DBLoginID FROM dbo.DBLogin " & _
>        "WHERE (RowDeleted IS NULL OR RowDeleted = 0) ORDER BY DBLoginID"
>    Set cndTemp = New ADODB.command
>    Set cndTemp.ActiveConnection = objConn
>    cndTemp.CommandType = adCmdText
>    cndTemp.CommandText = strSQL
>    Set rsDBLogin = cndTemp.Execute
>
> 'Create Permission recordset
>    strSQL = "SET NOCOUNT ON; SELECT PermissionID FROM
> dbo.UserExtraPermission " & _
>        "WHERE (RowDeleted IS NULL OR RowDeleted = 0) ORDER BY
> PermissionID"
>    Set cndTemp = New ADODB.command
>    Set cndTemp.ActiveConnection = objConn
>    cndTemp.CommandType = adCmdText
>    cndTemp.CommandText = strSQL
>    Set rsPermission = cndTemp.Execute
>
> 'Move on start positions
>    If rsDBLogin.RecordCount > 0 Then
>        rsDBLogin.MoveFirst
>    Else
>        Exit Sub
>    End If
>    If rsPermission.RecordCount > 0 Then
>        rsPermission.MoveFirst
>    Else
>        Exit Sub
>    End If
>
> 'Now insert all combinations of DBLoginID and PermissionID into
> UserAssignedPermission table
>    Do While rsDBLogin.EOF = False
>        rsPermission.MoveFirst
>        Do While rsPermission.EOF = False
>            strSQL = "INSERT INTO UserAssignedPermission(DBLoginID,
> PermissionID, Assigned) VALUES(" & _
>                rsDBLogin.Collect("DBLoginID") & "," &
> rsPermission.Collect("PermissionID") & "," & 0 & ")"
>            Set cndTemp = New ADODB.command
>            Set cndTemp.ActiveConnection = objConn
>            cndTemp.CommandType = adCmdText
>            cndTemp.CommandText = strSQL
>            cndTemp.Execute
>            rsPermission.MoveNext
>        Loop
>        rsDBLogin.MoveNext
>    Loop
> End Sub

See these links:

CREATE PROCEDURE:
http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx

And from the "See Also" link above:

Programming Stored Procedures:
http://msdn.microsoft.com/en-us/library/aa214375(SQL.80).aspx

Control-of-Flow Language:
http://msdn.microsoft.com/en-us/library/aa226017(SQL.80).aspx

And finally:

Transact-SQL Reference
http://msdn.microsoft.com/en-us/library/aa299742(SQL.80).aspx