?
Solved

MS SQL Stotred procedure....

Posted on 2009-04-24
6
Medium Priority
?
353 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:7of9121098
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:GSQL
ID: 24229943
SET @sSelectSql= 'SELECT Name from ' + @DBNAME + '.sys.objects WHERE type = ''U'''
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24229950

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
 

Author Comment

by:7of9121098
ID: 24230744
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 31

Accepted Solution

by:
RiteshShah earned 200 total points
ID: 24230748
you have declared cursor  FORWARD_ONLY in your code and I forgot to remove DYNAMIC. Please remove that word.
0
 

Author Comment

by:7of9121098
ID: 24230986

 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
 

Author Closing Comment

by:7of9121098
ID: 31574426
Thanks...RiteshShah!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question