December 14, 2002 at 11:19 pm
Hi..
I have noticed a strange problem with SET ROWCOUNT when it is used with variables.. in this case ORDER BY does not work and have no effect at all... for example:
DECLARE @flights_table TABLE (RecordID int NOT NULL, ScheduledDateTime datetime NOT NULL, RescheduledDateTime datetime NULL)
DECLARE @future_rows int
SET @future_rows = 10
SET ROWCOUNT @future_rows
INSERT @flights_table
SELECT
RecordID, ScheduledDateTime, RescheduledDateTime
FROM Inbound
WHERE RescheduledDateTime >= '2002-12-14 08:30'
ORDER BY RecordID
SET ROWCOUNT 0
SELECT * FROM @flights_table
ORDER BY RescheduledDateTime ASC
This code gives a different result every time it is executed.. and the ORDER BY does not work..
However, if I change SET ROWCOUNT @future_rows to used a number instead of a variable like: SET ROWCOUNT 10 .. then the code will works correctly as expected.. also if I change flights_table to a temporary table instead of a table variable then the code will work correctly too.
Any thoughts, solutions or help are greatly appreciated..
Thanks in advance.
Regards,
Abdullah.
December 17, 2002 at 8:00 am
This was removed by the editor as SPAM
December 17, 2002 at 10:01 am
you cann't just do a select * from @variable.
You first have to assign this sql to a variable and the execute it
like
declare @sql 1000
select @sql='select *from'+@variable
exec(@sql)
December 17, 2002 at 10:05 am
Mkumari, I think you missed the fact that in akhaibari's code, the param @flights_table is actually a TABLE variable.
Therefore SELECT * FROM @flights_table is perfectly valid.
December 17, 2002 at 10:15 am
SQL BOL (SQL 2000) states the following regarding the SET ROWCOUNT command.
"It is recommended that DELETE, INSERT and UPDATE statements currently using SET ROWCOUNT by rewritten to use the TOP syntax."
...it also says.....
"This option should be used with caution and primarily with the SELECT statement."
Since you are using it to control the quantity of records being added in an INSERT statement I think you are using SET ROWCOUNT in what is now considered a "non-recommended" context.
December 17, 2002 at 9:56 pm
Thank you all guys.
Paulhumphris, the TOP keyword solves the problem and the results are excellent. However, the TOP keyword requires the use of an explicit number, not a variable. And if you try to use dynamic SQL to solve this (by formatting the query in string, then running it using exec() function, the table variable @flights_table won't be
Recognized inside the dynamic SQL string… and if you declare it inside the dynamic
string, Then it is not going to be recognized outside of it.
Anyway, I am thinking of using temporary tables in place of table variables as it works will with SET ROWCOUNT and gives expected results. Even though it degrades the performance where I need it most.
Again: Any thoughts, solutions or help are greatly appreciated..
Have a nice day.
December 17, 2002 at 9:56 pm
Thank you all guys.
Paulhumphris, the TOP keyword solves the problem and the results are excellent. However, the TOP keyword requires the use of an explicit number, not a variable. And if you try to use dynamic SQL to solve this (by formatting the query in string, then running it using exec() function, the table variable @flights_table won't be
Recognized inside the dynamic SQL string… and if you declare it inside the dynamic
string, Then it is not going to be recognized outside of it.
Anyway, I am thinking of using temporary tables in place of table variables as it works will with SET ROWCOUNT and gives expected results. Even though it degrades the performance where I need it most.
Again: Any thoughts, solutions or help are greatly appreciated..
Have a nice day.
December 18, 2002 at 2:50 am
You could try using a subquery within your dynamic sql, this would eliminate the need to use a temporary table and allow you to use the now recommended TOP method.
DECLARE @sql varchar(8000), @future_rows int
SET @future_rows = 10
SET @sql = 'SELECT a.* FROM
(SELECT TOP ' + convert(varchar, @future_rows) + 'RecordID, ScheduledDateTime, RescheduledDateTime
FROM Inbound
WHERE RescheduledDateTime >= ''2002-12-14 08:30''
ORDER BY RecordID) a
ORDER BY a.RescheduledDateTime ASC'
exec sp_execute(@sql)
December 18, 2002 at 3:12 am
Thanks again,
But as I have stated in my previous post I cannot use a subquery because I cannot retrieve the results of the subquery into a table variable since it would be out of range if it has been defined inside the subquery.
However, if you define the table variable outside the subquery, then it won't be defined inside the subquery.
Regards.
December 18, 2002 at 3:19 am
Ok I see your point (I am fully aware of variable scope and the scope of dynamic statements).
Just to give another view point, how about using the temporary table and populating it using an INSERT EXEC statement which could be built using the TOP statement. This would provide you with the table you could use outside the scope of the dynamic statement and utilise the recommended method of returning the TOP n number of records.
December 18, 2002 at 3:57 am
Just build the entire string including the declares within the Dynamic SQL string. It will build them in the same scope then.
Ex.
Won't work
DECLARE @int int
SET @int = 5
EXEC('SELECT @int')
Will work
EXEC('DECLARE @int int
SET @int = 5
SELECT @int')
December 18, 2002 at 4:13 am
Thanks again,
That would return us to the original problem where I have no problem with SET ROWCOUNT and temp tables but only with table variables... It seems like there is no escape from temp tables.
I Think that your solution is the best in this situation.
Thanks again and Regards.
December 18, 2002 at 4:14 am
Thanks again,
That would return us to the original problem where I have no problem with SET ROWCOUNT and temp tables but only with table variables... It seems like there is no escape from temp tables.
I Think that your solution is the best in this situation.
Thanks again and Regards.
December 18, 2002 at 4:32 am
Thanks antares686....
But how to retrieve the results of a subquery without temp tables? in other words I want to store the results of a subquery into table variable..
is that possible?
Regards.
December 18, 2002 at 4:50 am
As long as you can place it within the Dynamic SQL and keep all in scope then yes. If not then you may need to create a #temp table which should still be visible to the DynamicSQL or ##temp if I am wrong but there you run into issue with multiple people running at the same time and takes a bit of planning to contend with.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply