How to change avoid using cursor in my script

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

Transact-SQL (2005)

How to change avoid using cursor in my script

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-09 : 23:13:59

Hello All,

I have a script that is using cursor and I want to know how I can aviod using cursor for the provided script. Please advice. Thanks.

Cursor Script:

Declare cur_audit cursor for
select m_id, m_requery, b_status
from SData.dbo.measure_audit
where b_type = @B_Type

open cur_audit
fetch next from cur_audit into @m_id, @m_query, @b_status

While @@Fetch_Status = 0
BEGIN

Set @Select_SQL =
' Select batchid, getdate(), ' + Cast(@m_id as varchar(20)) + ',(' + @M_Query + ')' +
' From sdata.dbo.batch' +
' where a_ind = 0 and b_type = ' + '''' + @B_Type+ '''' +
' and (b_status = ' + '''' + @B_Status + '''' +
' or ' + '''' + @B_Status + '''' + '= ' + '''ALL''' + ')'

Insert into SData.dbo.audit (B_id, a_date, m_id, m_value)
Exec (@Select_SQL)
Print @Select_SQL

fetch next from cur_audit into @m_id, @m_query, @b_status
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-09 : 23:26:02

[code]
Insert into SData.dbo.audit (B_id, a_date, m_id, m_value)
select batchid, getdate(), m_id, m_requery
from SData.dbo.measure_audit
where b_type = @B_Type
and a_ind = 0
[/code]

EDIT : wrong solution. Use Peter's query

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-09 : 23:34:39

do you have NULL value in b_status ? Do you want to exclude records where b_status is null into the audit table ? cause your cursor script will exclude records where b_status is null.

KH
[spoiler]Time is always against us[/spoiler]

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-10 : 06:53:51

thanks for the quick response khtan. Yes, I want to exclude records where b_status is null.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 07:00:35

khtan, you INSERT from the measure_audit table?
OP wanted to insert from sdata.dbo.batch table...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 07:08:44

Here is a SET-BASED solution for what you want.

INSERT		sData.dbo.Audit
		(
			b_ID,
			a_Date,
			m_ID,
			m_Value
		)
SELECT		b.BatchID,
		GETDATE(),
		ma.m_ID,
		'(' + ma.m_Requery + ')'
FROM		sData.dbo.Batch AS b
INNER JOIN	sData.dbo.Measure_Audit AS ma ON ma.b_Type = b.b_Type
WHERE		b.a_Ind = 0
		AND ma.b_Status IN (b.b_Status, 'ALL')

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-10 : 09:45:33

quote:
Originally posted by Peso

khtan, you INSERT from the measure_audit table?
OP wanted to insert from sdata.dbo.batch table...

E 12°55'05.63"
N 56°04'39.26"

Oops.

KH
[spoiler]Time is always against us[/spoiler]

pras2007
Posting Yak Master

216 Posts

Posted - 2009-06-12 : 10:12:59

Thanks for the response Peso,
The script works without using cursor but the individual SQL statement with a field is not be executed. The m_Value suppose to show a number instead of the actual SQL Statement, I think the “Exec (@Select_SQL)” suppose to be in the script somewhere. Does anybody know how to resolve this problem? Please advice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 02:26:24

quote:
Originally posted by pras2007

Thanks for the response Peso,
The script works without using cursor but the individual SQL statement with a field is not be executed. The m_Value suppose to show a number instead of the actual SQL Statement, I think the “Exec (@Select_SQL)” suppose to be in the script somewhere. Does anybody know how to resolve this problem? Please advice

so you want the query in ma.m_Requery to be executed and result stored in m_Value? in that case what you need is to loop over records in query

SELECT b.BatchID,
GETDATE(),
ma.m_ID,
'(' + ma.m_Requery + ')'
FROM sData.dbo.Batch AS b
INNER JOIN sData.dbo.Measure_Audit AS ma ON ma.b_Type = b.b_Type
WHERE b.a_Ind = 0
AND ma.b_Status IN (b.b_Status, 'ALL')

store the ma.m_Requery value in a variable and use EXEC(@Variable) to get result and store it in table. I'm not sure whether this will perform better than your cursor based solution. why is your system designed this way? i.e storing queries in column and then execute each and store result in other? what will be typical queries stored in this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources