CURSOR trouble (:

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
*
FROM
school
WHERE
school ='ABC' + @.where
=============================================

It gives me all the Records inside the SCHOOL='ABC' ...

Please check my SQL Above and Could somebody tell me , how can I attach the VARIABLE with CURSOR sql statement ?

Please advice me..(:

Thanks !Well, looks like another day is over, so it is time to repeat the (same old) basic thing about SQL again.

You can NOT just put up part of a SQL Statement into a variable and then think the SQL compiler acutlly magically produces dynamic SQL out of it.

That simple.

In your example:

::DECLARE curs1 CURSOR SCROLL
::FOR SELECT
::*
::FROM
::school
::WHERE
::school ='ABC' + @.where

@.where will NOT be appened to the SQL statement. It will be part of the right part of the comparison.

So, if @.where consists of (example)

::AND studentid='22'

it will check whether schoon equals "ABC AND studentid='22'".

That simple.

If you want to execute a dynamic SQL thing, then do so. There are means for this. But otherwise, variables are variables, not some "post compiler magic thing that happens when you dance around the computer at night singing magic chants".

Interesting enough you are not alone. Lots of people think like this - as I said in the beginning, some typical (beginner) error like this pops up every second day. Most, though, stumble opver it before they get into using / abusing cursors.|||

SELECT *
FROM
 school
WHERE
 school ='ABC'
 AND
 ( @.Input1 IS NULL OR @.Input1 = StudentID )
 AND
 ( @.Input2 IS NULL OR @.Input2 = teacherID )

>>using / abusing cursors

So true.... many developers often try to solve T-SQL problems by using cursors, similar to the way they might program in other environments—working with one record at a time in sequential order. But this approach doesn't exploit the power of SQL Servers "engine", which is optimized for set-based processing. Cursors usually require considerably more code than their equivalent set-based solutions and are much less efficient.

If a cursor is the first thing that you think of when you face a T-SQL problem, you have some unlearning to do—you need to start thinking "set-based.

http://www.windowsitpro.com/SQLServer/Article/ArticleID/22431/22431.html|||Christmas is supposed to be about giving so here we go. Some good advice already provided (though not entirely correct). First of all cursor or no cursor and trying to concatenate a where clause or not why are you doing this: if IsNull(@.Input1,NULL) <> NULL ?

For one thing when ever evaluating NULL always use: IF @.Input1 IS NULL or in your specific example IF @.Input1 IS NOT NULL

As for using the ISNULL function I suggest that you use this only when you suspect that the value is null or perhaps in your case an empty string in which case thew correct syntax would be: IF ISNULL(@.Input1, '') <> ''

Also, regardless of if you really need a cursor or not from the example you have provided you can generate the select as follows:

SELECT * FROM school
WHERE school = 'ABC' AND studentid LIKE ISNULL(@.Input1, '') + '%' AND teacherid LIKE ISNULL(@.Input2, '') + '%' ...

Now if you still really feel that you have to concatenate a where clause it can in fact be done but it is ugly! It works like this (but is only viable for returning a result set, in other words you cannot use it for a CURSOR!):

EXEC('SELECT * FROM school WHERE ' + @.Where)

Last but not least, as one of the other respondents suggested CURSOR's should be avoided at all costs and as such there are a number of other ways to apply updates to a group of records with some pretty flexible conditional logic.

Step 1: select the target records into a temporary table i.e.

SELECT [primary_key_field] INTO #target
FROM school WHERE (school = 'ABC')
AND (studentid LIKE ISNULL(@.Input1, '') + '%')
AND (teacherid LIKE ISNULL(@.Input2, '') + '%') ...

Now that you have the keyset available you can do a simple update to all the records in the resultset as follows:

UDPATE school
SET [whatever_field] = [whatever_value]
FROM school, #target
WHERE school.[primary_key_field] = #target.[primary_key_field]

For more sophisitcated updates you can use a flavour of the CASE in sql such as

SET [whatever_field] = CASE WHEN [condition1] = True THEN
value1
WHEN [condition2] = True THEN
value2
...
END

for as many fields as it takes.

Hope this helps.

Cheers

Gary|||Thanks to you all people up there...it solved my problem...Have a wonderful Holidays...:)sql

cursor select and variables

I have problems to place my variable into the select statement.

DECLARE @.DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @.DB_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.DB_NAME; --works fine

Select NAME, FILEDIRECTORY FROM @.DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\\%');

FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @.DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor

GO

How could i use a variable like @.DB_Name in my select ?

the object against which you run a query cannot be a variable. You need use dynamic SQL by first constructing your SQL query as a string and then using the EXEC command or the sp_execute_sql system stored procedure. e.g.

declare @.sql VARCHAR(4000)

set @.sql = 'Select NAME, FILEDIRECTORY FROM ' + @.DB_NAME + '.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE ''%\\%'');'

EXEC (@.sql)

Cursor or Table For Stored Procedure ?

Hello,
I have the following probleman :
I have a stored procedure that retrieve results of a select statement,
and i would like to store this results in a cursor or temporary table
or something like that.
Could u help me ? Tks...
Code that i tried :
create procedure ListOfProducts
@.categoryID int
as begin
select * from Product
end
declare c int
declare c cursor for ListOfProducts 1
open c
fetch next from c into @.idProduct
print @.idProduct
close c
deallocate cHi,
If you want to store a stored procedure result into a temporary table, use
INSERT/EXEC statement. Create a temporary table first using CREATE TABLE.
Tomasz B.
"Carlao" wrote:

> Hello,
> I have the following probleman :
> I have a stored procedure that retrieve results of a select statement,
> and i would like to store this results in a cursor or temporary table
> or something like that.
> Could u help me ? Tks...
> Code that i tried :
> create procedure ListOfProducts
> @.categoryID int
> as begin
> select * from Product
> end
> declare c int
> declare c cursor for ListOfProducts 1
> open c
> fetch next from c into @.idProduct
> print @.idProduct
> close c
> deallocate c
>|||If you want the instances of sql server, then you can use SQL-DMO API,
specifically the ListAvailableSQLServers Method of the Application object.
http://www.microsoft.com/resources/... />
c3561.mspx
AMB
"Carlao" wrote:

> Hello,
> I have the following probleman :
> I have a stored procedure that retrieve results of a select statement,
> and i would like to store this results in a cursor or temporary table
> or something like that.
> Could u help me ? Tks...
> Code that i tried :
> create procedure ListOfProducts
> @.categoryID int
> as begin
> select * from Product
> end
> declare c int
> declare c cursor for ListOfProducts 1
> open c
> fetch next from c into @.idProduct
> print @.idProduct
> close c
> deallocate c
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> If you want the instances of sql server, then you can use SQL-DMO API,
> specifically the ListAvailableSQLServers Method of the Application object.
> http://www.microsoft.com/resources/...>
0/c3561.mspx
>
> AMB
> "Carlao" wrote:
>|||You can create a temporary table (local or global) or a permanent one to gra
b
the result of the sp. You can also return a cursor variable from your sp.
Example:
use northwind
go
create procedure proc1
@.sd datetime,
@.ed datetime
as
set nocount on
declare @.rv int
set @.rv = 1
execute @.rv = dbo.[Sales by Year] @.sd, @.ed
return coalesce(nullif(@.rv, 0), @.@.error)
go
create procedure proc2
@.sd datetime,
@.ed datetime
as
set nocount on
create table #t (
ShippedDate datetime,
OrderID int,
Subtotal money,
col_year int
)
insert into #t
exec proc1 @.sd, @.ed
select * from #t
where Subtotal between 2000.00 and 3000.00
order by ShippedDate
drop table #t
return
go
execute proc2 '19960101', '19981231'
go
drop procedure proc2, proc1
go
AMB
"Carlao" wrote:

