Select Top w/ variable?
-
I'm trying to use a variable to determine how many of the top I need. I get a compile error when I try it. It works fine if I replace @orig_free_qty w/ a hard-coded value. Am I stuck w/ using hard-code?
UPDATE @Orig_Calendar Set free_day = 1 Where orig_start in (Select Top @orig_free_qty orig_start from @Orig_Calendar order by orig_start desc)
-
You will need to either use dynamic SQL or "SET ROWCOUNT @orig_free" instead of TOP. If you use SET ROWCOUNT, don't forget to set it back to 0 after the statements.
--Jonathan -
When I do this:
SELECT @orig_free_qty = 9
SET ROWCOUNT @orig_free_qty
UPDATE @Orig_Calendar Set orig_free_day = 1
I get this:
Invalid argument for SET ROWCOUNT. Must be a non-null non-negative integer.
-
What data type is @orig_free_qty?
--Jonathan -
@orig_free_qty numeric(4),
-
Can you use that information along with the error message to find the problem?
--Jonathan -
SET ROWCOUNT expects an INTEGER datatype, not a numeric(4). Changing your datatype to integer will resolve the problem.
-
Hm, Jonathan, that reminds me of those quizes on TV where you have to tell your name, and if you're right, win $1,000.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Let's not forget our "other DBMS" colleagues.
With "anOther DBMS", integer is an alias for a particular definition of numeric, so Rob was maybe quite justifiably stumped.
Cheers,
- Mark -
Me bad! No offense meant!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply