Link to home
Start Free TrialLog in
Avatar of CraigBroadman
CraigBroadman

asked on

Create Stored Procedure In Visual Basic

I have code to create a stored procedure, see below. It is in a text box and then when i execute the SQL connection i get an error to say "Incorrect Syntax near the keyword 'ObjectRS'"

If i take out the section of the statement that says

CLOSE ObjectRS
DEALLOCATE ObjectRS

then it will execute the SQL statement and create the stored procedure fine.

Any ideas?

CREATE Procedure GrantExecPermissions(@UserOrGroup VarChar(255)) AS
      
      SET NOCOUNT ON
            
      -- Declare and set cursor
      DECLARE @SQL VarChar(500)
      DECLARE @ObjectName VarChar(255)
      DECLARE ObjectRS CURSOR FOR (SELECT [Name] FROM dbo.SysObjects WHERE (xType = 'P' OR xType = 'FN') AND Category = 0)
      
      -- Open and fetch first record from cursor
      OPEN ObjectRS
      FETCH ObjectRS INTO @ObjectName
            
      -- Loop cursor
      WHILE @@FETCH_STATUS = 0
            BEGIN
                  -- Grant EXECUTE
                  SET @SQL = 'GRANT EXECUTE ON ' + @ObjectName + ' TO ' + @UserOrGroup
                  EXEC(@SQL)
                  
                  IF @@ERROR = 0
                        -- Print confirmation
                        PRINT 'Granted EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
                  ELSE
                        -- Print failure
                        PRINT 'Failed to grant EXECUTE  to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
                  
                  -- Get next object
                  FETCH ObjectRS INTO @ObjectName
            END
            
      CLOSE ObjectRS
      DEALLOCATE ObjectRS
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

This worked fine for me: VB6 + SQL Server 2000 + ADO 2.5
Avatar of CraigBroadman
CraigBroadman

ASKER

I dont know what ADO i have? How do i find that out?
Anyhow, here an amended version:

CREATE Procedure GrantExecPermissions ( @UserOrGroup VarChar(255)) AS
     
     SET NOCOUNT ON
         
     -- Declare and set cursor
     DECLARE @SQL VarChar(500)
     DECLARE @ObjectName sysname
     DECLARE @ownername sysname
     DECLARE ObjectRS CURSOR FOR (SELECT user_name(uid), [Name] FROM dbo.SysObjects WHERE (xType = 'P' OR xType = 'FN') AND Category = 0)
     
     -- Open and fetch first record from cursor
     OPEN ObjectRS
     FETCH ObjectRS INTO @ownername, @ObjectName
         
     -- Loop cursor
     WHILE @@FETCH_STATUS = 0
          BEGIN
               -- Grant EXECUTE
               SET @SQL = 'GRANT EXECUTE ON [' + @Ownername + '].[' + @ObjectName + '] TO ' + @UserOrGroup
               EXEC(@SQL)
               
               IF @@ERROR = 0
                    -- Print confirmation
                    PRINT 'Granted EXECUTE to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
               ELSE
                    -- Print failure
                    PRINT 'Failed to grant EXECUTE  to ''' + @UserOrGroup + ''' on ''' + @ObjectName + ''''
               
               -- Get next object
               FETCH ObjectRS INTO @ownername, @ObjectName
          END
         
     CLOSE ObjectRS
     DEALLOCATE ObjectRS

go
It doesn't like that either. Doesnt like the word go at all.

Could you add how you are executing the statement - i am passing it into this routine...

Sub GetADOrst(rst As adodb.Recordset, SQL As String, RecordCount As Long)
         
    Set rst = New adodb.Recordset
    Set rst = gobjConn.Execute(SQL)
 
End Sub

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thats all i needed, it works now!!

Just that I have a whole big program and for any sql statements it calls that procedure, just have to change it a bit though.

Thanks alot for your help