Using Cursor in RDL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Using Cursor in RDL

Author

Topic

shahidhameedwah
Starting Member

5 Posts

Posted - 2008-07-10 : 00:14:48

Hi to all

I have wrote query which is creating Cursors and temp tables and in the end query return temp table
i added that query to varchar variable and execute that query using EXEC
its working fine in SQL Query Analyzer but wen i added this Query to RDL report Dataset it gaves me Error that
"The Declare cursor SQL construct or statement is not supported."
Please help me i have to add this report in MSCRM where i am not allowed to add SP in MSCRM Database, so i have to right all report logic behind RDL report.
And also RDL layout not showing me temp tables

Thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 01:19:36

what's your reuirement. Could you post it so that we could try implementing it without using cursor.

shahidhameedwah
Starting Member

5 Posts

Posted - 2008-07-10 : 01:41:26

DECLARE @i_stdid
DECLARE @i_stdnum
DECLARE pcur2 CURSOR
FOR SELECT std_id
FROM #tmp_std
open pcur2
Fetch Next FROM pcur2 into @i_id
While @@fetch_status = 0
Begin
SELECT @i_stdid = null, @i_stdnum = null
SELECT TOP (1) @i_stdid = po_std_id, @i_stdnum = po_stdnum
FROM po_STUDENT
WHERE (po_std_id = @i_id)

--UPDATE the temp table
UPDATE #tmp_marks
set po_marks_stdid = @i_stdid, po_marks_stdnum = @i_stdnum
WHERE current of pcur2

--get the next record FROM the temp table
Fetch Next FROM pcur2 into @i_inscid, @s_no
end --loop

Close pcur2
Deallocate pcur2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 01:56:38

You dont need a cursor for this. you can simply do it with an update statement

UPDATE m
SET m.po_marks_stdid = b.po_std_id, 
m.po_marks_stdnum = b.po_stdnum 
FROM #tmp_marks m
CROSS APPLY (SELECT TOP 1 po_std_id,po_stdnum 
             FROM po_STUDENT s
             INNER JOIN #tmp_std st
             ON st.std_id =s.po_std_id
             WHERE s.linkingcol=m.linkingcol             ORDER BY your ordering field) b

the above code will do what you want. i dont know how temporary table is related to PO_Student. so please replace blue code with appropriate fields from your tables and also give a fields on which you need result to be ordered and take first one.

shahidhameedwah
Starting Member

5 Posts

Posted - 2008-07-10 : 02:08:34

is this query update table #tmp_marks for all records in #tmp_std

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 02:24:40

quote:
Originally posted by shahidhameedwah

is this query update table #tmp_marks for all records in #tmp_std

yup. it will update all records in #tmp_marks with values taken from PO_STUDENT and #tmp_std based on relation between them and tmp_marks.

shahidhameedwah
Starting Member

5 Posts

Posted - 2008-07-10 : 02:33:10

Thanks a lot
One more issue
how can i add temp tables columns in RDL report design
Like above Query in end of query a SELECT statement is their returning all data of temp table #tmp_marks but its not visible in data panel of RDL report, So how can i be able to add this temp table columns to RDL report

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 04:31:12

quote:
Originally posted by shahidhameedwah

Thanks a lot
One more issue
how can i add temp tables columns in RDL report design
Like above Query in end of query a SELECT statement is their returning all data of temp table #tmp_marks but its not visible in data panel of RDL report, So how can i be able to add this temp table columns to RDL report

Thanks again

Why is data not visible? what happens when you execute the query in report data tab?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources