variable assignment is not allowed in cursor declaration

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

DECLARE CURSOR (Transact-SQL)

• 12 contributors

Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation .

cursor_name Is the name of the Transact-SQL server cursor defined. cursor_name must conform to the rules for identifiers.

INSENSITIVE Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb ; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. When ISO syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.

SCROLL Specifies that all fetch options ( FIRST , LAST , PRIOR , NEXT , RELATIVE , ABSOLUTE ) are available. If SCROLL is not specified in an ISO DECLARE CURSOR , NEXT is the only fetch option supported. SCROLL cannot be specified if FAST_FORWARD is also specified. If SCROLL is not specified then only the fetch option NEXT is available and the cursor becomes FORWARD_ONLY .

select_statement Is a standard SELECT statement that defines the result set of the cursor. The keywords FOR BROWSE , and INTO are not allowed within select_statement of a cursor declaration.

SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type.

READ ONLY Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

UPDATE [OF column_name [ , ... n ]] Defines updatable columns within the cursor. If OF <column_name> [, <... n>] is specified, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated.

LOCAL Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

GLOBAL Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option.

FORWARD_ONLY Specifies that the cursor can only move forward and be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. All insert, update, and delete statements made by the current user (or committed by other users) that affect rows in the result set are visible as the rows are fetched. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor. Forward-only cursors are dynamic by default, meaning that all changes are detected as the current row is processed. This provides faster cursor opening and enables the result set to display updates made to the underlying tables. While forward-only cursors do not support backward scrolling, applications can return to the beginning of the result set by closing and reopening the cursor. If FORWARD_ONLY is specified without the STATIC , KEYSET , or DYNAMIC keywords, the cursor operates as a dynamic cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC , KEYSET , or DYNAMIC are specified. STATIC , KEYSET , and DYNAMIC cursors default to SCROLL . Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC , KEYSET , and DYNAMIC Transact-SQL cursors.

STATIC Specifies that the cursor always displays the result set as it was when the cursor was first opened, and makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb . Therefore inserts, updates, and deletes made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not detect changes made to the membership, order, or values of the result set after the cursor is opened. Static cursors may detect their own updates, deletes, and inserts, although they are not required to do so. For example, suppose a static cursor fetches a row, and another application then updates that row. If the application refetches the row from the static cursor, the values it sees are unchanged, despite the changes made by the other application. All types of scrolling are supported.

KEYSET Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset . This cursor provides functionality between a static and a dynamic cursor in its ability to detect changes. Like a static cursor, it does not always detect changes to the membership and order of the result set. Like a dynamic cursor, it does detect changes to the values of rows in the result set. Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of key values from all the rows returned by the query statement. With keyset-driven cursors, a key is built and saved for each row in the cursor and stored either on the client workstation or on the server. When you access each row, the stored key is used to fetch the current data values from the data source. In a keyset-driven cursor, result set membership is frozen when the keyset is fully populated. Thereafter, additions or updates that affect membership are not a part of the result set until it is reopened. Changes to data values (made either by the keyset owner or other processes) are visible as the user scrolls through the result set:

• If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2 because the deleted row appears as a gap in the result set. The key for the row exists in the keyset, but the row no longer exists in the result set.
• Inserts made outside the cursor (by other processes) are visible only if the cursor is closed and reopened. Inserts made from inside the cursor are visible at the end of the result set.
• Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

If the query references at least one table without a unique index, the keyset cursor is converted to a static cursor.

DYNAMIC Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor and fetch a new record, regardless of whether the changes occur from inside the cursor or by other users outside the cursor. Therefore all insert, update, and delete statements made by all users are visible through the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors. Updates made outside the cursor are not visible until they are committed (unless the cursor transaction isolation level is set to UNCOMMITTED ). For example, suppose a dynamic cursor fetches two rows and another application then updates one of those rows and deletes the other. If the dynamic cursor then fetches those rows, it will not find the deleted row, but it will display the new values for the updated row.

FAST_FORWARD Specifies a FORWARD_ONLY , READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. This type of cursor does not allow data modifications from inside the cursor.

Both FAST_FORWARD and FORWARD_ONLY can be used in the same DECLARE CURSOR statement.

READ_ONLY Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

SCROLL_LOCKS Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD or STATIC is also specified.

OPTIMISTIC Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNING Specifies that a warning message is sent to the client when the cursor is implicitly converted from the requested type to another.

select_statement Is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE , COMPUTE BY , FOR BROWSE , and INTO are not allowed within select_statement of a cursor declaration.

You can use a query hint within a cursor declaration; however, if you also use the FOR UPDATE OF clause, specify OPTION (<query_hint>) after FOR UPDATE OF .

SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. For more information, see Implicit Cursor Conversions.

FOR UPDATE [OF column_name [ , ... n ]] Defines updatable columns within the cursor. If OF <column_name> [, <... n>] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

DECLARE CURSOR defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. The OPEN statement populates the result set, and FETCH returns a row from the result set. The CLOSE statement releases the current result set associated with the cursor. The DEALLOCATE statement releases the resources used by the cursor.

The first form of the DECLARE CURSOR statement uses the ISO syntax for declaring cursor behaviors. The second form of DECLARE CURSOR uses Transact-SQL extensions that allow you to define cursors using the same cursor types used in the database API cursor functions of ODBC or ADO.

You cannot mix the two forms. If you specify the SCROLL or INSENSITIVE keywords before the CURSOR keyword, you cannot use any keywords between the CURSOR and FOR <select_statement> keywords. If you specify any keywords between the CURSOR and FOR <select_statement> keywords, you cannot specify SCROLL or INSENSITIVE before the CURSOR keyword.

If a DECLARE CURSOR using Transact-SQL syntax does not specify READ_ONLY , OPTIMISTIC , or SCROLL_LOCKS , the default is as follows:

If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY .

STATIC and FAST_FORWARD cursors default to READ_ONLY .

DYNAMIC and KEYSET cursors default to OPTIMISTIC .

Cursor names can be referenced only by other Transact-SQL statements. They cannot be referenced by database API functions. For example, after declaring a cursor, the cursor name cannot be referenced from OLE DB, ODBC or ADO functions or methods. The cursor rows cannot be fetched using the fetch functions or methods of the APIs; the rows can be fetched only by Transact-SQL FETCH statements.

After a cursor has been declared, these system stored procedures can be used to determine the characteristics of the cursor.

Variables may be used as part of the select_statement that declares a cursor. Cursor variable values do not change after a cursor is declared.

Permissions

Permissions of DECLARE CURSOR default to any user that has SELECT permissions on the views, tables, and columns used in the cursor.

Limitations and Restrictions

You cannot use cursors or triggers on a table with a clustered columnstore index. This restriction does not apply to nonclustered columnstore indexes; you can use cursors and triggers on a table with a nonclustered columnstore index.

A. Using simple cursor and syntax

The result set generated at the opening of this cursor includes all rows and all columns in the table. This cursor can be updated, and all updates and deletes are represented in fetches made against this cursor. FETCH NEXT is the only fetch available because the SCROLL option has not been specified.

B. Using nested cursors to produce report output

The following example shows how cursors can be nested to produce complex reports. The inner cursor is declared for each vendor.

@@FETCH_STATUS (Transact-SQL) CLOSE (Transact-SQL) Cursors (Transact-SQL) DEALLOCATE (Transact-SQL) FETCH (Transact-SQL) SELECT (Transact-SQL) sp_configure (Transact-SQL)

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

• Latest Articles
• Top Articles
• Posting/Update Guidelines
• Article Help Forum

• View Unanswered Questions
• View All Questions
• View C# questions
• View C++ questions
• View Javascript questions
• View Visual Basic questions
• View Python questions
• CodeProject.AI Server
• All Message Boards...
• Running a Business
• Sales / Marketing
• Collaboration / Beta Testing
• Work Issues
• Design and Architecture
• Artificial Intelligence
• Internet of Things
• ATL / WTL / STL
• Managed C++/CLI
• Objective-C and Swift
• System Admin
• Hosting and Servers
• Linux Programming
• .NET (Core and Framework)
• Visual Basic
• Web Development
• Site Bugs / Suggestions
• Spam and Abuse Watch
• Competitions
• The Insider Newsletter
• The Daily Build Newsletter
• Newsletter archive
• CodeProject Stuff
• Most Valuable Professionals
• The Lounge  
• The CodeProject Blog
• Where I Am: Member Photos
• The Insider News
• The Weird & The Wonderful
• What is 'CodeProject'?
• General FAQ
• Ask a Question
• Bugs and Suggestions

variable assignment is not allowed in a cursor declaration

Add your solution here

• Read the question carefully.
• Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
• If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

15.6.6.2 Cursor DECLARE Statement

This statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor. To fetch the rows later, use a FETCH statement. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.

The SELECT statement cannot have an INTO clause.

Cursor declarations must appear before handler declarations and after variable and condition declarations.

A stored program may contain multiple cursor declarations, but each cursor declared in a given block must have a unique name. For an example, see Section 15.6.6, “Cursors” .

For information available through SHOW statements, it is possible in many cases to obtain equivalent information by using a cursor with an INFORMATION_SCHEMA table.

madhivanan Premature Yak Congratulator

22864 Posts

SwePeso Patron Saint of Lost Yaks

30421 Posts

RyanRandall Master Smack Fu Yak Hacker

harsh_athalye Master Smack Fu Yak Hacker

X002548 Not Just a Number

15586 Posts

raysefo Constraint Violating Yak Guru

mikadad Starting Member

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

About the Site

Variable Assignment In Cursor Declaration

Hi, here is the code segment below; ... DECLARE find_dates CURSOR FOR SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC' EXEC (@SQL) but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help. thanks

Using Variables In A Cursor Declaration

Cursor declaration blues, dynamic cursor - sorting in declaration, code is getting compiled even columns are not matching in cursor declaration, variable assignment, variable declaration, declaration of record variable, inline variable assignment, dynamic variable used in decimal declaration, t-sql (ss2k8) :: variable declaration in a loop, variable declaration in a stored procedure query, local variable assignment in create trigger, analysis :: dynamic declaration of variable in mdx query, need help understanding a stored procedure variable declaration, help on script component assignment of output variable, parent package variable assignment issue in child package., cursor/variable help, how to use variable in a cursor, t-sql cursor variable.., using cursor variable as a tablename, variable in cursor sql statement (was please help me), set value for variable in a declared cursor, bind variable in cursor, how to iterate through table variable without using cursor, cursor declared with variable in where clause, adding variable to select for cursor, variable as column in cursor select, variable as field name in cursor for update, storing the result into a variable w/o cursor, possible to fetch next from cursor into table variable.

Hello All,I am trying to use a variable(@varStr ) in a cursor declaration. But I am unable to use it. something like:declare @intID as intset @intID  = 1DECLARE curDetailRecords CURSOR FOR (select fnameFrom Customers where id = @intID)Can we not use a variable in a cursor declaration.?ThanksImran

Hi! While working for a client on a SQL Server 6.5 SP5a, I got the following error when running the code below in first SQL Enterprise Manager 6.5 and then SQL Query Analyzer 7.0: IF @Departures = 1 DECLARE TableCursor CURSOR FOR SELECT AcType, BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, DepTime, FlightNumber, ArrStn FROM #TimeCall ORDER BY DepTime, FlightNumber ELSE DECLARE TableCursor CURSOR FOR SELECT AcType, BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, ArrTime, FlightNumber, DepStn FROM #TimeCall ORDER BY ArrTime, FlightNumber And the error I get when the query is run for the first time after switching tool: Server: Msg 202, Level 11, State 2, Procedure CreateFile, Line 178 Internal error -- Unable to open table at query execution time. Server: Msg 202, Level 11, State 1, Procedure CreateFile, Line 188 Internal error -- Unable to open table at query execution time. If I run the query again in one of the tools, it works. It also works if I use WITH RECOMPILE in the stored proc header. If I use the code below, it also works, and without RECOMPILE: DECLARE @SqlStr varchar( 255 ) IF @Departures = 1 SELECT @SqlStr = 'DECLARE TableCursor CURSOR ' + 'FOR SELECT AcType, ' + 'BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, ' + 'BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, ' + 'DepTime, FlightNumber, ArrStn ' + 'FROM #TimeCall ORDER BY DepTime, FlightNumber' ELSE SELECT @SqlStr = 'DECLARE TableCursor CURSOR ' + 'FOR SELECT AcType, ' + 'BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, ' + 'BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, ' + 'ArrTime, FlightNumber, DepStn ' + 'FROM #TimeCall ORDER BY ArrTime, FlightNumber' EXECUTE( @SqlStr ) Trying to get around the problem with the following code did not do any good: DECLARE TableCursor CURSOR FOR SELECT AcType, BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, CurTime = CASE @Departures WHEN 1 THEN DepTime ELSE ArrTime END, FlightNumber, OtherStation = CASE @Departures WHEN 1 THEN ArrStn ELSE DepStn END FROM #TimeCall ORDER BY CurTime, FlightNumber Server: Msg 202, Level 11, State 2, Procedure CreateFile, Line 176 Internal error -- Unable to open table at query execution time. Anyone have some good ideas on why this happens? Brgds Jonas Hilmersson

Hello everybody!I have a small table "ABC" like this:id_position | value---------------------------1 | 112 | 223 | 33I try to use a dynamic cursor as below.When the statement "order by id_position" in declare part of the cursor_abcis omitted - cursor work as it should.But when the statement "order by id_position" is used, cursor behave asstatic one.What's the matter, does anybody know?Code:declare @id_position as int, @value as intDECLARE cursor_abc CURSORFORselect id_position, value from abcorder by id_positionset nocount onopen cursor_abcFETCH NEXT FROM cursor_abcINTO @id_position, @valueWHILE @@FETCH_STATUS = 0BEGINprint @id_positionprint @valueprint '----------------------------'update abc set value=666 --next reading should give value=666FETCH NEXT FROM cursor_abcINTO @id_position, @valueENDCLOSE cursor_abcDEALLOCATE cursor_abcGORegardsLucas

I have some stored procedure and there is a cursor inside it. I added some new columns to the table and those columns I included in the cursor declaration and fetch statement. In the cursor declaration I forgot to add comma (,) in between the new columns. So SQL Server it considers as a alias name for that column so syntactically it is correct. But logically in the cursor declaration having less number of columns than the columns in the fetch statement. So it should throw an error. But the procedure is getting compiled without raising any error. But if I execute the procedure that time it is throwing the error. For example, below I have given the sample procedure. In this procedure, in the cursor declaration I removed the comma (,) between DOB and DOJ. If I compile this procedure it is getting compiled. But when execute that time only it is throwing the error. So I am interested in if any option is available to know the error in the compilation time itself. ALTER PROCEDURE Test AS BEGIN BEGIN TRY DECLARE @empId INT, @fname VARCHAR(50), @dob DATE, @doj DATE [code]....

How do you assign the value returned by a stored procedure to a declared variable and then display the value of that declared variable? The following does not work: declare @variable int set @variable = stored_procedure ... ... show value of @variable?? <HOW> Sorry for the stupid questions but I am new to SQL in general.

hi, i am new to sql server... i have used @ symbol for declaring local variables in stored procedure.... The symbol @@ is there....where to use and what is the purpose of using that symbol... could anyone tell... thanks

Is it possible to set the value for several variables from one select query?

Can someone say how to declare a record like variable in MSSQL-2000 Like: mr_rec record of variables a int, b char(20), c datetime? I could not find any examples on BOL. I want to use this in a stored procedure create script. Thanks, Vinnie

I have to write a query for printing multiple barcodes, depending on the quantity of items that came in the store, based on the order number. DECLARE @num INT SELECT BarCodes.BarCode, BarCodes.ArticleID, ArticlesTrafic.DocumentID, ArticlesTrafic.TrafficQuantity FROM BarCodes INNER JOIN Articles ON BarCodes.ArticleID = Articles.ArticleID INNER JOIN getAutoNumberTable(@num) ON @num=ArticlesTrafic.TrafficQuantity WHERE (ArticlesTrafic.DocumentID = @Param2) The thing i would like to do, is somehow assign a value to @num and pass it to the getAutoNumberTable stored procedure, which generates a table of consequtive numbers, so that each record is displayed multiple times. Is it even possible to do it without using temp tables and loops?

