Cursor Won''t Execute
- Hi,
I have a table that contains 3 columns - [Statement], [ServerName], [ReplaceValue]. When the three columns are placed together in a string it forms an execute stored procedure command. The table can contain up to 50 rows, which means 50 different execute stored procedure commands, therefore, what I want to do is put this into a cursor so that it will loop through each row, putting the three columns together and then executing the command. The curosor below, when executed, just returns the string for each row to the screen, it does not execute the statement.
Could someone please explain why this is, and show me what I need to do to get it to execute the code?
Thanks in advance.
DECLARE @sql nvarchar(4000)
DECLARE Localisation CURSOR FOR Select [Statement] + '''' + [ServerName] + '''' + ', ' + '''' + [ReplaceValue] + '''' From MyTable
OPEN Localisation
FETCH FROM Localisation
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sp_executesql @sql
-- print @sql
FETCH NEXT FROM Localisation
END
CLOSE Localisation
DEALLOCATE Localisation
www.sqlAssociates.co.uk
-
Where in this code does @sql get set?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Sorry, missed that bit out .....
DECLARE @sql nvarchar(4000)
SET @sql = (Select [Statement] + '''' + [ServerName] + '''' + ', ' + '''' + [ReplaceValue] + '''' From MyTable)
DECLARE Localisation CURSOR FOR Select [Statement] + '''' + [ServerName] + '''' + ', ' + '''' + [ReplaceValue] + '''' From MyTable
OPEN Localisation
FETCH FROM Localisation
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sp_executesql @sql
-- print @sql
FETCH NEXT FROM Localisation
END
CLOSE Localisation
DEALLOCATE Localisation
It just returns the error ....
Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Basically I am just wanting to execute the contents of MyTable row by row.
Thanks in advance.
www.sqlAssociates.co.uk
-
Hi,
If I just use the following it works fine, whats the best way to put this into a cursor so that it will loop through all the rows in MyTable and execute them?
USE MyDB
GO
declare @sql nvarchar(4000)
select @sql = (Select Top 1 [Statement] + '''' + [ServerName] + '''' + ', ' + '''' + [ReplaceValue] + '''' From MyTable)
--print @sql
exec sp_executesql @sql
Thanks in advance.
www.sqlAssociates.co.uk
-
When you use the FETCH command, you need to put the results of the FETCH into a local variable and then build your SQL string from that:
FETCH NEXT FROM Localisation INTO @strSQL
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil,
Thanks for your help, the cursor is pretty much doing what it's puposed to do
The only problem is that for some reason it's missing out the first row in the table.
If I just print @strSQL in Query Analyzer the "Grid" tab shows the first row in the table, then if I click the "Messages" tab it shows all the other rows in the table, and when I execute @strSQL it processes all the rows from the "Messages" tab but not the one from the "Grid" tab.
Thanks in advance.
DECLARE @strSQL nvarchar(4000)
DECLARE @sql nvarchar(4000)
DECLARE Localisation CURSOR FOR Select [Statement] + '''' + [ServerName] + '''' + ', ' + '''' + [ReplaceValue] + '''' From MyTable
OPEN Localisation
FETCH FROM Localisation
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- EXEC sp_executesql @strSQL
print @strSQL
FETCH NEXT FROM Localisation INTO @strSQL
END
CLOSE Localisation
DEALLOCATE Localisation
GO
www.sqlAssociates.co.uk
-
OK. Try using FETCH FIRST as your initial FETCH statement - and you need the local variable there too:
FETCH FIRST FROM Localisation into @strSQL
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil,
Sorry to be a pain (I am trying to figure this out myself). When I execute the following code ...
DECLARE @strSQL nvarchar(4000)
DECLARE @sql nvarchar(4000)
DECLARE Localisation CURSOR FOR Select [Statement] + '''' + [ServerName] + '''' + ', ' + '''' + [ReplaceValue] + '''' From MyTable
OPEN Localisation
FETCH FROM Localisation
WHILE (@@FETCH_STATUS = 0)
BEGIN
--EXEC sp_executesql @strSQL
print @strSQL
FETCH FIRST FROM Localisation INTO @strSQL
END
CLOSE Localisation
DEALLOCATE Localisation
GO
The following error message is returned .....
Server: Msg 16911, Level 16, State 1, Line 13
fetch: The fetch type first cannot be used with forward only cursors.
I have tried a few other changes to the cursor but none of them have worked.
Thanks for your help, it's much appreciated.
www.sqlAssociates.co.uk
-
Ah yes, there are different types of cursors - it's looking like the default type is forward only, which means that the FIRST qualifier cannot be used.
Try this
FETCH NEXT FROM Localisation into @strSQL
WHILE (@@FETCH_STATUS = 0)
BEGIN
--EXEC sp_executesql @strSQL
print @strSQL
FETCH NEXT FROM Localisation INTO @strSQL
END
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil,
Thats worked a treat!!
Thanks for your help, it's greatly appreciated.
Chris.
www.sqlAssociates.co.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply