MS SQL Stotred procedure....

I get an error on cursor, can't see the problem. Help!

ALTER PROCEDURE [dbo].[WOW_SETLOGINS]
 (
    --Designed to set userlogins in for a given database/tables.

    --Retrieves a string of table names based on a given database.
    --Input parameters...  
    @DBNAME VARCHAR(45),
    @LOGINNAME VARCHAR(45)  
 )

AS

DECLARE @sSelectSql NVARCHAR(1000)
DECLARE @sGrantSql NVARCHAR(1000)
 
--The cursor that will be filled by the dynamic SQL
DECLARE @outputCursor CURSOR

--Grab all of the tables from the given database.
SET @sSelectSql= 'SELECT Name ' + @DBNAME + '..sysobjects WHERE type = ''U'''

--Create the dynamic SQL to fill a CURSOR instead, Note the OPEN keyword.
SET @sSelectSql = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @sSelectSql + ' ; OPEN @outputCursor'

--Execute dynamic SQL
--The parameter list for the sproc: OUTPUT CURSOR
--The parameter to pass to the sproc: the CURSOR
EXEC sp_executesql @sSelectSql, N' @outputCursor CURSOR OUTPUT', @outputCursor OUTPUT        

--Code that will just output the values from the cursor
DECLARE @TABLENAME VARCHAR(45) --The name of the table.

--Grap All of the fields and insert them into the variables
FETCH NEXT FROM @outputCursor INTO @TABLENAME

WHILE (@@FETCH_STATUS=0)
 BEGIN
   SET @sGrantSql= 'GRANT SELECT, INSERT, DELETE, UPDATE ON ' + @DBNAME + '..'
   SET @sGrantSql= @sGrantSql + @TABLENAME + ' TO [' + @LOGINNAME + ']'  
 
   --Grant the table the given permissions...
   EXEC sp_executesql @sGrantSql, N' @DBNAME VARCHAR(45), @TextFieldName varchar(25), @TABLENAME VARCHAR(45), @LOGINNAME VARCHAR(45)',
   @DBNAME,@TABLENAME,@LOGINNAME
   
   FETCH NEXT FROM @outputCursor INTO @TABLENAME
 END
 
 CLOSE @outputCursor
 DEALLOCATE @outputCursor
7of9121098Asked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
you have declared cursor  FORWARD_ONLY in your code and I forgot to remove DYNAMIC. Please remove that word.
0
 
GSQLCommented:
SET @sSelectSql= 'SELECT Name from ' + @DBNAME + '.sys.objects WHERE type = ''U'''
0
 
RiteshShahCommented:

ALTER PROCEDURE [dbo].[WOW_SETLOGINS]
 (
    --Designed to set userlogins in for a given database/tables.
 
    --Retrieves a string of table names based on a given database. 
    --Input parameters...   
    @DBNAME VARCHAR(45), 
    @LOGINNAME VARCHAR(45)   
 )
 
AS 
 
DECLARE @sSelectSql NVARCHAR(1000)
DECLARE @sGrantSql NVARCHAR(1000)
 
--The cursor that will be filled by the dynamic SQL
DECLARE @outputCursor CURSOR DYNAMIC
 
--Grab all of the tables from the given database.
SET @sSelectSql= 'SELECT Name from' + @DBNAME + '..sysobjects WHERE type = ''U'''
 
--Create the dynamic SQL to fill a CURSOR instead, Note the OPEN keyword.
SET @sSelectSql = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @sSelectSql + ' ; OPEN @outputCursor'
 
--Execute dynamic SQL
--The parameter list for the sproc: OUTPUT CURSOR
--The parameter to pass to the sproc: the CURSOR
EXEC sp_executesql @sSelectSql, N' @outputCursor CURSOR OUTPUT', @outputCursor OUTPUT        
 
--Code that will just output the values from the cursor
DECLARE @TABLENAME VARCHAR(45) --The name of the table.
 
--Grap All of the fields and insert them into the variables
FETCH NEXT FROM @outputCursor INTO @TABLENAME
 
WHILE (@@FETCH_STATUS=0)
 BEGIN
   SET @sGrantSql= 'GRANT SELECT, INSERT, DELETE, UPDATE ON ' + @DBNAME + '..' 
   SET @sGrantSql= @sGrantSql + @TABLENAME + ' TO [' + @LOGINNAME + ']'  
  
   --Grant the table the given permissions...
   EXEC sp_executesql @sGrantSql, N' @DBNAME VARCHAR(45), @TextFieldName varchar(25), @TABLENAME VARCHAR(45), @LOGINNAME VARCHAR(45)', 
   @DBNAME,@TABLENAME,@LOGINNAME
   
   FETCH NEXT FROM @outputCursor INTO @TABLENAME
 END
 
 CLOSE @outputCursor
 DEALLOCATE @outputCursor

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
7of9121098Author Commented:
I get an following error when I run it....it doesn't like the keyword dymanic.

Incorrect syntax near 'dynamic'.
Msg 137, Level 15, State 2, Procedure SETLOGINS, Line 29
Must declare the scalar variable "@outputCursor".
Msg 13
0
 
7of9121098Author Commented:

 I solved the issue I had to remove the dynamic and put a space in the 'SELECT Name from'  to 'Select Name From '. Thanks
0
 
7of9121098Author Commented:
Thanks...RiteshShah!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.