MS SQL Server - Procedure

Author

Procedure

amatuer

2006-11-08, 7:12 pm

Hi I am writing a produre in SQL Server. Can any1 tel me how do i
create a recordset? I have a sql statement that queries data, bt nw i
would like to loop through the data so that i can do some processing.

Thanx in advance.

Uri Dimant

2006-11-08, 7:12 pm

Can you elaborate on this? What are you trying to achive?
What is the versrion are you using?

"amatuer" <njoosub@gmail.com> wrote in message
news:1162991074.709471.260570@h48g2000cwc.googlegroups.com...
> Hi I am writing a produre in SQL Server. Can any1 tel me how do i
> create a recordset? I have a sql statement that queries data, bt nw i
> would like to loop through the data so that i can do some processing.
>
> Thanx in advance.
>

2006-11-08, 7:12 pm

I I understand you correctly, I believe you asking 2different question
here...
any1 tel me how do i create a recordset?

result of select statement a record

have a sql statement that queries data, bt nw i
would like to loop through the data so that i can do some processing.

You could use cursor or while loop
insert the processed record into temp table
after finishing the record processing
return the data from temp table

vt

"amatuer" <njoosub@gmail.com> wrote in message
news:1162991074.709471.260570@h48g2000cwc.googlegroups.com...
> Hi I am writing a produre in SQL Server. Can any1 tel me how do i
> create a recordset? I have a sql statement that queries data, bt nw i
> would like to loop through the data so that i can do some processing.
>
> Thanx in advance.
>

amatuer

2006-11-08, 7:12 pm

For example in asp wen u query data the data is put into a recordset of
which u can then step through this recordset to do calculations. I want
to so the same here, where i can step through the data that i queried
so that i cn do my calculations. How can i do this? I am nt sure if a
recordset can be created nor do i knw how to create one.

Uri Dimant wrote:[color=darkred
]
> Can you elaborate on this? What are you trying to achive?
> What is the versrion are you using?
>
>
>
> "amatuer" <njoosub@gmail.com> wrote in message
> news:1162991074.709471.260570@h48g2000cwc.googlegroups.com...

John Bell

2006-11-08, 7:12 pm

Hi

It sounds like you are looking to declare a cursor and process each row
separately. Look up DECLARE CURSOR in the Transact SQL section of Books
online for more information and examples. In general using a cursor is not a
good idea if a set based solution can be found. You may want to post your
code and what you are trying to achieve to see if there is a better way of
doing this. See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post
DDL and sample data.

John

"amatuer" wrote:

> Hi I am writing a produre in SQL Server. Can any1 tel me how do i
> create a recordset? I have a sql statement that queries data, bt nw i
> would like to loop through the data so that i can do some processing.
>
> Thanx in advance.
>
>

Tracy McKibben

2006-11-08, 7:12 pm

amatuer wrote:
> For example in asp wen u query data the data is put into a recordset of
> which u can then step through this recordset to do calculations. I want
> to so the same here, where i can step through the data that i queried
> so that i cn do my calculations. How can i do this? I am nt sure if a
> recordset can be created nor do i knw how to create one.
>

You're talking about a CURSOR (see Books Online). You can do this
within a stored procedure, but it's generally advised against.
Procedural operations (like cursors) perform much more poorly than
set-based operations. See if there is a way to do your calculations
across an entire data set.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Uri Dimant

2006-11-08, 7:12 pm

amatuer
As others have already pointed out take a look at CURSORs in the BOL.
Also , see how WHILE BEGIN ...END loop is working , which under some
circumstances may work faster

"amatuer" <njoosub@gmail.com> wrote in message
news:1162993079.171239.72230@f16g2000cwb.googlegroups.com...
> For example in asp wen u query data the data is put into a recordset of
> which u can then step through this recordset to do calculations. I want
> to so the same here, where i can step through the data that i queried
> so that i cn do my calculations. How can i do this? I am nt sure if a
> recordset can be created nor do i knw how to create one.
>
> Uri Dimant wrote:
>

amatuer

2006-11-08, 7:12 pm

Heres the code:

Declare @i int
Declare @Yr int
Declare @Datum DateTime
Declare @Datum2 DateTime
Declare @Reenval int
Declare @Reenval2 int
Declare @name varchar(35)

SET @Yr = 1986

While (@Yr < 2006)
Begin
Set @Datum = "1/1/" & @Yr
Set @Datum2 = @Datum + 9
Set @Reenval2 = 0
Set @i = 1

While (@i < 37)

Set @Reenval = 0

SELECT @Reenval = (SUM(Reenval_Silo) AS Sum_Silo) FROM R_Reenval With
(Nolock)
WHERE (NAAM = '@name') AND (DATUM BETWEEN '@Datum' AND '@Datum2')
GROUP BY NAAM

Set @Reenval2 = @Reenval2 + @Reenval

Insert Into R_Wheat_10_Dupl (Naam, BeginDatum, EindDatum, Reenval,
BeginJaar, BeginMaand, BeginDag, EindJaar, EindMaand, EindDag)
Values (@name, @Datum, @Datum2, @Reenval, Year(@Datum), Month(@Datum),
Day(@Datum), Year(@Datum2), Month(@Datum2), Day(@Datum2))

Set @Datum = @Datum +10
Set @Datum2 = @Datum2 +10

Set @i = @i + 1
End

Set @Yr = @Yr + 1
End

As can be seen in the above code there is a bid loop that runs through
years. Now i would like to query data from my db getng names of places.
Then i would like to run thorugh that data in a loop. This data (names
of places) would now be the outa loop (i.e. this biggest loop).

This is the query i am gna use for the names of places loop: SELECT
DISTINCT NAAM
FROM R_Reenval.

Thanx for the help.

Tracy McKibben wrote:
> amatuer wrote:
>
> You're talking about a CURSOR (see Books Online). You can do this
> within a stored procedure, but it's generally advised against.
> Procedural operations (like cursors) perform much more poorly than
> set-based operations. See if there is a way to do your calculations
> across an entire data set.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Arnie Rowland

2006-11-08, 7:12 pm

No, he/she is an Access programmer and is really talking about a
recordset...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"Tracy McKibben" <tracy@realsqlguy.com> wrote in message
news:4551DEE6.9030402@realsqlguy.com...
> amatuer wrote:
>
> You're talking about a CURSOR (see Books Online). You can do this within
> a stored procedure, but it's generally advised against. Procedural
> operations (like cursors) perform much more poorly than set-based
> operations. See if there is a way to do your calculations across an
> entire data set.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

John Bell

2006-11-08, 7:12 pm

Hi

You could probably do this in a single statement, although I am not totally
clear what you are trying to do especially as there is no ddl or sample data
(see my other post). If you want to group by a 10 day date range some thing
like:

SELECT naam,
datum,
DATEADD(d,((datepart
(dy,datum)/10)*10)- datepart(dy,datum),d
atum) AS datum1,
DATEADD(d,(((datepar
t(dy,datum)/10)+1)*10)- datepart(dy,datum),d
atum) AS
datum2
FROM R_Reenval

Then this can be used in an INSERT..SELECT statement such as:

INSERT INTO R_Wheat_10_Dupl (Naam, BeginDatum, EindDatum, Reenval,
BeginJaar, BeginMaand, BeginDag, EindJaar, EindMaand, EindDag)
SELECT r.naam, datum1, datum2,
SELECT SUM(d.Reenval_Silo)
FROM R_Reenval d
WHERE d.NAAM = r.NAAM AND d.DATUM BETWEEN datum1 AND datum2 ),
Year(r.Datum), Month(r.Datum), Day(r.Datum), Year(r.Datum2),
Month(r.Datum2), Day(r.Datum2)
FROM
( SELECT naam,
datum,
DATEADD(d,((datepart
(dy,datum)/10)*10)- datepart(dy,datum),d
atum) AS datum1,
DATEADD(d,(((datepar
t(dy,datum)/10)+1)*10)- datepart(dy,datum),d
atum) AS
datum2
FROM R_Reenval ) R

If you want to get 0 values for dates where there is no data then you would
need to use a calendar table such as
http://sqlserver2000.databases.aspf...ndar-table.html

It would also be quicker if the subquery could be incorporated in to the
derived table.

Hopefully from this you may be able to get a faster solution!

John

"amatuer" wrote:

> Heres the code:
>
> Declare @i int
> Declare @Yr int
> Declare @Datum DateTime
> Declare @Datum2 DateTime
> Declare @Reenval int
> Declare @Reenval2 int
> Declare @name varchar(35)
>
> SET @Yr = 1986
>
> While (@Yr < 2006)
> Begin
> Set @Datum = "1/1/" & @Yr
> Set @Datum2 = @Datum + 9
> Set @Reenval2 = 0
> Set @i = 1
>
> While (@i < 37)
>
> Set @Reenval = 0
>
> SELECT @Reenval = (SUM(Reenval_Silo) AS Sum_Silo) FROM R_Reenval With
> (Nolock)
> WHERE (NAAM = '@name') AND (DATUM BETWEEN '@Datum' AND '@Datum2')
> GROUP BY NAAM
>
> Set @Reenval2 = @Reenval2 + @Reenval
>
> Insert Into R_Wheat_10_Dupl (Naam, BeginDatum, EindDatum, Reenval,
> BeginJaar, BeginMaand, BeginDag, EindJaar, EindMaand, EindDag)
> Values (@name, @Datum, @Datum2, @Reenval, Year(@Datum), Month(@Datum),
> Day(@Datum), Year(@Datum2), Month(@Datum2), Day(@Datum2))
>
> Set @Datum = @Datum +10
> Set @Datum2 = @Datum2 +10
>
> Set @i = @i + 1
> End
>
> Set @Yr = @Yr + 1
> End
>
> As can be seen in the above code there is a bid loop that runs through
> years. Now i would like to query data from my db getng names of places.
> Then i would like to run thorugh that data in a loop. This data (names
> of places) would now be the outa loop (i.e. this biggest loop).
>
> This is the query i am gna use for the names of places loop: SELECT
> DISTINCT NAAM
> FROM R_Reenval.
>
> Thanx for the help.
>
>
> Tracy McKibben wrote:
>
>

amatuer

2006-11-09, 7:13 pm

Hi, I solved my previous problem bt nw i have another question. Firstly
I am using Microsoft SQL Enterprise Manager, Version: 8.0.

As pointed out previously I am an Access developer.
I want to now execute my procedure in SQL Server. I tried executing it
in SQL Query Analyzer by clicking on the execute query button. This
message displays: The command(s) completed successfully, but when I
have a look at my tables, no processing has taken place.

Below is my code. Please have a look at it and point out any errors if
any.

Declare @i int
Declare @x int
Declare @Yr int
Declare @Datum DateTime
Declare @Datum2 DateTime
Declare @Reenval int
Declare @Reenval2 int
Declare @name varchar(35)
Declare @TName varchar(35)
Declare @Count1 int

SELECT DISTINCT TOP 1 @TName = (NAAM) FROM R_Reenval With (Nolock)
Group By NAAM ORDER BY NAAM

SELECT @Count1 = Count (DISTINCT NAAM) FROM R_Reenval With (Nolock)
Group By NAAM ORDER BY NAAM

Set @Count1 = @Count1 + 1

Set @x = 1

While (@x < @Count1)
Begin

If @x > 1
SELECT DISTINCT TOP 1 @name = (NAAM) FROM R_Reenval With (Nolock)
WHERE (NAAM > @TName) ORDER BY NAAM
Else
Set @name = @TName

SET @Yr = 1986

While (@Yr < 2006)
Begin
Set @Datum = '1/1/' & @Yr
Set @Datum2 = @Datum + 9
Set @Reenval2 = 0
Set @i = 1

While (@i < 37)
Begin

Set @Reenval = 0

SELECT @Reenval = (SUM(Reenval_Silo)) FROM R_Reenval With (Nolock)
WHERE (NAAM = @name) AND (DATUM BETWEEN @Datum AND @Datum2)
GROUP BY NAAM

Set @Reenval2 = @Reenval2 + @Reenval

Insert Into R_Wheat_10_Dupl (Naam, BeginDatum, EindDatum, Reenval,
BeginJaar, BeginMaand, BeginDag, EindJaar, EindMaand, EindDag)
Values (@name, @Datum, @Datum2, @Reenval, Year(@Datum), Month(@Datum),
Day(@Datum), Year(@Datum2), Month(@Datum2), Day(@Datum2))

Set @Datum = @Datum +10
Set @Datum2 = @Datum2 +10

Set @i = @i + 1
End

Set @Yr = @Yr + 1
End

Set @x = @x + 1
End

Any suggestions? Thanx for all the response.

John Bell wrote:[color=darkred
]
> Hi
>
> You could probably do this in a single statement, although I am not totally
> clear what you are trying to do especially as there is no ddl or sample data
> (see my other post). If you want to group by a 10 day date range some thing
> like:
>
> SELECT naam,
> datum,
> DATEADD(d,((datepar
t(dy,datum)/10)*10)- datepart(dy,datum),d
atum) AS datum1,
> DATEADD(d,(((datepa
rt(dy,datum)/10)+1)*10)- datepart(dy,datum),d
atum) AS
> datum2
> FROM R_Reenval
>
> Then this can be used in an INSERT..SELECT statement such as:
>
> INSERT INTO R_Wheat_10_Dupl (Naam, BeginDatum, EindDatum, Reenval,
> BeginJaar, BeginMaand, BeginDag, EindJaar, EindMaand, EindDag)
> SELECT r.naam, datum1, datum2,
> SELECT SUM(d.Reenval_Silo)
> FROM R_Reenval d
> WHERE d.NAAM = r.NAAM AND d.DATUM BETWEEN datum1 AND datum2 ),
> Year(r.Datum), Month(r.Datum), Day(r.Datum), Year(r.Datum2),
> Month(r.Datum2), Day(r.Datum2)
> FROM
> ( SELECT naam,
> datum,
> DATEADD(d,((datepar
t(dy,datum)/10)*10)- datepart(dy,datum),d
atum) AS datum1,
> DATEADD(d,(((datepa
rt(dy,datum)/10)+1)*10)- datepart(dy,datum),d
atum) AS
> datum2
> FROM R_Reenval ) R
>
> If you want to get 0 values for dates where there is no data then you would
> need to use a calendar table such as
> http://sqlserver2000.databases.aspf...ndar-table.html
>
> It would also be quicker if the subquery could be incorporated in to the
> derived table.
>
> Hopefully from this you may be able to get a faster solution!
>
>
>
> John
>
> "amatuer" wrote:
>

John Bell

2006-11-09, 7:13 pm

Hi

This is only going to press one value of NAAM and the value returned by
SELECT @Count1 = Count (DISTINCT NAAM) FROM R_Reenval With (Nolock)
Group By NAAM ORDER BY NAAM

will be the count of the last NAAM which would be 1. Maybe you want

SELECT @Count1 = Count (*) FROM R_Reenval
WHERE NAAM = @TName

John

"amatuer" wrote:

> Hi, I solved my previous problem bt nw i have another question. Firstly
> I am using Microsoft SQL Enterprise Manager, Version: 8.0.
>
> As pointed out previously I am an Access developer.
> I want to now execute my procedure in SQL Server. I tried executing it
> in SQL Query Analyzer by clicking on the execute query button. This
> message displays: The command(s) completed successfully, but when I
> have a look at my tables, no processing has taken place.
>
> Below is my code. Please have a look at it and point out any errors if
> any.
>
> Declare @i int
> Declare @x int
> Declare @Yr int
> Declare @Datum DateTime
> Declare @Datum2 DateTime
> Declare @Reenval int
> Declare @Reenval2 int
> Declare @name varchar(35)
> Declare @TName varchar(35)
> Declare @Count1 int
>
> SELECT DISTINCT TOP 1 @TName = (NAAM) FROM R_Reenval With (Nolock)
> Group By NAAM ORDER BY NAAM
>
> SELECT @Count1 = Count (DISTINCT NAAM) FROM R_Reenval With (Nolock)
> Group By NAAM ORDER BY NAAM
>
> Set @Count1 = @Count1 + 1
>
> Set @x = 1
>
> While (@x < @Count1)
> Begin
>
> If @x > 1
> SELECT DISTINCT TOP 1 @name = (NAAM) FROM R_Reenval With (Nolock)
> WHERE (NAAM > @TName) ORDER BY NAAM
> Else
> Set @name = @TName
>
>
> SET @Yr = 1986
>
> While (@Yr < 2006)
> Begin
> Set @Datum = '1/1/' & @Yr
> Set @Datum2 = @Datum + 9
> Set @Reenval2 = 0
> Set @i = 1
>
> While (@i < 37)
> Begin
>
> Set @Reenval = 0
>
> SELECT @Reenval = (SUM(Reenval_Silo)) FROM R_Reenval With (Nolock)
> WHERE (NAAM = @name) AND (DATUM BETWEEN @Datum AND @Datum2)
> GROUP BY NAAM
>
> Set @Reenval2 = @Reenval2 + @Reenval
>
> Insert Into R_Wheat_10_Dupl (Naam, BeginDatum, EindDatum, Reenval,
> BeginJaar, BeginMaand, BeginDag, EindJaar, EindMaand, EindDag)
> Values (@name, @Datum, @Datum2, @Reenval, Year(@Datum), Month(@Datum),
> Day(@Datum), Year(@Datum2), Month(@Datum2), Day(@Datum2))
>
> Set @Datum = @Datum +10
> Set @Datum2 = @Datum2 +10
>
> Set @i = @i + 1
> End
>
> Set @Yr = @Yr + 1
> End
>
> Set @x = @x + 1
> End
>
> Any suggestions? Thanx for all the response.
>
> John Bell wrote:
>
>

amatuer

2006-11-09, 7:13 pm

No offense John but i tested the query and it does give me the answer i
am looking for. My main question is, how do i execute this procedure?

John Bell wrote:[color=darkred
]
> Hi
>
> This is only going to press one value of NAAM and the value returned by
> SELECT @Count1 = Count (DISTINCT NAAM) FROM R_Reenval With (Nolock)
> Group By NAAM ORDER BY NAAM
>
> will be the count of the last NAAM which would be 1. Maybe you want
>
> SELECT @Count1 = Count (*) FROM R_Reenval
> WHERE NAAM = @TName
>
> John
>
> "amatuer" wrote:
>

John Bell

2006-11-09, 7:13 pm

Hi

What you posted is only a script and not a stored procedure. You should
check out books online on how to create stored procedures using the CREATE
PROCEDURE statement and execute them with the EXEC statement.

As this is only a script just pressing the execute query button (green
arrow) in Query Analyser will execute the script. If it says "The command(s)
completed successfully" after doing this and you verify that it has not
changed your data, then there is a problem with the script.

John

"amatuer" wrote:

> No offense John but i tested the query and it does give me the answer i
> am looking for. My main question is, how do i execute this procedure?
>
>
> John Bell wrote:
>
>

amatuer

2006-11-09, 7:13 pm

Hi, i have this line at the top of the script.
CREATE PROCEDURE [dbo].[Wheat_10] AS

John Bell wrote:[color=darkred
]
> Hi
>
> What you posted is only a script and not a stored procedure. You should
> check out books online on how to create stored procedures using the CREATE
> PROCEDURE statement and execute them with the EXEC statement.
>
> As this is only a script just pressing the execute query button (green
> arrow) in Query Analyser will execute the script. If it says "The command(s)
> completed successfully" after doing this and you verify that it has not
> changed your data, then there is a problem with the script.
>
> John
>
>
> "amatuer" wrote:
>

Tracy McKibben

2006-11-09, 7:13 pm

amatuer wrote:
> Hi, i have this line at the top of the script.
> CREATE PROCEDURE [dbo].[Wheat_10] AS
>

If your script includes that line, then executing the script just
CREATES the stored proc, it doesn't run the query. To execute the
sproc, open a new query window and use the EXECUTE command:

EXEC MySproc

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

John Bell

2006-11-09, 7:13 pm

Hi

XXX per the last email you will need to execute the stored procedure with
the EXEC command. You can debug the stored procedure in Query Analyser using
the object browser (F8). Find the stored procedure in the hierarchy, right
click and choose the debug menu item (at the bottom!). This will allow you to
step through each statement and view the values of the variables.

John

"amatuer" wrote:

> Hi, i have this line at the top of the script.
> CREATE PROCEDURE [dbo].[Wheat_10] AS
>
> John Bell wrote:
>
>

amatuer

2006-11-10, 5:14 am

Thanx guys, Now i know how to execute my procedure.
Now i have another problem. Heres the code:

SET @Yr = 1986
Set @Datum = '1/1/' & @Yr

I am experiencing a problem with the "Set @Datum " statement. Does
anyone know how would I use the cast function to convert this, " '1/1/'
& @Yr " to be put into my DateTime var,@Datum?

Thanx.

John Bell wrote:[color=darkred
]
> Hi
>
> XXX per the last email you will need to execute the stored procedure with
> the EXEC command. You can debug the stored procedure in Query Analyser using
> the object browser (F8). Find the stored procedure in the hierarchy, right
> click and choose the debug menu item (at the bottom!). This will allow you to
> step through each statement and view the values of the variables.
>
> John
>
> "amatuer" wrote:
>

Tibor Karaszi

2006-11-10, 5:15 am

See my reply to your other post.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"amatuer" <njoosub@gmail.com> wrote in message
news:1163140995.801417.306480@e3g2000cwe.googlegroups.com...
> Thanx guys, Now i know how to execute my procedure.
> Now i have another problem. Heres the code:
>
> SET @Yr = 1986
> Set @Datum = '1/1/' & @Yr
>
> I am experiencing a problem with the "Set @Datum " statement. Does
> anyone know how would I use the cast function to convert this, " '1/1/'
> & @Yr " to be put into my DateTime var,@Datum?
>
> Thanx.
>
> John Bell wrote:
>

John Bell

2006-11-10, 5:15 am

If you have problems finding the post Tibor is referring to see
http://tinyurl.com/yhfbv5

I am not sure why you are continuing to try and solve this with procedural
code

John

"amatuer" wrote:

> Thanx guys, Now i know how to execute my procedure.
> Now i have another problem. Heres the code:
>
> SET @Yr = 1986
> Set @Datum = '1/1/' & @Yr
>
> I am experiencing a problem with the "Set @Datum " statement. Does
> anyone know how would I use the cast function to convert this, " '1/1/'
> & @Yr " to be put into my DateTime var,@Datum?
>
> Thanx.
>
> John Bell wrote:
>
>

Sponsored Links