SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 variable assignment in cursor declaration
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 07/05/2006 :  09:29:45  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 07/05/2006 :  09:33:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Dont use Cusrsor. Use while loop.
Refer this for example
http://sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/05/2006 :  09:38:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
declare @columns table (id int identity(0, 1), tablename sysname, columnname sysname)

insert		@columns
		(
			tablename,
			columnname
		)
select		isc.table_name,
		isc.column_name
from		information_schema.columns isc
inner join	information_schema.tables ist on ist.table_name = isc.table_name
where		isc.data_type in ('datetime', 'smalldatetime')
		and ist.table_type = 'base table'

declare	@id int,
	@sql varchar(2000),
	@tablename sysname,
	@columnname sysname

select	@id = max(id)
from	@columns

while @id >= 0
	begin
		select	@tablename = quotename(tablename),
			@columnname = quotename(columnname)
		from	@columns
		where	id = @id

		SELECT @SQL = 'select DISTINCT(' + @columnname + ') from ' + @tablename + ' order by ' + @columnname + ' ASC',
			@id = @id - 1

		exec	(@sql)
	end

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 07/05/2006 09:39:44
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 07/05/2006 :  10:29:57  Show Profile  Reply with Quote
quote:
way to access all the dbs and their tables inside
To access all the dbs you can use sp_MSforeachdb. sp_MSforeachdb and sp_MSForEachTable (which Madhivanan posted about) are explained here:

http://www.databasejournal.com/features/mssql/article.php/3441031

You should note the warning towards the end of that article if you use these sprocs though.


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 07/05/2006 :  10:46:39  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Why don't you put cursor declaration also as a dynamic SQL:

select @SQL = 'DECLARE find_dates CURSOR FOR select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC'

Open find_dates
...more code

Since cursor is by default global, you can access it outside dynamic sql also.


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/05/2006 :  11:59:30  Show Profile  Reply with Quote
quote:
Originally posted by raysefo

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



This just seems like an incredibly bad idea.....what's it for?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 07/08/2006 :  05:25:48  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi,

How can i put cursor declaration as dynamic SQL? or let me know how can i get values of this statement below;

'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC' one by one without cursor in a while loop?
I have 24 DBs and each of them have 7 tables inside.All of the tables approx. have 5000000 rows of datas inside. Thats why, i need to use DYNAMIC SQL because i dont wanna do this operation one by one for all tables in 24 DBs. I wanna run the SP and do my other stuff, thats why i m trying to do it.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 07/10/2006 :  01:52:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Did you try the methods suggested?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mikadad
Starting Member

USA
1 Posts

Posted - 07/28/2006 :  12:04:08  Show Profile  Send mikadad an AOL message  Send mikadad an ICQ Message  Send mikadad a Yahoo! Message  Reply with Quote
I too am trying to correct this issue in my code, any help would be appreciated. I am using this to shoot out an email to the folks who meet the criteria in the SELECT statement. Thanks My code is below:

CREATE PROCEDURE usp_email_softeng

AS

DECLARE @TITLE VARCHAR(500)
DECLARE @DESCRIPTION VARCHAR(500)
DECLARE @REQUIREMENTS VARCHAR(500)
DECLARE @MyRecipients nvarchar (255)
DECLARE @NEWMessage nvarchar (255)
DECLARE @NEWSubject nvarchar (255)
DECLARE @RECORD_NUM NCHAR(2)
DECLARE myCursor Cursor For

select @record_num=[id],
@title=[title],
@description=[description],
@REQUIREMENTS=[REQUIREMENTS],
[loginid]
from users c, display a,(select max(id) mxid from display) b where a.id=b.mxid AND SOFTWARE ='YES' AND TASK ='SOFTWARE'


Open MyCursor

Fetch Next From MyCursor Into @MyRecipients

While @@Fetch_Status = 0

BEGIN

Print @MyRecipients


/*select @record_num=[id],
@title=[title],
@description=[description],
@REQUIREMENTS=[REQUIREMENTS]
from display a,(select max(id) mxid from display) b where a.id=b.mxid AND SOFTWARE ='YES'
*/

SET @NEWSUBJECT='YOU HAVE AN SOFTWARE ENGINEERING REQUEST TO APPROVE!!! THIS IS A TEST EMAIL FOR THE WAP TOOL, JUST DISREGARD!!!'
SET @NEWMESSAGE='TITLE: '+@TITLE + " " + +CHAR(13)+ +CHAR(13)+ 'DESCRIPTION: '+@DESCRIPTION + " " + +CHAR(13)++CHAR(13)+ 'REQUIREMENTS: '+@REQUIREMENTS + " "++CHAR(13)++CHAR(13)+ + 'Click on this link to view your request. http://localhost/WAP/db/ba2btpeb2.asp?a=SOFTWAREEDITRECORD&ID='+@RECORD_NUM


Exec Master.dbo.xp_sendmail
@MyRecipients,
@SUBJECT =@NEWSUBJECT,
@MESSAGE=@NEWMESSAGE

Fetch Next From MyCursor Into @MyRecipients

End

Close MyCursor

Deallocate MyCursor
GO
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000