tsql with out using cursors

Question

tsql with out using cursors

Damian-1673

SSCommitted

Points: 1622
More actions
November 21, 2014 at 7:31 am

#301319

Hi
Please.. can anyone give me an alternative code for the below tsql with out using cursors
declare @dbname as varchar(100)
Declare cr cursor for
select [name] from [master].sys.sysdatabases where [name] like ('%tst%')
Open cr
Fetch cr into @dbname
WHILE @@FETCH_STATUS = 0
begin
-- my query
FETCH cr into @dbname
end
CLOSE cr
DEALLOCATE cr

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

It might help if you would explain what you are actually trying to do.

Some queries can be written to function without cursors, others can't (such as for example executing a stored procedure on every database).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Damian-1673 SSCommitted Points: 1622 More actions · Answer 2

this query will just go into a stored procedure only, no functions or any other method.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Damian-167372 (11/21/2014)
this query will just go into a stored procedure only, no functions or any other method.

Here is the challenge. You obfuscated the real meat of what is going on. You said, here is a basic cursor structure how can I do it differently? We need to see the code you are currently doing in a cursor. Otherwise about all we can offer is to say "change the logic so it is set based".

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Damian-1673 SSCommitted Points: 1622 More actions · Answer 4

Damian-1673

SSCommitted

Points: 1622

November 21, 2014 at 8:01 am

#1760151

is there a way we can do this in WHILE Loop?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Damian-167372 (11/21/2014)
is there a way we can do this in WHILE Loop?

Sure...you could replace the cursor with a while loop. However, all you are going to do is make it even slower.

What are you doing inside the loop? That is what needs to be rewritten as a set based approach.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Damian-167372 (11/21/2014)
is there a way we can do this in WHILE Loop?

What do you think this is? A pizza?

WHILE @@FETCH_STATUS = 0

begin

-- my query

FETCH cr into @dbname

end

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Damian-1673 SSCommitted Points: 1622 More actions · Answer 7

ok this is what i do

DECLARE @server_namevarchar(200)

Declare cr cursor for

SELECT server_name FROM dbo.Tbl_ServerList

Open cr

Fetch cr into @server_name

WHILE @@FETCH_STATUS = 0

begin

SET @cmd = 'select * INTO ##myTable FROM OPENROWSET(''SQLNCLI'',''Server='+@server_name+';Trusted_Connection=yes;'',''exec sp_who'')'

exec(@cmd)

INSERT INTO Capture_Logins

SELECT Loginame,HostName,DbName,cmd

FROM ##myTable

DROP TABLE ##myTable

Fetch cr into @server_name

end

CLOSE cr

DEALLOCATE cr

so now tell me how this can be changed

Damian-1673 SSCommitted Points: 1622 More actions · Answer 8

well you got my answere hope its clear now

Luis Cazares (11/21/2014)
Damian-167372 (11/21/2014)
is there a way we can do this in WHILE Loop?
What do you think this is? A pizza?
WHILE @@FETCH_STATUS = 0
begin
-- my query
FETCH cr into @dbname
end

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Damian-167372 (11/21/2014)
ok this is what i do
DECLARE @server_namevarchar(200)
Declare cr cursor for
SELECT server_name FROM dbo.Tbl_ServerList
Open cr
Fetch cr into @server_name
WHILE @@FETCH_STATUS = 0
begin
SET @cmd = 'select * INTO ##myTable FROM OPENROWSET(''SQLNCLI'',''Server='+@server_name+';Trusted_Connection=yes;'',''exec sp_who'')'
exec(@cmd)
INSERT INTO Capture_Logins
SELECT Loginame,HostName,DbName,cmd
FROM ##myTable
DROP TABLE ##myTable
Fetch cr into @server_name
end
CLOSE cr
DEALLOCATE cr
so now tell me how this can be changed

Did you read the very first post from Koen? This is one of those rare times when a cursor really is the best approach.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Damian-1673 SSCommitted Points: 1622 More actions · Answer 10

Sean Lange (11/21/2014)

Damian-167372 (11/21/2014)
Did you read the very first post from Koen? This is one of those rare times when a cursor really is the best approach.

Thanks, yes i did, but i still thought it can be done differently.

may be not then.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Damian-167372 (11/21/2014)

Sean Lange (11/21/2014)
Damian-167372 (11/21/2014)
Did you read the very first post from Koen? This is one of those rare times when a cursor really is the best approach.
Thanks, yes i did, but i still thought it can be done differently.
may be not then.

No unfortunately not. This is because you have to execute the stored proc against a number of databases. There really is no other way to do this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Damian-1673 SSCommitted Points: 1622 More actions · Answer 12

so your saying you cant loop it through that record set for each server in that server_list table?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

I think that you missed my point. A cursor IS a while loop (well, not exactly but it needs it to be useful).

There's a different way to do that, but a for these kind of queries the cursors are fine because you're going one server (or one database) at a time.

DECLARE @SQL varchar(8000);

SELECT @SQL = STUFF((

SELECT '

UNION ALL

SELECT loginame,

hostname,

dbname,

cmd

FROM OPENROWSET(''SQLNCLI'',''Server='+server_name+';Trusted_Connection=yes;'',''exec sp_who'')'

FROM dbo.Tbl_ServerList

FOR XML PATH(''),type).value('.', 'varchar(8000)'), 1, 11, '') ;

INSERT INTO Capture_Logins

EXEC(@SQL);

You can find other alternatives for management such as the one explained here:

http://msdn.microsoft.com/en-us/library/bb964743.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Damian-1673 SSCommitted Points: 1622 More actions · Answer 14

ok this what i have come up.. so you can do it

DECLARE @tbl_var TABLE( servername varchar (100 ), row_num INT IDENTITY ( 1 , 1 ) )

DECLARE @rowcnt INT

DECLARE @val_servername varchar( 100 )

DECLARE @row_move INT

INSERT INTO @tbl_var ( servername)

SELECT server_name from Server_List

SET @rowcnt = @@ROWCOUNT

SET @row_move = 1

WHILE @rowcnt >= @row_move

BEGIN

SELECT @val_servername = servername

FROM @tbl_var

WHERE row_num = @row_move

PRINT @val_servername

-- my query

SET @row_move = @row_move + 1

END