Weird Behavior in SET ROWCOUNT with variables

Question

Post reply

Weird Behavior in SET ROWCOUNT with variables

Viewing 6 posts - 16 through 20 (of 20 total)

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

akhaibari SSC Journeyman Points: 86 More actions · Answer 1

I Would like to thank you all guys for your help.

Regards.

tcartwright SSCertifiable Points: 5573 More actions · Answer 2

You could declare the table variable inside the dynamic sql and use it as an output variable from a call to sp_executesql, and pass in your locally declared table variable.

Tim C.

//Will write code for food

Tim C //Will code for food

paulhumphris Hall of Fame Points: 3496 More actions · Answer 3

Tim, have you got an example of that you can post here?

akhaibari SSC Journeyman Points: 86 More actions · Answer 4

Thanks Tim for your post...

I've tried what you've suggested, but with no success. output parameters can be anything but table variables. Here is my code:

DECLARE @flights_table TABLE (RecordID int NOT NULL, ScheduledDateTime datetime NOT NULL, RescheduledDateTime datetime NULL)

DECLARE @future_rows int

DECLARE @sql nvarchar(1000)

DECLARE @ParmDefinition nvarchar(500)

SET @future_rows = 10

SET @sql =

N'INSERT @out_table

SELECT TOP ' + CONVERT(varchar(10), @future_rows) + '

RecordID, ScheduledDateTime, RescheduledDateTime

FROM Inbound

WHERE RescheduledDateTime >= GETDATE()

ORDER BY RescheduledDateTime ASC'

SET @ParmDefinition =N'@out_table TABLE (RecordID int NOT NULL, ScheduledDateTime datetime NOT NULL, RescheduledDateTime datetime NULL) OUTPUT'

--exec(@sql)

EXECUTE sp_executesql

@sql,

@ParmDefinition,

@flights_table OUTPUT

The above code gives the error:

Must declare the variable '@flights_table'.

However, if you use any other type for @flights_table and @out_table it will work like charm.

Are there anyway to solve this problem.. or there is no escape from using temp tables?

Regards.

tcartwright SSCertifiable Points: 5573 More actions · Answer 5

Sorry for not trying the sp_executesql with a temp teable before posting. I have used it with other scalar variable types, and made a dumb assumption that it would work for you there as well. I am finding QUITE A FEW restrictions on table variables that are not present for temp tables. Several of which have caused me to do things that I would have preferred to do a cleaner way. Paul I can give you several examples of sp_executesql using scalar variables if you still desire. Just post back to let me know if so.

Tim C.

//Will write code for food

Tim C //Will code for food

akhaibari SSC Journeyman Points: 86 More actions · Answer 6

Thanks Tim. It is not your fault, it is the fault of Microsoft, as it is logical to assume that you can use a table variables as any other scalar variable. But this limitation with OUTPUT parameters is not documented, even OPUTPUT parameters it self are not documented.

If anyone knows a way around this problem, without using temp variables, please let me know. This code will be called very frequently and I would have a serious performance issue if I use temp tables.

Regards.