Default datetime values?

I'm confused. I have two variables:
@.StartDate and @.EndDate. I have to figure out how to
execute this report each day through email grabbing
yesterdays data. I tried just for a test to put in GetDate
() and GetDate()-1 and I got an error. I'm wondering how
do I set two datetime variables for yesterday 12:00 AM to
today 12:00 AM. Please help,
Regards,
BryanBmurtha,
Try using DateAdd function like
=DateAdd(DateInterval.Day, -1, Today())
Regards,
Cem
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:46c301c47352$60dcc490$a601280a@.phx.gbl...
> I'm confused. I have two variables:
> @.StartDate and @.EndDate. I have to figure out how to
> execute this report each day through email grabbing
> yesterdays data. I tried just for a test to put in GetDate
> () and GetDate()-1 and I got an error. I'm wondering how
> do I set two datetime variables for yesterday 12:00 AM to
> today 12:00 AM. Please help,
> Regards,
> Bryan|||Or try:
=Today.AddDays(-1)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cem Demircioglu" <cem@.NoSpamPlease.com> wrote in message
news:e4cADS1cEHA.3988@.tk2msftngp13.phx.gbl...
>
> Bmurtha,
> Try using DateAdd function like
> =DateAdd(DateInterval.Day, -1, Today())
> Regards,
> Cem
>
> "bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
> news:46c301c47352$60dcc490$a601280a@.phx.gbl...
> > I'm confused. I have two variables:
> > @.StartDate and @.EndDate. I have to figure out how to
> > execute this report each day through email grabbing
> > yesterdays data. I tried just for a test to put in GetDate
> > () and GetDate()-1 and I got an error. I'm wondering how
> > do I set two datetime variables for yesterday 12:00 AM to
> > today 12:00 AM. Please help,
> >
> > Regards,
> > Bryan
>

Declaring Large Variables In SProc

Hello
I am trying to use the OPENXML command within a sproc to parse an xml
document and save the data to a table.
The xml document is saved in a different table in a field with data type
TEXT.
To parse the document, I first have to prepare it by calling ...
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
... where @.doc is the variable holding the xml document
I am trying to load the xml document into the local @.doc variable by ....
SELECT @.doc = xml_data FROM XMLTABLE
My problem is the xml document is 12-13k long so I can't declare the @.doc
variable as a varchar because a varchar can only be declared to a maximum of
8000 bytes and I cant declare it as a text datatype because local variables
can't be declare as text.
So.....what do I do?
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Peter,
Take a look at the following in BOL
TEXTPTR, WRITETEXT and UPDATETEXT.
You will first need to get a pointer to the Text field and then use the
other two functions to manipulate the content like so
declare @.ptr binary(16)
SELECT @.ptr = TEXTPTR(xml_data) FROM XMLTABLE
kevin
"Peter" wrote:

> Hello
> I am trying to use the OPENXML command within a sproc to parse an xml
> document and save the data to a table.
> The xml document is saved in a different table in a field with data type
> TEXT.
> To parse the document, I first have to prepare it by calling ...
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> ... where @.doc is the variable holding the xml document
> I am trying to load the xml document into the local @.doc variable by ....
> SELECT @.doc = xml_data FROM XMLTABLE
> My problem is the xml document is 12-13k long so I can't declare the @.doc
> variable as a varchar because a varchar can only be declared to a maximum
of
> 8000 bytes and I cant declare it as a text datatype because local variable
s
> can't be declare as text.
> So.....what do I do?
> Regards
> Peter
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||Thanks Kevin.
I have looked up what you suggested but still can't work out how it helps me
(it is 6.30am and I havn't been to bed yet).
I have retieved the pointer as you outlined but then what do I do with it?
How do I pass the contents of that pointer to the sp_xml_preparedocument
procedure?
Peter
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:6D465FA3-9C96-4D24-A32A-289ED73DD7E7@.microsoft.com...
> Peter,
> Take a look at the following in BOL
> TEXTPTR, WRITETEXT and UPDATETEXT.
> You will first need to get a pointer to the Text field and then use the
> other two functions to manipulate the content like so
> declare @.ptr binary(16)
> SELECT @.ptr = TEXTPTR(xml_data) FROM XMLTABLE
> kevin
> "Peter" wrote:
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter,
Excuse me if I double posted.
You can pass the pointer into the proc and then use the UPDATETEXT function
to manipulate the text in the xml. I would personally pull that text back t
o
code (c#/Java/VB/C++/etc) and manipulate it there, but I am assuming you hav
e
some plan for that. the following is an example
******************************
--the table and some data
CREATE TABLE dbo.kevtest (
[int] bigint IDENTITY (1, 1) NOT NULL ,
myname varchar (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
mytext text COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO dbo.kevtest(myname,mytext)('KEVIN','this is my text')
--the sp
ALTER proc dbo.usp_testptr
@.p binary(16)
as
updatetext dbo.kevtest.mytext @.p null 0 ' How you like me now?'
--the TSQL
declare @.ptr binary(16)
select * from dbo.kevtest
select @.ptr = TEXTPTR(mytext) from dbo.kevtest
EXEC dbo.usp_testptr @.ptr
select * from dbo.kevtest
******************************
kevin
"Peter" wrote:

> Thanks Kevin.
> I have looked up what you suggested but still can't work out how it helps
me
> (it is 6.30am and I havn't been to bed yet).
> I have retieved the pointer as you outlined but then what do I do with it?
> How do I pass the contents of that pointer to the sp_xml_preparedocument
> procedure?
> Peter
> "kevin" <kevin@.discussions.microsoft.com> wrote in message
> news:6D465FA3-9C96-4D24-A32A-289ED73DD7E7@.microsoft.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||That should be
INSERT INTO dbo.kevtest(myname,mytext) VALUES('KEVIN','this is my text')
"kevin" wrote:
> Peter,
> Excuse me if I double posted.
> You can pass the pointer into the proc and then use the UPDATETEXT functio
n
> to manipulate the text in the xml. I would personally pull that text back
to
> code (c#/Java/VB/C++/etc) and manipulate it there, but I am assuming you h
ave
> some plan for that. the following is an example
> ******************************
> --the table and some data
> CREATE TABLE dbo.kevtest (
> [int] bigint IDENTITY (1, 1) NOT NULL ,
> myname varchar (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> mytext text COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> INSERT INTO dbo.kevtest(myname,mytext)('KEVIN','this is my text')
> --the sp
> ALTER proc dbo.usp_testptr
> @.p binary(16)
> as
> updatetext dbo.kevtest.mytext @.p null 0 ' How you like me now?'
> --the TSQL
> declare @.ptr binary(16)
> select * from dbo.kevtest
> select @.ptr = TEXTPTR(mytext) from dbo.kevtest
>
> EXEC dbo.usp_testptr @.ptr
>
> select * from dbo.kevtest
> ******************************
> kevin
>
> "Peter" wrote:
>|||Thanks Kevin
I appreciate your time and effort here but the code you have supplied works
because the sproc you have created (usp_testptr) expects a pointer (or at
least a binary(16) value).
However, the sproc I need to use (sp_xml_preparedocument) expects a char,
varchar or text field so I cannot pass the pointer to it.
I may be totally and not seeing the forest for the trees here but I
still cant get you code to work in my circumstance.
Kind Regards
Peter
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:4152B5ED-54F8-4341-8539-C5BE18484A2E@.microsoft.com...
> That should be
> INSERT INTO dbo.kevtest(myname,mytext) VALUES('KEVIN','this is my text')
>
> "kevin" wrote:
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter,
[forum gurus please correct me if I am wrong!]
My suggestion is an alternative. Unless you can be certain that the xml
string will be less than 8000 characters you can't pass it to that sproc.
What I am suggesting is a way you can tell the sproc how to find the text
containing the xml so that it can manipulate it.
There are system stored procedures that you can use to read from a file, but
even then the sproc will still be limited to holding the xml in a
varchar(8000) as you can't create text variable in a sproc... as you already
know.
Why can't you deal with the xml in code?
Has this sproc every worked as you expect it to, or is it in development?
Kevin
"Peter" wrote:

> Thanks Kevin
> I appreciate your time and effort here but the code you have supplied work
s
> because the sproc you have created (usp_testptr) expects a pointer (or at
> least a binary(16) value).
> However, the sproc I need to use (sp_xml_preparedocument) expects a char,
> varchar or text field so I cannot pass the pointer to it.
> I may be totally and not seeing the forest for the trees here but
I
> still cant get you code to work in my circumstance.
> Kind Regards
> Peter
>
> "kevin" <kevin@.discussions.microsoft.com> wrote in message
> news:4152B5ED-54F8-4341-8539-C5BE18484A2E@.microsoft.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>

declare variables

New to stored procedures. Is it necessary to place a default value into a
variable at the time you declare it?
SAMPLE: “ @.Sec int = 100”
Can @.Sec just be declared?It can just be declared. It will default to NULL:
DECLARE @.SEC INT
SELECT @.SEC
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:10D3C4D8-9FD7-47E0-B77E-DD1C1A4EE8CA@.microsoft.com...
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: " @.Sec int = 100"
> Can @.Sec just be declared?
>|||No, it is not necessary, but until you put a value into it, it's value will
be Null. This can bite you if it's a char() or varChar() because by defaul
t
(there's a setting to change this, but don't use it) the nulls propagate whe
n
you concatenate them... i.e.,
null + 'dsasdasd' is null
"Rich" wrote:

> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:548AE03F-A9DD-441E-98AD-5F7416492EB5@.microsoft.com...
> No, it is not necessary, but until you put a value into it, it's value
will
> be Null. This can bite you if it's a char() or varChar() because by
default
> (there's a setting to change this, but don't use it) the nulls propagate
when
> you concatenate them... i.e.,
> null + 'dsasdasd' is null
It's no different with numeric types:
SELECT CONVERT(INT, NULL) + 1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ok, that is good but here is a second part to the question. If I place a
default value in the variable but then at run time, I have a agent that call
s
the stored procedure and pushes a value to it, will the value pushed in
always take priority over the default value?
"Rich" wrote:

> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||Are you talking about variables within the procedure, or paramaterss to the
procedure?
Variables within the proc, at any given point in code, will have whatever
value was last assigned (if any). Just like any other language. E.g:
DECLARE @.variable INT
-- @.variable is NULL
SET @.variable = 1
-- @.variable = 1
SET @.variable = 2
-- @.variable = 2
Parameters with a default value are different - if you pass the param to the
proc it will have the value you passed (including NULL); if you do not pass
the param it will have the default value. E.g:
CREATE PROC foo (@.i INT = 0) AS
BEGIN
SELECT @.i AS i
END
EXEC foo -- returns 0
EXEC foo @.i = 1 -- returns 1
EXEC foo @.i = NULL -- returns NULL
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Yes, If the parameter declaration in the Stored Proc has a default value,
and you nevertheless pass in a value, the passed in value will always take
pre3cedence over the default value.
This is true even When the Passed in value is Null, and teh default value
is somethiong other than Null...
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Hello CB,
perfect, I kind of tested that and found it to be true, I just wanted to
hear it from another programmer!
:)
"CBretana" wrote:
> Yes, If the parameter declaration in the Stored Proc has a default value,
> and you nevertheless pass in a value, the passed in value will always take
> pre3cedence over the default value.
> This is true even When the Passed in value is Null, and teh default value
> is somethiong other than Null...
> "Rich" wrote:
>

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 int <-- Here is my Problem
set @.i= @.i+1
end
all the variables @.NumberOfRecords, and the @.Record are passed to a stored procedure. Can some one help me plese. Any input is appreciated. Thank you.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.|||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|||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.|||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|||

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;

|||

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

|||

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

|||

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

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 int <-- Here is my Problem
set @.i= @.i+1
end
all the variables @.NumberOfRecords, and the @.Record are passed to a stored procedure. Can some one help me plese. Any input is appreciated. Thank you.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.|||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|||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.|||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|||

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;

|||

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

|||

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

|||

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

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 int <-- Here is my Problem
set @.i= @.i+1
end
all the variables @.NumberOfRecords, and the @.Record are passed to a stored procedure. Can some one help me plese. Any input is appreciated. Thank you.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.|||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|||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.|||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|||

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;

|||

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

|||

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

|||

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

Declaration of record variable

Can someone say how to declare a record like variable in MSSQL-2000 Like:
mr_rec record of variables a int, b char(20), c datetime? I could not find any examples on BOL. I want to use this in a stored procedure create script.
Thanks, Vinniethere is no such thing as a "record" variable in mssql.

You can declare a table variable however, this might meet your need. A table variable is basically a temp table. See BOL for more info on table variables.

Debugging / Breakpoints

Hi,

I didn't play around with breakpoints to much, yet. So today I created a package with 2 variables, which both raise their change event. I added a event handler for that just to show the name of the variable which initiated that event (system::variablename) in a messagebox. I added two scripts to the main workflow, one changes one variable, one the other.

To see what's happening I placed a breakpoint on the script in the event handler. I start the package, the first script is executed, the event is fired and the script is started. The breakpoint stops executing the package. So good so far. When I now click on "continue" the script task in the event handler changes to "yellow" and I hear a beep. That's all. I don't see any other action in the package, the second script is not started and the run seams to be locked.

What's wrong? Is the problem sitting in front of the monitor? Am I the 1000th person hitting that bug?

Thanks,

Thomas,

I've never seen that happen. Mind you I can't remember ever setting breakpoints on something in an eventhandler. Perhaps that could be the problem? I'm only guessing tho.

-Jamie