I'm wondering if there's a way to pass a variable to assigning a decimal datatype; declare @intPrecision int set @intPrecision = 3 declare @decVariable decimal(38, @intPrecision) I've basically been given the task by my mentor to create a script to round a decimal to a given number of decimal places. ie; 1234.56789; 2 dp => 1234.57 and not 1234.57000 Any advice would be great.

I am reviewing some code we have inherited (riddled with multiple nested cursors) and in the process of re-writing some of the code. I came across this and it has me puzzled. As I understand it, if you declare a variable and then try to re-declare a variable of the same name an error is generated. If I do this inside a While loop this does not seem to be the case. What ever is assigned is kept and just added to (in the case of a table variable) I understand things are in scope for the batch currently running but I would expect an error to return (example 1 and 2) --Table var declaration in loop SET NOCOUNT ON DECLARE @looper INT = 0 WHILE @looper <= 10 BEGIN DECLARE @ATable TABLE ( somenumber INT ) [Code] ....

I have the following lines embedded in a stored procedure --- ---- ---- select @querystr = "select @lkinpos = pos_lkin_pos from "+@database+" where pos_clnt_id = "+str(@clntid)+" and pos_isin ="+"'"+ @isinno+"'" print @querystr exec(@querystr) --- --- --- When i execute the above stored procedure, it returns an error as follows: Server: Msg 137, Level 15, State 1, Line 1 Must declare the variable '@lkinpos'. When i see the print @querystr statement, it returns the query str as follows: select @lkinpos = pos_lkin_pos from nsdldpm..pos_mstr where pos_clnt_id = 10000045 and pos_isin ='ine227a01011' This when executed independently, gives me the required output. Can anybody solve this for me? Thanks in advance

Hi Guys, i'm batttling with the below Trigger creation __________________________________________________ _ CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS BEGIN DECLARE @v_object_key VARCHAR(80) DECLARE @v_object_name VARCHAR(40) DECLARE @v_object_verb VARCHAR(40) DECLARE @v_datetime DATETIME SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins <--- my problem area!! SET @v_object_name = 'FochiniTable' SET @v_object_verb = 'Create' SET @v_datetime = GETDATE() IF ( USER <> 'webuser' ) INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment) VALUES ('Fochini', @v_object_key, @v_object_name, @v_object_verb, '1', @v_datetime,'0', 'Triggered by Customer CREATE') END ________________________________________________ i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id and the statement: SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000] any help will be appreciated lehare.

Is there a way to write such a query where we can declare the variable dynamically ? Currently I am using the query as shown below : declare @pYear_Internal as NVarchar(100) set @pYear_Internal = [D FISCALPERIOD].[FP CODE].[FP CODE] WITH MEMBER MEASURES.[REVENUE] AS [Measures].[TOTAL REVENUE] SET LAST5YEARS AS STRTOMEMBER(@pYear_Internal).Lag(4) : STRTOMEMBER(@pYear_Internal) [code].... While executing the above query, getting the error - Query (1, 9) Parser: The syntax for '@pYear_Internal' is incorrect.  It looks like it doesn't recognize DECLARE keyword as it does with SQL queries.  I just want a query that runs directly against the server. 

I've been curious why some variables don't need to be declared with a value type in a stored procedure. Please see the example below in the bolded area. The stored procedure works fine - nothing is wrong with it, but I just wanted an explanation on why and when is a value type not needed for a variable. CREATE PROCEDURE [DBO].[EmailRpt] ( @TagDest char(2) = NULL, @DateWanted smalldatetime = NULL, @CustName varchar(64) = NULL, @AcctID AcctId = NULL, @ContactPhn phone = NULL, @CustAddr address = NULL, @City city, @CrossSt varchar(50) , @Access varchar(50) = NULL, @Activity tinyint, ) AS DECLARE @String as varchar(2000), @Header as varchar(200), @MailBox as varchar(50), @ActivityName as Name, @Return as int, @UserName as Name

We have one main package from which 7 other child packages are called. We are using ParentPackage variables to assign values for variables and database connections. While the values from ParentPackage variable get assigned to some of the packages properly, to others it doesn��t assign the value. For example: Except for one of the packages the database connection string gets assigned properly to all other packages. Similarly, in another package one of the variables doesn��t get assigned. In other packages it is assigned properly. We have checked all the other property values and they are exactly the same. We cannot make any head or tail of this erratic behavior. Please Help.

I'm not sure about this one so if someone could help I'd appreciate this. As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a) and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.

if i have the cursor cursor: cursor for select name from sysobjects where type='P' AND category='0' and wants a variable before sysobjects which equals a table name, so itll be: cursor for select name from @variable..sysobjects where type='P' AND category='0' how do i include a variable within a cursor statement

Hello All,Is there a T-SQL equivalent of the PL/SQL cursor rowtype datatype ?Thanks in advance

I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns. I then need to run the following script: declare Detail_Cursor cursor for select @colname, max(len(@colname)) from @table The message I receive is "must declare variable @table". Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated. Can anyone let me know what I'm doing wrong or how I can get the same result. Thanks

Hi All, What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @Where. (I tried with DEBUGGING option of Query Analyzer also). ============================================= SET @Where = '' if IsNull(@Input1,NULL) <> NULL Set @Where = @Where + " AND studentid='" + @input1 +"'" if isnull(@Input2,NULL) <> NULL Set @Where = @Where + " AND teacherid =' " + @Input2 +"'" DECLARE curs1 CURSOR SCROLL FOR SELECT firstname FROM school WHERE school ='ABC' + @where ============================================= Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ? Thanks !

Hi, I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue. ------------------------------------------------------------------------ DECLARE @str_var VARCHAR(10) DECLARE @field_val VARCHAR(10) DECLARE c1 CURSOR LOCAL FOR SELECT field1 FROM tableA WHERE field1 = @str_var WHILE (Sometime TRUE) BEGIN .... SET @str_var = 'set to some values, eg. ABC123, XYZ123' OPEN c1 FETCH c1 INTO @field_val WHILE (@@fetch_status != -1) BEGIN PRINT @field_val ... FETCH c1 INTO @field_val END CLOSE c1 END DEALLOCATE c1 ---------------------------------------------------------------------- Thanks a lots, Vincent

SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.I have a stored procedure which is not working the way I think itshould be.I have a CURSOR which has a variable in the WHERE clause:DECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameIt won't return anything, even when I verify that @dbname has a valueand if I run the query in Query Analyzer with the value, it returnsrows:SELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = 'Archive'ORDER BY tablenameDB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-2909:52:11.823DtsAdtStdArchive_DataSourceType5175768822006-03-2909:52:11.8702006-03-29 09:52:11.887DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-2909:52:12.103I've taken out most of the guts for simplicity, but here's what I'vegot:--CREATE TABLE dbo.db_ind--(--db_ind_tkintIDENTITY,-- id int NULL,-- tablename sysname NOT NULL,-- indid int NULL,-- indexname sysname NOT NULL,-- shcontig1dt datetime NULL,-- defragdt datetime NULL,-- shcontig2dt datetime NULL,-- reindexdt datetime NULL--)ALTER PROCEDURE IDR(@hours int)AS--SET NOCOUNT ON--SET ANSI_WARNINGS OFFDECLARE @tabname varchar(100),@indname varchar(100),@dbname varchar(50),@vsql varchar(1000),@v_hours varchar(4),@shcontig1dtdatetime,@shcontig2dtdatetime,@defragdtdatetime,@reindexdtdatetime,@idint,@indidint,@rundbcursorint,@runtabcursorint,@runindcursorintDECLARE get_dbs CURSOR local fast_forward FORSELECT dbnameFROM db_jobsWHERE idrdate < getdate() - 4or idrdate is nullORDER BY dbnameDECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameDECLARE get_inds CURSOR local fast_forward FORSELECT indid, indexname, defragdt, reindexdtFROM db_indWHERE dbname = @dbnameAND tablename = @tabnameORDER BY indexnameOPEN get_dbsFETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0SELECT @v_hours = CONVERT(varchar,@hours)--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====WHILE @rundbcursor = 1BEGIN -- db whilePRINT '============================='PRINT @dbnamePRINT '============================='--================================================== ================================================== =====--================================================== ================================================== =====OPEN get_tabsFETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0BEGINPRINT 'table: ' + @tabnameSELECT @runtabcursor = 1endELSEBEGINPRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITSSELECT @runtabcursor = 0endWHILE @runtabcursor = 1BEGINPRINT @dbnamePRINT @tabname--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END-- 1st loop through indexesCLOSE get_inds--================================================== ================================================== =====--==========PRINT 'db.tab: ' + @dbname + '.' + @tabname--==========--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'dbname: ' + @dbnamePRINT 'tabname: ' + @tabnamePRINT 'indname: ' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END -- 2nd loop through indexesCLOSE get_inds--================================================== ================================================== =====FETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0SELECT @runtabcursor = 1ELSESELECT @runtabcursor = 0END-- loop through tablesCLOSE get_tabs--================================================== ================================================== =====--================================================== ================================================== =====PRINT 'Index Maintenence complete. Job report in[DB_Rpt_Fragmentation]'PRINT ''FETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0END -- loop through databasesCLOSE get_dbsdeallocate get_dbsdeallocate get_tabsdeallocate get_inds--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====GOAnd this is what I'm getting:=============================Archive=============================(0 row(s) affected)not getting any tables!Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]......etc.Am I missing something obvious?Thank you for any help you can provide!!

Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...

When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.

I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable. The following fails to parse: Declare Cursor1 Cursor For 'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name' Open cursor1 That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.

I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example: declare @col varchar(50) set @col = 'Temperature' declare notifycurs cursor scroll for select @col from Table Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC (ie. exec('select '+@col+' from Table' ) but that gave me an error. Is there a way to pass in a variable for a column name for a curor select statement????

I'm trying something like: UPDATE tbl SET @varFieldName = @varValue The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd. I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then. To get the field name, I : SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars' Thanks, Carl

Hello All, A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor. Thank you in advance

Hello, I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like: Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table it does not work. any help would be great. thnx

• Submit Resource
• Advance Search

A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query.

The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1 .

• Line (1) introduces the declaration section.
• Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation T1 . Although we know these types are INTEGER , we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type NUMBER ).
• Lines (4) through (8) define the cursor T1Cursor . It ranges over a relation defined by the SELECT - FROM - WHERE query. That query selects those tuples of T1 whose first component is less than the second component. Line (8) declares the cursor FOR UPDATE since we will modify T1 using this cursor later on Line (14). In general, FOR UPDATE is unnecessary if the cursor will not be used for modification.
• Line (9) begins the executable section of the program.
• Line (10) opens the cursor, an essential step.
• On Line (12), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the tuple retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type.
• On Line (13), a test for the loop-breaking condition. Its meaning should be clear: %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples.
• On Line (14), a SQL DELETE statement that deletes the current tuple using the special WHERE condition CURRENT OF T1Cursor .
• On Line (15), a SQL INSERT statement that inserts the reverse tuple into T1 .
• Line (17) closes the cursor.
• Line (18) ends the PL/SQL program.
• Lines (19) and (20) cause the program to execute.

Assigning values to cursor variables

Result sets can be assigned to cursor variables at different times and in multiple ways using the SET statement.

About this task

Once cursor variables have been assigned values, the cursor variables and cursor variables field values can be assigned and referenced.

Pls-00208 Identifier Is Not a Legal Cursor Attribute

Action: Replace the first argument with the name of a legally declared exception. Action: Set the cursor parameter to IN OUT or OUT. Then repeat the process. – The ROWCOUNT attribute does not indicate the actual number of rows until you have traversed the entire cursor. In other words, you should not rely on this attribute to indicate how many rows are in a cursor after it is opened. Cause: A binding variable, i.e. a colon-prefixed identifier, was found in an inappropriate context. Cause: An attempt was made to declare a cursor variable in a package specification, which is not allowed. Although REF CURSOR types can be defined in a block, subroutine, or PL/SQL package, cursor variables can only be declared in a block or subroutine. PLS-00701: Illegal ORACLE Error Number String for PRAGMA EXCEPTION_INIT You are trying to create a table with %type attributes, which is not allowed. Action: Report the internal error to your customer service representative or do one of the following, depending on the issue: – Review the application logic to use default parameter types and expressions supported at local and remote sites. – Perform the upgrade or downgrade correctly and make sure that the correct versions of all system packages are installed.

– Report the legal syntax error to your customer service representative. If you see row and column numbers with the error message, they can help you find a workaround. For example, try recoding the faulty line to avoid the error. Action: Change the cursor return type or select list so that the number of column values returned matches the number of items in the select list. Action: Edit the declaration or do not apply the %ROWTYPE attribute to the identifier. Cause: An attempt was made to create an index table using cursor variables. Cause: An attempt was made to reference a remote cursor attribute, which is not allowed. For example, the code might look like this: A literal, constant, IN, loop counter, or function call parameter was incorrectly used as the target of an assignment. For example, the following statement is invalid because assignment target 30 is a literal: When you declare a constant or variable, use the %TYPE attribute to automatically provide the data type of a column. When declaring a record, use the %ROWTYPE attribute to automatically provide the data types for a row.

Cause: The prefix in a qualified name was a subroutine or cursor that was not in a perimeter area. That is, a subroutine or cursor name is used as a scope qualifier for a reference to an entity (within that subroutine or cursor) that is outside the scope. Example: declare the number x; Type T1 is the registration (a number); The function f returns t1 is a number; start x := f.a; — the lawful use of the “f” function as a scope qualifier; — solves the local variable “a” in function “f”. x := f().a; — legal reference to component “A” of the end of registration returned; start x := f.a; — unlawful use of the function “f” as a scope qualifier x:= f().a; — legal reference to component “A” of the end of registration returned; Action: Check the spelling and declaration of the cursor name and identifiers referenced in the cursor declaration. Also, make sure that the declaration is correctly placed in the block tree. If a return type is specified, make sure that it references an existing database table or a previously declared cursor or cursor variable. Cause: The evaluation value in the LIMIT clause of a bulk checkout was found in an inappropriate context. For example, the following statement is invalid because the LIMIT clause expects a numeric value in a bulk checkout.

C1 BULK COLLECTION WITHIN var_tab LIMIT `22333`; – Illegal action: Make sure the data type identifier defines a valid type. Remember to use the %TYPE and %ROWTYPE attributes if necessary. Cause: The %LAST attribute must be applied to an enumeration type (or a subtype of an enumeration type). This error occurs when “%LAST” follows a name that is not declared in this way. Action: Check the spelling of all identifiers in the declaration. If necessary, move the declaration so that it no longer contains references. Action: Remove the invalid formal parameter reference. Cause: Once one subtype was defined as NOT NULL, it was used as the base type for another subtype defined as NULL.

This is not allowed. For example, the code might look like this: DECLARE SUBTYPE Weekday IS INTEGER NOT NULL; SUBTYPE Weekend IS WEEK DAY ZERO; — illegal instead of DECLARE SUBTYPE Weekday IS INTEGER NOT ZERO; The SUBTYPE weekend is a weekday; Cause: Modifying the attribute did not extend the constraints or attempt to change the attribute type. For attributes of type NUMBER, the scale and precision can be changed to allow an increase in the number of digits after and before the decimal point. The size can be increased for VARCHAR2 and RAW attributes. Do not modify other type attributes. Changing the attribute type is also not allowed. Cause: A cursor was declared with a SELECT statement containing duplicate column names. These references are ambiguous.

Action: Check the spelling of the identifier in the WHEN clause, and replace the function call with an exception name. Cause: A FETCH statement could not assign a value to an assignment target in its IN list because the target is not a legally formed and declared variable. For example, the following assignment is not allowed because `Jones` is a string and not a variable: Cause: This exception is thrown for the following errors: – The expression in the INTO clause of the OPEN or RETURNING statement does not belong to either the legal SQL data type or the data type PL/SQL RECORD – A collection of records is used in the INTO clause of the OPEN or GIVING statement.