> Hello,
> I have the following probleman :
> I have a stored procedure that retrieve results of a select statement,
> and i would like to store this results in a cursor or temporary table
> or something like that.
> Could u help me ? Tks...
> Code that i tried :
> create procedure ListOfProducts
> @.categoryID int
> as begin
> select * from Product
> end
> declare c int
> declare c cursor for ListOfProducts 1
> open c
> fetch next from c into @.idProduct
> print @.idProduct
> close c
> deallocate c
>

cursor insert with like %

Hi I have a weird problem I want to cursor thru the values in a
temporary table and use the values to do a select statement to insert
into another temporary table...This select statement uses a like clause

something like where...when I take off the insert still nothing comes
back from the select...when I hardcode values it works...I get
results...is there something wrong with appending a +'%' to a value
read from a cursor?

DECLARE @.DEPT VARCHAR(65)
SET @.DEPT = "00201,00203"

DECLARE @.TB_ABSENCES TABLE(DeptOrEmpId VARCHAR(65))
DECLARE @.TB_DEPT TABLE ( V_DEPARTMENT_CODE VARCHAR(128) )

INSERT INTO @.TB_DEPT (V_DEPARTMENT_CODE)
SELECT V_DEPT FROM [ISIS].[dbo].[FU_GET_DEPTS_FROM_STRING](',', @.DEPT)

DECLARE DEPTS CURSOR FAST_FORWARD FOR
SELECT V_DEPARTMENT_CODE+'%' FROM @.TB_DEPT

OPEN DEPTS
FETCH NEXT FROM DEPTS INTO @.DEPT_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN

--INSERT INTO @.TB_ABSENCES TABLE
SELECT Code from TB_EMPLOYEE_DEPARTMENT T2
WHERE T2.V_HIERARCHY_CODE LIKE @.DEPT_CODE + '%'

FETCH NEXT FROM DEPTS INTO @.DEPT_CODE

END

CLOSE DEPTS
DEALLOCATE DEPTSyurps (yurps@.yahoo.co.uk) writes:
> Hi I have a weird problem I want to cursor thru the values in a
> temporary table and use the values to do a select statement to insert
> into another temporary table...This select statement uses a like clause
> something like where...when I take off the insert still nothing comes
> back from the select...when I hardcode values it works...I get
> results...is there something wrong with appending a +'%' to a value
> read from a cursor?

I would guess that there are trailing spaces. Rewrite as:

DECLARE @.DEPT VARCHAR(65)
SET @.DEPT = "00201,00203"

DECLARE @.TB_ABSENCES TABLE(DeptOrEmpId VARCHAR(65))

INSERT INTO @.TB_ABSENCES TABLE
SELECT Code
from TB_EMPLOYEE_DEPARTMENT T2
JOIN [ISIS].[dbo].[FU_GET_DEPTS_FROM_STRING](',', @.DEPT) D
ON T2.V_HIERARCHY_CODE LIKE rtrim(V_DEPT) + '%'

Yeah, that's right. No cursor. There is no need for it, and it could
be costly in terms of performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh gosh !! a couple hours wasted on something that I didn't need
to...

thanks!

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

CURSOR FETCH STATEMENT IS HANGING

Hi All,
I have a stored proc that uses a cursor to iterate over the join resultset
of 5 tables. After fetching roughly 12,000 records and running for about 30
minutes, the next FETCH statement inside the WHILE loop hangs. This happens
consisently. I tried to do a commit / checkpoint after every 10,000 records,
but the problem persists.
Can any one please provide some thoughts about why this could be happening?
ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
development database.
Thanks,
RajeshWhat are you trying to accomplish?
Why are you using cursors?
What kind of cursor are you using?
Any research about a set-based solution?
Can we see some code?
AMB
"rajeshlh" wrote:

> Hi All,
> I have a stored proc that uses a cursor to iterate over the join resultse
t
> of 5 tables. After fetching roughly 12,000 records and running for about 3
0
> minutes, the next FETCH statement inside the WHILE loop hangs. This happen
s
> consisently. I tried to do a commit / checkpoint after every 10,000 record
s,
> but the problem persists.
> Can any one please provide some thoughts about why this could be happening
?
> ps: The tempdb size is 955 MB and the disk size is 33 GB. This is our
> development database.
> Thanks,
> Rajesh
>|||I have to process a set of records obtained from the join of 5 tables. For
each record in the resultset, i need to insert/update into 5-6 tables, plus
i
need to write to a log table the key for each inserted row or updated row.
Following is the code.
The FETCH FROM Subscription_Cursor fails inside the While Loop after some
11,000 records. Since iam writing debug statements to another table, i can
say that after processing 11000 records inside the cursor, the FETCH
statement freezes.
I did think about SET-based approach, but the processing logic forces me to
use a cursor. The Job is run nightly and processes about 100,000+ records.
Following is the SP Code.
CREATE PROCEDURE TEST_SP
AS
--Variables to store Name record values
DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
@.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
@.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
varchar(11),@.Fax_Number varchar(11)
--Variables to store Product record values
DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
@.Current_Email_Address varchar(50),@.Email_Password varchar(50)
--Variables to store Order record values
DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
varchar(2),
@.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
datetime,@.Order_Entry_Type varchar(5)
--Variables to store Demographic record values
DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
varchar(1024)
--variales to store newly created reg_visitor_id,account_id and order_id -
for subscriptions not existing in Elogic Reg DB
DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
integer,@.Address_Id integer
--variables to store values present in staging tables - For subscriptions
already existing in Elogic Reg DB
DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
--variables used for logging
DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
@.Log_Summary_Id integer,@.Source_File_Name varchar(100)
--Variables to store Donor's data
DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
@.Donor_State_Prov varchar(2),@.Donor_Country_Code
varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
@.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
--Variables to store CDS to Elogic Converted values
DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
--Misc variables
DECLARE @.SP_NAME varchar(50),@.Ret integer
DECLARE @.Reg_Visitor_Product varchar(100)
DECLARE @.Exec_Start_Time datetime
DECLARE @.Promo_Code_Pos2 varchar(1)
DECLARE @.Delivery_Type varchar(1)
DECLARE @.Cnt int
--Cursor to subscriptions stored in the staging tables
DECLARE Subscriptions_Cursor CURSOR FOR
SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_name
,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_na
me,nr.job_title,nr.company_name,
nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code,nr
.telephone,nr.fax_number,
pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.num
_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.ord
er_net_value,ord.source_code,ord.medium_code,ord.document_key,
ord.setcode,ord.orig_start_issue,ord.order_entry_type,
dr.version_number,dr.segment_number,dr.demo_data,
delivery_type
FROM cds_name_record nr
JOIN cds_product_record pr
ON nr.account_number=pr.account_number
AND nr.publisher_code=pr.publisher_code
AND nr.mag_code=pr.mag_code
JOIN cds_order_record ord
ON ord.account_number=pr.account_number
AND ord.publisher_code=pr.publisher_code
AND ord.mag_code=pr.mag_code
JOIN cds_demographic_record dr
ON dr.account_number=pr.account_number
AND dr.publisher_code=pr.publisher_code
AND dr.mag_code = pr.mag_code
JOIN publication_subscription ps
ON ps.external_multi_mag_code = pr.publisher_code
AND ps.code = pr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address
AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
Non-Gift and Donee Orders
AND ord.order_status='B' --Get only Base Orders
AND dr.demo_status = 'B' --Get only Base Demo Records
--AND nr.account_number <>'0010115103'
ORDER BY CAST(nr.account_number AS int)
--cursor to the reg_feed_detail_log table , used for populating the
reg_feed_summary_log table
DECLARE Summary_Cursor CURSOR FOR
SELECT
publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*) a
s
summary_count
FROM reg_feed_detail_log
GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
SET @.Exec_Start_Time = CURRENT_TIMESTAMP
OPEN Subscriptions_Cursor
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
SET @.Cnt =1
PRINT 'start'
WHILE @.@.FETCH_STATUS = 0
BEGIN --B1
INSERT INTO Demo_data values ('inside while loop',null,null)
INSERT INTO debug_table
(eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code,m
ag_code,order_status,set_code,delivery_t
ype,seq)
VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_p
ublication_id,@.Account_Number
,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
@.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
NULL,@.Donor_Fax_Number = NULL,
@.Donor_Account_number = NULL
SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
SELECT @.Promo_Code_Pos2 = NULL
IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
BEGIN --B2
SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
nr.postal_code,@.Donor_Company_Name = nr.company_name,
@.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
nr.address_line_2,@.Donor_City = nr.city,
@.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
nr.fax_number
FROM cds_name_record nr
JOIN cds_order_record ord
ON ord.account_number=nr.account_number
AND ord.publisher_code=nr.publisher_code
AND ord.mag_code=nr.mag_code
WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
Get Only current address Name record
AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Order
or Non-Subscibing Donor Order
AND nr.publisher_code = @.Publisher_Code
AND nr.mag_code = @.Mag_Code
AND ord.order_number = @.Order_Number
-- Note : Donor and Donee will have different account numbers, but same
publisher code, mag code and order number
END --END B2
SET @.eLogic_Account_Status = CASE
WHEN @.Service_Status = 'A' THEN 'A'
WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
WHEN @.Service_Status = 'C' THEN 'X'
WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
END
SET @.eLogic_Pay_Type = CASE
WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
ELSE ''
END
SET @.eLogic_Auto_Renew= CASE
WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN 1
ELSE 0
END
SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
SET @.eLogic_Account_Type=CASE
WHEN @.Source_Code = 'CC' THEN
CASE
WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
ELSE 'CONTROLLED'
END
WHEN @.Source_Code = 'CA' THEN 'COMP'
ELSE 'PAID'
END
SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
FROM account A
JOIN publication_subscription PS
ON A.pub_code = PS.code
WHERE A.account_number = @.Account_Number
AND PS.external_multi_mag_code = @.Publisher_Code
--If a reg_visitor is not determined in the Staging tables or by looking up
the Account table , a new reg_visitor
--is created
IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
BEGIN --B3
--No Reg_Visitor corresponding to the mag subscription - mag subscription
generated at CDS
--Create a new reg_visitor and associated rows in Account,Account_Order
and visitor_demographic
SELECT @.Reg_Visitor_Product = master_brand
FROM publication
WHERE external_multi_mag_code=@.Publisher_Code
--insert into reg_visior table
EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTPUT,
@.p_common_name = @.Common_Name,
@.p_company_name = @.Company_Name,
@.p_email = @.Current_Email_Address,
@.p_encrypted_password = @.Email_Password,
@.p_given_name = NULL,
@.p_login_id = @.Email_User_Name,
@.p_merged_visitor_id = NULL,
@.p_middle_initial = NULL,
@.p_middle_name = NULL,
@.p_name_suffix = NULL,
@.p_password = @.Email_Password,
@.p_product = @.Reg_Visitor_Product,
@.p_subproduct = NULL,
@.p_professional_title = @.Job_Title,
@.p_record_status = 1,
@.p_registration_level = 1,
@.p_salutation = NULL,
@.p_sur_name = NULL,
@.p_zip = NULL,
@.p_country = 'TESTDTS'
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAME)
--ROLLBACK TRAN T1
RETURN -2
END
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'reg_visitor',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = NULL,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--insert into address table
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Company_Name,
@.p_address_line_1 = @.Address_Line_1,
@.p_address_line_2 = @.Address_Line_2,
@.p_city = @.City,
@.p_postal_code = @.Postal_Code,
@.p_state_prov = @.State_Prov,
@.p_country_code = @.Country_Code,
@.p_phone = @.Telephone,
@.p_fax = @.Fax_Number,
@.p_address_type = 0 --shipping
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
BEGIN
--Insert the corresponding Donor Address
EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
@.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_company_name = @.Donor_Company_Name,
@.p_address_line_1 = @.Donor_Address_Line_1,
@.p_address_line_2 = @.Donor_Address_Line_2,
@.p_city = @.Donor_City,
@.p_postal_code = @.Donor_Postal_Code,
@.p_state_prov = @.Donor_State_Prov,
@.p_country_code = @.Donor_Country_Code,
@.p_phone = @.Donor_Telephone,
@.p_fax = @.Donor_Fax_Number,
@.p_address_type = 1 --Billing
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into address for donor')
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'address',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Address_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
--IF @.Account_Number = '0010115103' OR @.Cnt =11214
--INSERT INTO demo_data values ('inserted into detail log for donor
address')
END
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -3
END
--Insert into Account table
EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id OUTPUT,
@.p_account_number = @.Account_Number,
@.p_pub_code = @.Mag_Code,
@.p_account_type = @.eLogic_Account_type ,
@.p_status = @.eLogic_Account_Status,
@.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
Order, NULL will be inserted for Supp_Account_Number
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -4
END
INSERT INTO Demo_data values ('completed inserting into account
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = NULL,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed inserting into detail log for
account table',@.Reg_Visitor_id,@.Account_Number)
--insert into account_order table
EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
@.p_account_id = @.Account_Id,
@.p_order_id = @.Order_Id OUTPUT,
@.p_term = @.Order_Term,
@.p_term_unit = NULL,
@.p_pay_type = @.eLogic_Pay_Type,
@.p_net_amt = @.Order_Net_Value,
@.p_quantity = @.Num_Copies,
@.p_vendor_order_number = @.Order_Number,
@.p_promo_response_key = @.Document_Key,
@.p_number_of_installments=1,
@.p_auto_renew =@.eLogic_Auto_Renew
IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
BEGIN
RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -5
END
INSERT INTO Demo_data values ('completed inserting into account_order
table',@.Reg_Visitor_id,@.Account_Number)
--Log details to reg_feed_detail_log
EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
@.P_Pub_Code = @.Mag_Code,
--@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
@.P_Source_Key_1 = NULL,
@.P_Source_Key_2 = NULL,
@.P_Source_Key_3 = NULL,
@.P_Target_Type = 'account_order',
@.P_Target_Key_1 = @.Reg_Visitor_Id,
@.P_Target_Key_2 = @.Account_Id ,
@.P_Target_Key_3 = @.Order_Id,
@.P_Process_Type = 'INSERT',
@.P_Source_File_Name = @.Source_File_Name
INSERT INTO Demo_data values ('completed logging to detail log table for
account_order',@.Reg_Visitor_id,@.Account_
Number)
--insert into visitor_demographics table only for Online Magazines ;
Delivery Type W - Online, P- Print
IF(@.Delivery_Type = 'W')
BEGIN
INSERT INTO Demo_data values ('calling demographcis
sp',@.Reg_Visitor_id,@.Account_Number)
EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id,
@.Mag_Code = @.Mag_Code,
@.Demo_Data = @.Demo_Data,
@.Segment_Number = @.Segment_Number,
@.Version_Number = @.Version_Number
IF ( @.@.error <> 0 OR @.Ret < 0 )
BEGIN
RAISERROR('%s: Error inserting/updating into Visitor_Demographics
table!', 18, 6, @.SP_NAME)
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
RETURN -6
END
END
INSERT INTO Demo_data values ('DEBUG LINE
HIT',@.Reg_Visitor_id,@.Account_Number)
END --END B3
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('About to fetch next row- current row details
-->',@.Reg_visitor_id,@.Account_number)
FETCH NEXT FROM Subscriptions_Cursor
INTO
@.eLogic_Reg_Visitor_Id,@.eLogic_Publicati
on_Id,@.Source_File_Name,@.Account_Num
ber ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
@.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
@.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
@.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
@.Current_Email_Address,
@.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
@.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
@.Version_Number,@.Segment_Number,@.Demo_Da
ta,@.Delivery_Type
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
SET @.Cnt = @.Cnt + 1
INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
END --END B1
CLOSE Subscriptions_Cursor
DEALLOCATE Subscriptions_Cursor
"Alejandro Mesa" wrote:
> What are you trying to accomplish?
> Why are you using cursors?
> What kind of cursor are you using?
> Any research about a set-based solution?
> Can we see some code?
>
> AMB
> "rajeshlh" wrote:
>|||- Declare the cursor LOCAL FAST_FORWARD.
- In the WHERE clause, change:
GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date
by:
(nr.address_start_date <= GETDATE() and nr.address_end_date >= GETDATE())
- Do you need the ORDER BY clause in the select associated?
- Are the rows, of the result, processed in group or one by one?. For
example, Do you need to process multiple rows
per nr.account_number as a group?
- Can you do it by chuncks?
declare @.min int
declare @.max int
select @.min = min(nr.account_number), @.max = max(nr.account_number)
from ...
where ...
while @.min <= @.max
begin
DECLARE Subscriptions_Cursor CURSOR local fast_forward
for
select ...
from ...
where ...
and nr.account_number between @.min and case when (@.min + 1000) > @.max
then @.max else (@.min + 1000) end
open cursor ...
while 1 = 1
begin
fetch ...
if @.@.error != 0 or @.@.fetch_status != 0 break
..
end
close cursor ...
deallocate cursor ...
set @.min = @.min + 1000
end
...
AMB
"rajeshlh" wrote:
> I have to process a set of records obtained from the join of 5 tables. For
> each record in the resultset, i need to insert/update into 5-6 tables, plu
s i
> need to write to a log table the key for each inserted row or updated row
.
> Following is the code.
> The FETCH FROM Subscription_Cursor fails inside the While Loop after some
> 11,000 records. Since iam writing debug statements to another table, i can
> say that after processing 11000 records inside the cursor, the FETCH
> statement freezes.
> I did think about SET-based approach, but the processing logic forces me t
o
> use a cursor. The Job is run nightly and processes about 100,000+ records.
> Following is the SP Code.
> CREATE PROCEDURE TEST_SP
> AS
> --Variables to store Name record values
> DECLARE @.Account_Number varchar(20),@.Publisher_Code varchar(5),@.Mag_Code
> varchar(5),@.Postal_Code varchar(6),@.Common_Name varchar(30),
> @.Job_Title varchar(50),@.Company_Name varchar(30),@.Address_Line_1
> varchar(30),@.Address_Line_2 varchar(30),@.City varchar(30),
> @.State_Prov varchar(2),@.Country_Code varchar(2),@.Telephone
> varchar(11),@.Fax_Number varchar(11)
> --Variables to store Product record values
> DECLARE @.Service_Status varchar(1),@.Start_Issue datetime,@.Expire_Issue
> datetime,@.Num_Copies integer,@.Email_User_Name varchar(50),
> @.Current_Email_Address varchar(50),@.Email_Password varchar(50)
> --Variables to store Order record values
> DECLARE @.Order_Number varchar(20),@.Order_Status varchar(1),@.Order_Term
> integer,@.Order_Net_Value money,@.Source_Code varchar(2),@.Medium_Code
> varchar(2),
> @.Document_Key varchar(10),@.Setcode varchar(1),@.Orig_Start_Issue
> datetime,@.Order_Entry_Type varchar(5)
> --Variables to store Demographic record values
> DECLARE @.Version_Number varchar(10),@.Segment_Number integer ,@.Demo_Data
> varchar(1024)
> --variales to store newly created reg_visitor_id,account_id and order_id -
> for subscriptions not existing in Elogic Reg DB
> DECLARE @.Reg_Visitor_Id integer ,@.Account_Id integer ,@.Order_Id
> integer,@.Address_Id integer
> --variables to store values present in staging tables - For subscriptions
> already existing in Elogic Reg DB
> DECLARE @.eLogic_Reg_Visitor_Id integer ,@.eLogic_Account_Id integer
> ,@.eLogic_Order_Id integer,@.eLogic_Publication_Id integer
> --variables used for logging
> DECLARE @.Publication_Id integer,@.Pub_Code varchar(5),@.Target_Type
> varchar(100),@.Process_Type varchar(100),@.Summary_Count integer,
> @.Log_Summary_Id integer,@.Source_File_Name varchar(100)
> --Variables to store Donor's data
> DECLARE @.Donor_Company_Name varchar(30),@.Donor_Address_Line_1
> varchar(30),@.Donor_Address_Line_2 varchar(30),@.Donor_City varchar(30),
> @.Donor_State_Prov varchar(2),@.Donor_Country_Code
> varchar(2),@.Donor_Telephone varchar(11),@.Donor_Fax_Number varchar(11),
> @.Donor_Account_number varchar(20), @.Donor_Postal_Code varchar(6)
> --Variables to store CDS to Elogic Converted values
> DECLARE @.eLogic_Account_Status varchar(5),@.eLogic_Pay_Type
> varchar(5),@.eLogic_Auto_Renew bit,@.eLogic_Account_Type varchar(20)
> --Misc variables
> DECLARE @.SP_NAME varchar(50),@.Ret integer
> DECLARE @.Reg_Visitor_Product varchar(100)
> DECLARE @.Exec_Start_Time datetime
> DECLARE @.Promo_Code_Pos2 varchar(1)
> DECLARE @.Delivery_Type varchar(1)
> DECLARE @.Cnt int
>
> --Cursor to subscriptions stored in the staging tables
> DECLARE Subscriptions_Cursor CURSOR FOR
> SELECT nr.eLogic_reg_visitor_id,nr.eLogic_publication_id,nr.source_file_na
me,nr.account_number,nr.publisher_code,nr.mag_code,nr.postal_code,nr.common_
name,nr.job_title,nr.company_name,
> nr.address_line_1,nr.address_line_2,nr.city,nr.state_prov,nr.country_code
,nr.telephone,nr.fax_number,
> pr.eLogic_account_id,pr.service_status,pr.start_issue,pr.expire_issue,pr.
num_copies,pr.email_user_name,pr.email_password,pr.current_email_address,
> ord.eLogic_order_id,ord.order_number,ord.order_status,ord.order_term,ord.
order_net_value,ord.source_code,ord.medium_code,ord.document_key,
> ord.setcode,ord.orig_start_issue,ord.order_entry_type,
> dr.version_number,dr.segment_number,dr.demo_data,
> delivery_type
> FROM cds_name_record nr
> JOIN cds_product_record pr
> ON nr.account_number=pr.account_number
> AND nr.publisher_code=pr.publisher_code
> AND nr.mag_code=pr.mag_code
> JOIN cds_order_record ord
> ON ord.account_number=pr.account_number
> AND ord.publisher_code=pr.publisher_code
> AND ord.mag_code=pr.mag_code
> JOIN cds_demographic_record dr
> ON dr.account_number=pr.account_number
> AND dr.publisher_code=pr.publisher_code
> AND dr.mag_code = pr.mag_code
> JOIN publication_subscription ps
> ON ps.external_multi_mag_code = pr.publisher_code
> AND ps.code = pr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date --
> Get Only current address
> AND (ord.setcode='A' OR ord.setcode='C' OR ord.setcode='E')--Get only
> Non-Gift and Donee Orders
> AND ord.order_status='B' --Get only Base Orders
> AND dr.demo_status = 'B' --Get only Base Demo Records
> --AND nr.account_number <>'0010115103'
> ORDER BY CAST(nr.account_number AS int)
>
> --cursor to the reg_feed_detail_log table , used for populating the
> reg_feed_summary_log table
> DECLARE Summary_Cursor CURSOR FOR
> SELECT
> publication_id,pub_code,target_type,proc
ess_type,source_file_name,count(*)
as
> summary_count
> FROM reg_feed_detail_log
> GROUP BY publication_id,pub_code,target_type,proc
ess_type,source_file_name
> SET @.SP_NAME = OBJECT_NAME(@.@.PROCID)
> SET @.Exec_Start_Time = CURRENT_TIMESTAMP
> OPEN Subscriptions_Cursor
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_D
ata,@.Delivery_Type
>
> SET @.Cnt =1
> PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN --B1
> INSERT INTO Demo_data values ('inside while loop',null,null)
> INSERT INTO debug_table
> (eLogic_reg_visitor_id,eLogic_publicatio
n_id,account_number,publisher_code
,mag_code,order_status,set_code,delivery
_type,seq)
> VALUES (@.eLogic_Reg_Visitor_Id,@.eLogic_
publication_id,@.Account_Number
> ,@.Publisher_Code ,@.Mag_Code,@.Order_Status,@.SetCode,@.Deliv
ery_Type,@.Cnt)
> SELECT @.Reg_Visitor_Id = NULL,@.Account_Id = NULL,@.Order_Id =
> NULL,@.Address_Id = NULL,@.Reg_Visitor_Product = NULL
> SELECT @.Donor_Company_Name = NULL,@.Donor_Address_Line_1 =
> NULL,@.Donor_Address_Line_2 = NULL,@.Donor_City = NULL,
> @.Donor_State_Prov = NULL,@.Donor_Country_Code = NULL,@.Donor_Telephone =
> NULL,@.Donor_Fax_Number = NULL,
> @.Donor_Account_number = NULL
> SELECT @.eLogic_Account_Status = NULL,@.eLogic_Pay_Type =
> NULL,@.eLogic_Auto_Renew = NULL,@.eLogic_Account_Type = NULL
> SELECT @.Promo_Code_Pos2 = NULL
> IF ( @.SetCode = 'C' OR @.SetCode ='E') --SetCode 'C' and 'E' denote donee
> BEGIN --B2
> SELECT @.Donor_Account_number = nr.account_number,@.Donor_Postal_Code =
> nr.postal_code,@.Donor_Company_Name = nr.company_name,
> @.Donor_Address_Line_1 = nr.address_line_1,@.Donor_Address_Line_2 =
> nr.address_line_2,@.Donor_City = nr.city,
> @.Donor_State_Prov = nr.state_prov,@.Donor_Country_Code =
> nr.country_code,@.Donor_Telephone = nr.telephone,@.Donor_Fax_Number =
> nr.fax_number
> FROM cds_name_record nr
> JOIN cds_order_record ord
> ON ord.account_number=nr.account_number
> AND ord.publisher_code=nr.publisher_code
> AND ord.mag_code=nr.mag_code
> WHERE GETDATE() BETWEEN nr.address_start_date AND nr.address_end_date -
-
> Get Only current address Name record
> AND (ord.setcode='B' OR ord.setcode='D')--Get only Donor Orders
> AND (ord.order_status='B' OR ord.order_status='D') --Get only Base Orde
r
> or Non-Subscibing Donor Order
> AND nr.publisher_code = @.Publisher_Code
> AND nr.mag_code = @.Mag_Code
> AND ord.order_number = @.Order_Number
> -- Note : Donor and Donee will have different account numbers, but same
> publisher code, mag code and order number
> END --END B2
>
> SET @.eLogic_Account_Status = CASE
> WHEN @.Service_Status = 'A' THEN 'A'
> WHEN @.Service_Status IN ( 'B','H','I') THEN 'C'
> WHEN @.Service_Status = 'C' THEN 'X'
> WHEN @.Service_Status IN ('D','E','F','G') THEN 'O'
> END
> SET @.eLogic_Pay_Type = CASE
> WHEN @.Order_Entry_Type IN ('A','B','C','D') THEN 'P'
> WHEN @.Order_Entry_Type IN ('L','M','U') THEN 'F'
> ELSE ''
> END
> SET @.eLogic_Auto_Renew= CASE
> WHEN ( SUBSTRING(@.Document_Key,1,1)= '#' OR @.Medium_Code = 'E') THEN
1
> ELSE 0
> END
> SET @.Promo_Code_Pos2 = SUBSTRING(@.Document_Key,2,1)
> SET @.eLogic_Account_Type=CASE
> WHEN @.Source_Code = 'CC' THEN
> CASE
> WHEN @.Promo_Code_Pos2 = 'T' THEN 'FREETRIAL'
> WHEN @.Promo_Code_Pos2 = 'E' THEN 'EMAILONLY'
> ELSE 'CONTROLLED'
> END
> WHEN @.Source_Code = 'CA' THEN 'COMP'
> ELSE 'PAID'
> END
>
> SELECT top 1 @.Reg_Visitor_Id = A.reg_visitor_id
> FROM account A
> JOIN publication_subscription PS
> ON A.pub_code = PS.code
> WHERE A.account_number = @.Account_Number
> AND PS.external_multi_mag_code = @.Publisher_Code
> --If a reg_visitor is not determined in the Staging tables or by looking
up
> the Account table , a new reg_visitor
> --is created
> IF ( @.eLogic_Reg_Visitor_Id IS NULL AND @.Reg_Visitor_Id IS NULL)
> BEGIN --B3
> --No Reg_Visitor corresponding to the mag subscription - mag subscriptio
n
> generated at CDS
> --Create a new reg_visitor and associated rows in Account,Account_Order
> and visitor_demographic
> SELECT @.Reg_Visitor_Product = master_brand
> FROM publication
> WHERE external_multi_mag_code=@.Publisher_Code
> --insert into reg_visior table
> EXEC @.Ret= dbo.upd_reg_visitor @.p_reg_visitor_id = @.Reg_Visitor_Id OUTP
UT,
> @.p_common_name = @.Common_Name,
> @.p_company_name = @.Company_Name,
> @.p_email = @.Current_Email_Address,
> @.p_encrypted_password = @.Email_Password,
> @.p_given_name = NULL,
> @.p_login_id = @.Email_User_Name,
> @.p_merged_visitor_id = NULL,
> @.p_middle_initial = NULL,
> @.p_middle_name = NULL,
> @.p_name_suffix = NULL,
> @.p_password = @.Email_Password,
> @.p_product = @.Reg_Visitor_Product,
> @.p_subproduct = NULL,
> @.p_professional_title = @.Job_Title,
> @.p_record_status = 1,
> @.p_registration_level = 1,
> @.p_salutation = NULL,
> @.p_sur_name = NULL,
> @.p_zip = NULL,
> @.p_country = 'TESTDTS'
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Reg_Visitor table!', 18, 2, @.SP_NAM
E)
> --ROLLBACK TRAN T1
> RETURN -2
> END
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id = SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'reg_visitor',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = NULL,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --insert into address table
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Company_Name,
> @.p_address_line_1 = @.Address_Line_1,
> @.p_address_line_2 = @.Address_Line_2,
> @.p_city = @.City,
> @.p_postal_code = @.Postal_Code,
> @.p_state_prov = @.State_Prov,
> @.p_country_code = @.Country_Code,
> @.p_phone = @.Telephone,
> @.p_fax = @.Fax_Number,
> @.p_address_type = 0 --shipping
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> IF (@.SETCODE = 'C' OR @.SETCODE = 'E') -- Donee subscription
> BEGIN
> --Insert the corresponding Donor Address
> EXEC @.Ret= dbo.set_address @.p_address_id = @.Address_Id OUTPUT,
> @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_company_name = @.Donor_Company_Name,
> @.p_address_line_1 = @.Donor_Address_Line_1,
> @.p_address_line_2 = @.Donor_Address_Line_2,
> @.p_city = @.Donor_City,
> @.p_postal_code = @.Donor_Postal_Code,
> @.p_state_prov = @.Donor_State_Prov,
> @.p_country_code = @.Donor_Country_Code,
> @.p_phone = @.Donor_Telephone,
> @.p_fax = @.Donor_Fax_Number,
> @.p_address_type = 1 --Billing
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into address for donor')
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'address',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Address_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> --IF @.Account_Number = '0010115103' OR @.Cnt =11214
> --INSERT INTO demo_data values ('inserted into detail log for donor
> address')
> END
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting into Address table!', 18, 3, @.sp_name)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -3
> END
> --Insert into Account table
> EXEC @.Ret = dbo.set_account @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id OUTPUT,
> @.p_account_number = @.Account_Number,
> @.p_pub_code = @.Mag_Code,
> @.p_account_type = @.eLogic_Account_type ,
> @.p_status = @.eLogic_Account_Status,
> @.p_supp_account_number = @.Donor_Account_Number -- If its a Non-Gift
> Order, NULL will be inserted for Supp_Account_Number
>
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Account_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account table!', 18, 4, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -4
> END
> INSERT INTO Demo_data values ('completed inserting into account
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = NULL,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed inserting into detail log for
> account table',@.Reg_Visitor_id,@.Account_Number)
> --insert into account_order table
> EXEC @.Ret = dbo.set_account_order @.p_reg_visitor_id = @.Reg_Visitor_Id,
> @.p_account_id = @.Account_Id,
> @.p_order_id = @.Order_Id OUTPUT,
> @.p_term = @.Order_Term,
> @.p_term_unit = NULL,
> @.p_pay_type = @.eLogic_Pay_Type,
> @.p_net_amt = @.Order_Net_Value,
> @.p_quantity = @.Num_Copies,
> @.p_vendor_order_number = @.Order_Number,
> @.p_promo_response_key = @.Document_Key,
> @.p_number_of_installments=1,
> @.p_auto_renew =@.eLogic_Auto_Renew
> IF ( @.@.error <> 0 OR @.Ret < 0 OR @.Order_Id IS NULL)
> BEGIN
> RAISERROR('%s: Error inserting into Account_Order table!', 18, 5, @.SP_N
AME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -5
> END
> INSERT INTO Demo_data values ('completed inserting into account_order
> table',@.Reg_Visitor_id,@.Account_Number)
> --Log details to reg_feed_detail_log
> EXEC Log_Reg_Feed_Details @.P_Publication_Id = @.eLogic_Publication_Id,
> @.P_Pub_Code = @.Mag_Code,
> --@.P_Process_Cycle_Id =SELECT DATEPART(dy, GETDATE()) ,
> @.P_Source_Key_1 = NULL,
> @.P_Source_Key_2 = NULL,
> @.P_Source_Key_3 = NULL,
> @.P_Target_Type = 'account_order',
> @.P_Target_Key_1 = @.Reg_Visitor_Id,
> @.P_Target_Key_2 = @.Account_Id ,
> @.P_Target_Key_3 = @.Order_Id,
> @.P_Process_Type = 'INSERT',
> @.P_Source_File_Name = @.Source_File_Name
> INSERT INTO Demo_data values ('completed logging to detail log table for
> account_order',@.Reg_Visitor_id,@.Account_
Number)
> --insert into visitor_demographics table only for Online Magazines ;
> Delivery Type W - Online, P- Print
> IF(@.Delivery_Type = 'W')
> BEGIN
> INSERT INTO Demo_data values ('calling demographcis
> sp',@.Reg_Visitor_id,@.Account_Number)
> EXEC @.Ret = Process_CDS_Demographics @.Reg_Visitor_Id = @.Reg_Visitor_Id
,
> @.Mag_Code = @.Mag_Code,
> @.Demo_Data = @.Demo_Data,
> @.Segment_Number = @.Segment_Number,
> @.Version_Number = @.Version_Number
> IF ( @.@.error <> 0 OR @.Ret < 0 )
> BEGIN
> RAISERROR('%s: Error inserting/updating into Visitor_Demographics
> table!', 18, 6, @.SP_NAME)
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
> RETURN -6
> END
> END
> INSERT INTO Demo_data values ('DEBUG LINE
> HIT',@.Reg_Visitor_id,@.Account_Number)
>
> END --END B3
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('About to fetch next row- current row details
> -->',@.Reg_visitor_id,@.Account_number)
> FETCH NEXT FROM Subscriptions_Cursor
> INTO
> @.eLogic_Reg_Visitor_Id,@.eLogic_Publicat
ion_Id,@.Source_File_Name,@.Account_
Number ,@.Publisher_Code ,@.Mag_Code ,@.Postal_Code ,@.Common_Name ,@.Job_Title ,
> @.Company_Name ,@.Address_Line_1,@.Address_Line_2 ,@.City ,
> @.State_Prov ,@.Country_Code ,@.Telephone ,@.Fax_Number,
> @.eLogic_Account_Id,@.Service_Status ,@.Start_Issue ,@.Expire_Issue
> ,@.Num_Copies ,@.Email_User_Name ,@.Email_Password,
> @.Current_Email_Address,
> @.eLogic_Order_Id,@.Order_Number, @.Order_Status ,@.Order_Term
> ,@.Order_Net_Value ,@.Source_Code,@.Medium_Code ,
> @.Document_Key ,@.Setcode ,@.Orig_Start_Issue, @.Order_Entry_Type,
> @.Version_Number,@.Segment_Number,@.Demo_
Data,@.Delivery_Type
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('fetched next row',@.eLogic_Reg_Visitor_Id,@.Account_num
ber)
> SET @.Cnt = @.Cnt + 1
> INSERT INTO demo_data (debug_message,reg_visitor_id,account_nu
mber)VALUES
> ('value of @.@.FETCHSTATUS =',@.@.Fetch_Status,@.Account_number)
> END --END B1
> CLOSE Subscriptions_Cursor
> DEALLOCATE Subscriptions_Cursor
>
> "Alejandro Mesa" wrote:
>

CURSOR AS PROCEDURE OUTPUT

I've written this GenericCursor procedure that return a CURSOR as output parameter, where the CURSOR is opened by a dynamic SQL statement executed via sp_executesql:

CREATE PROCEDURE dbo.GenericCursor
@.genericCursor CURSOR VARYING OUTPUT
, @.CMD Nvarchar(1024)
AS
BEGIN
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMD,
N'@.utentiCursor CURSOR out',
@.genericCursor out
END

Everityng works fine: I want to use GenericCursor procedure and it works, but when I try to CLOSE and then DEALLOCATE the CURSOR it raises an error (as in the example):

CREATE PROCEDURE testGenericCursor
AS
BEGIN
DECLARE @.MyCursor CURSOR;
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SELECT idUtente FROM Utenti'
EXEC dbo.UtentiCursor @.MyCursor OUTPUT, @.CMDx;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @.MyCursor;
END;
-- CLOSE @.MyCursor;
-- DEALLOCATE @.MyCursor;
END


Why CLOSE and DEALLOCATEare not usable? They generate the following errors:

Msg 16950, Level 16, State 2, Line 13
The variable '@.MyCursor' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Line 14
The variable '@.MyCursor' does not currently have a cursor allocated to it.

I don't think you ever put the cursor into your output parameter in the first place (unless you just made a copy\paste error when putting your code into the post).

The problem is here:

DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;' <<<< This won't work (see below)
exec sp_executesql @.CMD, <<<< Shouldn't this be @.CMDx?

Your going to have to pass @.genericCursor into sp_executesql as an output parameter as well, something like this:

DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMDx, N'@.genericCursor cursor varying output', @.genericCursor output

|||

The CLOSE and DEALLOCATE are in a different scope than the cursor...they don't know each other exist.

Your code revised:

CREATE PROCEDURE dbo.GenericCursor

@.genericCursor CURSOR VARYING OUTPUT

, @.CMD Nvarchar(1024)

AS

BEGIN

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'

exec sp_executesql @.CMD,

N'@.utentiCursor CURSOR out',

@.genericCursor out

CLOSE @.genericCursor

DEALLOCATE @.genericCursor

END

CREATE PROCEDURE testGenericCursor

AS

BEGIN

DECLARE @.MyCursor CURSOR;

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SELECT name FROM dbo.sysobjects;'

EXEC dbo.GenericCursor @.MyCursor OUTPUT, @.CMDx;

WHILE (@.@.FETCH_STATUS = 0)

BEGIN;

FETCH NEXT FROM @.MyCursor;

END

END

EXECdbo.testGenericCursor

This is an example from the EXECUTE (Transact-SQL) topic in Books Online that uses EXEC:

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

USE AdventureWorks;

GO

DECLARE tables_cursor CURSOR

FOR

SELECT s.name, t.name

FROM sys.objects AS t

JOIN sys.schemas AS s ON s.schema_id = t.schema_id

WHERE t.type = 'U';

OPEN tables_cursor;

DECLARE @.schemaname sysname;

DECLARE @.tablename sysname;

FETCH NEXT FROM tables_cursor INTO @.schemaname, @.tablename;

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN;

EXEC ('ALTER INDEX ALL ON ' + @.schemaname + '.' + @.tablename + ' REBUILD;');

FETCH NEXT FROM tables_cursor INTO @.schemaname, @.tablename;

END;

PRINT 'The indexes on all tables have been rebuilt.';

CLOSE tables_cursor;

DEALLOCATE tables_cursor;

GO

A good link for dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html#cursor0

|||He is using cursor variables, which can be passed between scopes just like he is doing in dbo.GenericCursor. He just has to modify his sp_executesql call to pass the cursor variable as an output parameter.|||

Thanks to evryone out there: I noticed I've posted the wrong code :-P

> SO THIS MEANS YOU ARE SO GOOD THAT YOU'VE UNDERSTOOD IT EVEN IF IT WAS WRONG!!!

Just to summarize what the matter is, this is the final working version:

CREATE PROCEDURE dbo.GenericCursor

@.genericCursor CURSOR VARYING OUTPUT

, @.CMD Nvarchar(1024)

AS

BEGIN

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'

exec sp_executesql @.CMDx,

N'@.genericCursor cursor output',

@.genericCursor out

END

--

CREATE PROCEDURE testGenericCursor

AS

BEGIN

DECLARE @.MyCursor CURSOR;

DECLARE @.name as varchar(100);

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SELECT TOP 50 name FROM dbo.sysobjects;'

EXEC dbo.GenericCursor @.MyCursor OUTPUT, @.CMDx;

FETCH NEXT FROM @.MyCursor INTO @.name;

WHILE (@.@.FETCH_STATUS = 0)

BEGIN;

FETCH NEXT FROM @.MyCursor INTO @.name;

SELECT @.name

END

CLOSE @.MyCursor

DEALLOCATE @.MyCursor

END

--

EXEC dbo.testGenericCursor

cursor and fetch statement

can you please give me an example of using the FETCH and cursor statement

And make to be able to fetch more that one row at a time,

lets say the two database are TEST(fetch to) and TLC (fetch from) and both have Table called Mine with same fieldsSally,

I have moved your post into the forum from the MSSQL articles

MODERATOR|||

Quote:

Originally Posted by Sally1053

can you please give me an example of using the FETCH and cursor statement

And make to be able to fetch more that one row at a time,

lets say the two database are TEST(fetch to) and TLC (fetch from) and both have Table called Mine with same fields

would you mind giving a more detailed specs? maybe FETCH is not the right solutionsql

Current Row Number

Hi,
Is there a way to get the current Row number.
If get this as a result of select statement,
12 as
56 yu
89 gh
I need like this
1 12 as
2 56 yu
3 89 gh
I saw some eg.s using temporary tables. But Is there a straight forward way
to get this.
Thanks
KiranYou can use Group By, Order By clauses to get the row number. or you can
also make sure of subquery to get the row number..check examples here
http://www.aspfaq.com/show.asp?id=2427
instead of temp tables, you can make use of table variables in the example
provided in the above link.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Kiran" <Kiran@.nospam.net> wrote in message
news:#0zmneS$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight forward
way
> to get this.
> Thanks
> Kiran
>|||Kiran wrote:
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight
> forward way to get this.
> Thanks
> Kiran
This uses a temp table as well, but maybe it's something different than
what you saw. Can you tell us how you are going to to know the order of
the rows during the select? Do you have an ORDER BY for the statement
that is driving the final order of the rows?
create table #OrderTest(col1 int not null, col2 varchar(3) not null)
insert into #OrderTest (col1, col2) values (10, 'AA')
insert into #OrderTest (col1, col2) values (12, 'AA')
insert into #OrderTest (col1, col2) values (13, 'AA')
insert into #OrderTest (col1, col2) values (16, 'AA')
Select IDENTITY(int, 1, 1) as "OrderCol", col1, col2
INTO #OrderTest2
From #OrderTest
Select * from #OrderTest2
Drop Table #OrderTest
Drop Table #OrderTest2
David Gugick
Imceda Software
www.imceda.com|||Hi Kiran,
Wait for SQL 2005
Regards,
Daniel
"Kiran" <Kiran@.nospam.net> wrote in message
news:#0zmneS$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight forward
way
> to get this.
> Thanks
> Kiran
>

Current Row Number

Hi,
Is there a way to get the current Row number.
If get this as a result of select statement,
12 as
56 yu
89 gh
I need like this
1 12 as
2 56 yu
3 89 gh
I saw some eg.s using temporary tables. But Is there a straight forward way
to get this.
Thanks
KiranYou can use Group By, Order By clauses to get the row number. or you can
also make sure of subquery to get the row number..check examples here
http://www.aspfaq.com/show.asp?id=2427
instead of temp tables, you can make use of table variables in the example
provided in the above link.
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Kiran" <Kiran@.nospam.net> wrote in message
news:#0zmneS$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight forward
way
> to get this.
> Thanks
> Kiran
>|||Kiran wrote:
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight
> forward way to get this.
> Thanks
> Kiran
This uses a temp table as well, but maybe it's something different than
what you saw. Can you tell us how you are going to to know the order of
the rows during the select? Do you have an ORDER BY for the statement
that is driving the final order of the rows?
create table #OrderTest(col1 int not null, col2 varchar(3) not null)
insert into #OrderTest (col1, col2) values (10, 'AA')
insert into #OrderTest (col1, col2) values (12, 'AA')
insert into #OrderTest (col1, col2) values (13, 'AA')
insert into #OrderTest (col1, col2) values (16, 'AA')
Select IDENTITY(int, 1, 1) as "OrderCol", col1, col2
INTO #OrderTest2
From #OrderTest
Select * from #OrderTest2
Drop Table #OrderTest
Drop Table #OrderTest2
David Gugick
Imceda Software
www.imceda.com|||Hi Kiran,
Wait for SQL 2005
Regards,
Daniel
"Kiran" <Kiran@.nospam.net> wrote in message
news:#0zmneS$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight forward
way
> to get this.
> Thanks
> Kiran
>sql

Current Row Number

Hi,
Is there a way to get the current Row number.
If get this as a result of select statement,
12 as
56 yu
89 gh
I need like this
1 12 as
2 56 yu
3 89 gh
I saw some eg.s using temporary tables. But Is there a straight forward way
to get this.
Thanks
Kiran
You can use Group By, Order By clauses to get the row number. or you can
also make sure of subquery to get the row number..check examples here
http://www.aspfaq.com/show.asp?id=2427
instead of temp tables, you can make use of table variables in the example
provided in the above link.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Kiran" <Kiran@.nospam.net> wrote in message
news:#0zmneS$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight forward
way
> to get this.
> Thanks
> Kiran
>
|||Kiran wrote:
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight
> forward way to get this.
> Thanks
> Kiran
This uses a temp table as well, but maybe it's something different than
what you saw. Can you tell us how you are going to to know the order of
the rows during the select? Do you have an ORDER BY for the statement
that is driving the final order of the rows?
create table #OrderTest(col1 int not null, col2 varchar(3) not null)
insert into #OrderTest (col1, col2) values (10, 'AA')
insert into #OrderTest (col1, col2) values (12, 'AA')
insert into #OrderTest (col1, col2) values (13, 'AA')
insert into #OrderTest (col1, col2) values (16, 'AA')
Select IDENTITY(int, 1, 1) as "OrderCol", col1, col2
INTO #OrderTest2
From #OrderTest
Select * from #OrderTest2
Drop Table #OrderTest
Drop Table #OrderTest2
David Gugick
Imceda Software
www.imceda.com
|||Hi Kiran,
Wait for SQL 2005
Regards,
Daniel
"Kiran" <Kiran@.nospam.net> wrote in message
news:#0zmneS$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Is there a way to get the current Row number.
> If get this as a result of select statement,
> 12 as
> 56 yu
> 89 gh
> I need like this
> 1 12 as
> 2 56 yu
> 3 89 gh
> I saw some eg.s using temporary tables. But Is there a straight forward
way
> to get this.
> Thanks
> Kiran
>

Current Date in Transact SQL

Does anyone know if there's a Transact SQL statement to retrieve the current date? I've looked in the msdn T-SQL syntax pages, but I can't find it.

Thanks.getdate()

CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expre

I am having this error when using execute query for CTE

Help will be appriciated

Would be interesting to have the code you tried to execute, because this i ibviously a syntax error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||? As Jens noted, you haven't showed us any code... But I'm betting you just need to use a semicolon before the "WITH": ;WITH myCTE AS ... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <dba_sql@.discussions.microsoft.com> wrote in message news:35bbde4b-222b-4311-8087-09d80efaa94b@.discussions.microsoft.com... I am having this error when using execute query for CTE Help will be appriciated|||

I am getting this error when running the following code:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int)

RETURNS @.ClassificationsInTree TABLE (ClassificationId int)

AS

BEGIN

WITH CLINTREE(ClassificationId) AS (

SELECT TopClassificationId FROM ClassificationTree

WHERE ClassificationTreeId = 81203717

UNION ALL

SELECT ClassificationId FROM Classification

INNER JOIN CLINTREE ON

CLINTREE.ClassificationId = Classification.ParentClassificationTree

WHERE Classification.ClassificationId <> CLINTREE.ClassificationId

)

--INSERT @.ClassificationsInTree

SELECT ClassificationId FROM CLINTREE

OPTION (MAXRECURSION 10);

RETURN

END

GO

The error messages:

Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7

Incorrect syntax near the keyword 'WITH'.

Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18

Line 18: Incorrect syntax near 'MAXRECURSION'.

Any thoughts? This is the exact syntax found in the help files, no?

|||? I was able to run that batch on my end with no errors once I uncommented the insert line... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:0b38d9cc-f171-4ae6-8377-fc50393d8045@.discussions.microsoft.com... I am getting this error when running the following code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int) RETURNS @.ClassificationsInTree TABLE (ClassificationId int) AS BEGIN WITH CLINTREE(ClassificationId) AS ( SELECT TopClassificationId FROM ClassificationTree WHERE ClassificationTreeId = 81203717 UNION ALL SELECT ClassificationId FROM Classification INNER JOIN CLINTREE ON CLINTREE.ClassificationId = Classification.ParentClassificationTree WHERE Classification.ClassificationId <> CLINTREE.ClassificationId ) --INSERT @.ClassificationsInTree SELECT ClassificationId FROM CLINTREE OPTION (MAXRECURSION 10); RETURN END GO The error messages: Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7 Incorrect syntax near the keyword 'WITH'. Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18 Line 18: Incorrect syntax near 'MAXRECURSION'. Any thoughts? This is the exact syntax found in the help files, no?|||

Thanks for the reply.

I get those same two errors whether that line is commented or not...

Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.

|||? None that I know of. The only one I could think of was compatability level -- but I just tested with a database set to compatability level 80 (SQL Server 2000) and was still not able to replicate the error. Regardless, you should probably make sure yours is set correctly. Right-click on your database in SSMS, click Properties, then Options. Make sure Compatability Level is set to SQL Server 2005... Aside from that, though, I'm not sure what's going on. Can you use CTEs at all (outside of UDFs?) And did you try adding a semicolon before the WITH, as I suggested before? It appears to be unnecessary on my end, but it's always a good idea anyway... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:1cbe0923-6981-44eb-b4cd-8bdee48ad315@.discussions.microsoft.com... Thanks for the reply. I get those same two errors whether that line is commented or not... Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.|||

So your suggestion to check the compatability level led me to the answer. The database server I was trying to run the query against is a SQL2000 server. We have many instances of SQL Server running for development purposes, and I didn't realize I was working against a SQL2000 instance. That database has the data I need in it, so I will have to move it to another machine.

A silly mistake, but I wouldn't have realized it, Thanks for your help!

|||I got the same error in Crystal Reports XI. I used Toad to write the SQL, then copied it into the command editor in Crystal. It worked fine, until I opened the same saved report on CRXI from a Citrix client. Still works fine using Toad, same datasource, credentials, etc. After reading this thread, I tried putting the semicolon in front of WITH and it worked. Strange to me, but it works,