Showing posts with label declaration. Show all posts
Showing posts with label declaration. Show all posts

Tuesday, March 27, 2012

CURSOR INSENSITIVE

Hi,

DECLARE MyCursor CURSOR READ_ONLY FOR Select statement

it seems I could not use INSENSITIVE in this cursor declaration, how can I accomplish that?

this works

DECLARE MyCursor2 INSENSITIVE CURSOR FOR Select * From sys.sysobjects FOR READ ONLY

and here is the syntax for the DECLARE CURSOR via BOL for 2005, you can use either syntax:

SQL 92 Syntax

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

[;]

Transact-SQL Extended Syntax

DECLARE cursor_name CURSOR[ LOCAL | GLOBAL ][ FORWARD_ONLY | SCROLL ][ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ][ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ][ TYPE_WARNING ]FOR select_statement[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]|||

INSENSITIVE in the declaration is a SQL-92 syntax, and READ_ONLY is not. So they are part of two different versions of cursor syntax (confusingly smashed together.

Check books online in the DECLARE CURSOR topic and it shows the two different syntaxes together.

Why do you think you need both? INSENSITIVE implies read only: INSENSITIVE: "modifications made to base tables are not reflected in the data returned by fetches made to this cursor"

And to answer your other question here. Cursors are pretty horrible for you SQL performance if you can get around their use. If you can't think of a way to avoid using them, post here and someone will help you :)

|||Please update thread Jim.sql

Sunday, March 25, 2012

Cursor Declaration

Hello All!
I am trying to declare a cursor and I keep on gettin the following error
when I try and debug: Incorrect syntax near the keyword 'declare'. I am
stumped and becuase I have even tried using the numerous exmaples
listed on the internet and modified the select clause.
PLEASE HELP!
Here is my stored PRoc code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE FXSecondPart
AS
BEGIN
--SET NOCOUNT ON;
declare @.1m as float
declare @.2m as float
declare @.spot as float
declare @.@.ValDt as datetime
declare @.@.PayAmt as money
declare @.@.RcvAmt as money
declare @.BS as char(1)
declare @.CCY as char(3)
declare @.Sell
declare pubcrsr cursor
for select ISIN
from AllTrades
FOR READ ONLY
if i parse this I get a error!READ_ONLY not READ ONLY
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
and also
declare @.Sell needs a type maybe int?
declare @.Sell int
http://sqlservercode.blogspot.com/|||umm.. what type exactly is the @.Sell variable?
:)
Peter
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||based on what you've posted
1)there should be a END associated with the BEGIN
2) add a datatype to declare @.Sell
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Chris Allison" <ChrisAllison@.discussions.microsoft.com> wrote in message
news:63674EDA-96BF-41B0-A30C-19F6D254A5C1@.microsoft.com...
> Hello All!
> I am trying to declare a cursor and I keep on gettin the following error
> when I try and debug: Incorrect syntax near the keyword 'declare'. I am
> stumped and becuase I have even tried using the numerous exmaples
> listed on the internet and modified the select clause.
> PLEASE HELP!
> Here is my stored PRoc code:
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE FXSecondPart
> AS
> BEGIN
> --SET NOCOUNT ON;
> declare @.1m as float
> declare @.2m as float
> declare @.spot as float
> declare @.@.ValDt as datetime
> declare @.@.PayAmt as money
> declare @.@.RcvAmt as money
> declare @.BS as char(1)
> declare @.CCY as char(3)
> declare @.Sell
> declare pubcrsr cursor
> for select ISIN
> from AllTrades
> FOR READ ONLY
>
> if i parse this I get a error!
>|||Hi SQL
That doesnt work! If I parse this I get a incorrect syntax near declare, the
same error!!
It cant be a permissions problem as I have SA privilidges. coulld it be
another system setting? or perhaps im declaring it in the wrong 'section' of
my stored procedure. Im using SQL Express 2005
declare pubcrsr cursor READ_ONLY
for select ISIN
from AllTrades
OPEN pubcrsr
WHILE @.@.Fetch_Status <>-1
Begin
FETCH NEXT from pubcrsr
INTO @.@.ValDt, @.@.PayAmt, @.@.RcvAmt
--SELECT @.spot = (SELECT LastTrade FROM HistoricalFX WHERE (CCY = @.CCY) AND
--(Date = @.p1))
--SELECT @.1m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '1M') AND (Date = @.p1) )
--SELECT @.2m = (SELECT LastTrade FROM HistoricalFXFwrd WHERE (CCY = @.CCY)
AND
--(Period = '2M') AND (Date = @.p1) )
-- pass @.spot, @.1m, @.2m, @.valdt, to calculating procedure
-- with the returned value calculate the PnL and put this in a 'counter'
FETCH NEXT from pubcrsr
End
Close pubcrsr
Deallocate pubcrsr
"SQL" wrote:

> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>|||THATS IT!!! I know it was a simple problem Thanks Guys it was the @.sell
variable which ive deleted!!
Great Stuff!
"SQL" wrote:

> READ_ONLY not READ ONLY
> declare pubcrsr cursor READ_ONLY
> for select ISIN
> from AllTrades
>
> and also
> declare @.Sell needs a type maybe int?
> declare @.Sell int
>
> http://sqlservercode.blogspot.com/
>

Thursday, March 22, 2012

Cursor

hello; I have an error on the level of declaration of cursor for update:

Sqlstate 37000: the clause update is authorized only for DECLARE CUSOR. which is the solution?

can you post in English? what is the declare statement that caused this error ?|||I don't know French, but did you declare your cursor FOR UPDATE? If you try to update columns that are not allowed, you will get an error. I can tell you error says something about clause UPDATE. Also make sure that your syntax is correct on the cursor. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp|||

I have an error in declaration of Cursor.

sql state : 37000 :The Clause Update is autorized only for Declare Cursor.

|||Can you post the SQL? Sounds like you are doing an update on a non-updatable cursor or the syntax is incorrect.|||the problem is in ODBC for SqlServer 2005?