declare programaticly a X number of variables

Hello everyone,

I really need some help; I need to declare dinamicly an number of records to be pulled out of the table.. so I need to declare first
DECLARE @NumberOfRecords int
then
DECLARE @i int
set @i=0
and
WHILE @i<@NumberOfReocords
    begin
             declare
                      @Record Idea

int <-- Here is my Problem
set @i= @i+1
end
all the variables @NumberOfRecords, and the @Record Idea

are passed to a stored procedure. Can some one help me plese. Any input is appreciated. Thank you.

Answer this question

declare programaticly a X number of variables

Jarron

See this post for more details on how to do it in SQL Server 2000 using identity column and temporary table approach.

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=117121&SiteID=1

Fahmy

Umachandar Jayachandran - MS wrote:

Order of records does not make sense in a table since it is essentially an unordered set of rows. You can only talk about records if the order in which you fetch rows or count rows is deterministic. So to this effect, you have to include an ORDER BY in your query against the table for example to say number records based on the sort order. You can easily number rows in SQL Server 2005 using the ROW_NUMBER function. This allows you to generate a sequential number for each row in a result set based on a particular order. This can be combined with a filter to get specific number of rows based on the order. Ex:

-- fetches 1 to 10 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 1 and 10;

-- fetches 11 to 20 rows sorted by keycol order
with paged_t
as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 11 and 20;

Note that this doesn't guarantee that you will get distinct rows for each selection since it depends on other transactions against the table. If you are inserting new rows that can appear in the beginning, it is possible to get same row twice and so on. There are ways to avoid this but it will affect concurrency (using serializable isolation level for example). The scenario I described above is a paging scenario.

On the other hand, if you just need some N number of rows sorted by some column every time then you can just use the TOP clause in a SELECT statement. You can number the rows easily on the client-side.

-- @numrows is parameter to SP:
select top(@numrows) *
from tbl
order by keycol;

Hi Jayachandran

excellent, i am also interested but I am very much keen to know how can it(row sequence #) be generated in SQL server 2000 using query.

please help

thanks in advance

ChrisNewmanUK
Basicly, I have a database and I was asket to code a ASP webform in C# in which the user forst enter am integer representing the number of records she/he needs to pull out of the database and the enter the records ID's ... it is for statistical reasons... I thought that the easeast way is to create a store procedure on which to pass the parameters... Any ideeas. Thank you
allez
Perhaps if you tell us the exact problem you are trying to solve, it will be easier to suggest a more efficient solution than using dynamic SQL or relying on multiple variables. For example, TOP clause now takes any expression in SQL Server 2005 that you can use to dynamically retrieve n number of rows. Ex:

select top (select count(*) from tbl1) *
from tbl2
order by keycol;

You could also use row_number() or identity to generate a sequence number and then process rows based on it.
209732
Hi,

Can u tell is that equivalent to this query

select * from <table_name> order by column1 limit 0,10 -- iam taking top 10 records from the table.

But iam using this in query in a cursor and i will get the value (0,10) from the result set of an other query

Please Help, Iam new to MYSQL

Thanks,

Murali.V
Bender

Order of records does not make sense in a table since it is essentially an unordered set of rows. You can only talk about records if the order in which you fetch rows or count rows is deterministic. So to this effect, you have to include an ORDER BY in your query against the table for example to say number records based on the sort order. You can easily number rows in SQL Server 2005 using the ROW_NUMBER function. This allows you to generate a sequential number for each row in a result set based on a particular order. This can be combined with a filter to get specific number of rows based on the order. Ex:

-- fetches 1 to 10 rows sorted by keycol order
with paged_t

as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 1 and 10;

-- fetches 11 to 20 rows sorted by keycol order
with paged_t
as

(

select *, row_number() over(order by keycol) as rownum from table

)

select * from page_t
where rownum between 11 and 20;

Note that this doesn't guarantee that you will get distinct rows for each selection since it depends on other transactions against the table. If you are inserting new rows that can appear in the beginning, it is possible to get same row twice and so on. There are ways to avoid this but it will affect concurrency (using serializable isolation level for example). The scenario I described above is a paging scenario.

On the other hand, if you just need some N number of rows sorted by some column every time then you can just use the TOP clause in a SELECT statement. You can number the rows easily on the client-side.

-- @numrows is parameter to SP:
select top(@numrows) *
from tbl
order by keycol;
Rob Reiss
I did not quite understand your question. Are you asking how to declare number of variables dynamically If so you could use dynamic SQL. See sp_executesql topic in Books Online for more details.
threna
sometimes i need to pull out 3 records.. other times 5 records, etc... and each time i need to specify the recordID ( e.g. 1000323, 1000356, 1000365) ... so each time i need to have a different number of @RecordID's @RecordID1, @RecordID2.. etc.

Once again thank you
declare programaticly a X number of variables

Answer this question