variable assignment in cursor declaration

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

General SQL Server Forums

New to SQL Server Programming

variable assignment in cursor declaration

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-05 : 09:29:45

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

22864 Posts

Posted - 2006-07-05 : 09:33:26

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 09:38:40

[code]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[/code]
Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-05 : 10:29:57

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.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-05 : 10:46:39

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"

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-05 : 11:59:30

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

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-07-08 : 05:25:48

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-10 : 01:52:19

Did you try the methods suggested?

Madhivanan

Failing to plan is Planning to fail

mikadad
Starting Member

1 Post

Posted - 2006-07-28 : 12:04:08

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources