Stored Procedure Conundrum

Stored Procedure Conundrum

Post by Stev » Fri, 21 Jun 2002 02:15:03



Hi,

I am just getting to grips with using Stored Procedures for more than
just basic queries, although I am totally stumped when trying to do
something quite simple. Please help.....

I have a query that will produce a recordset that I need to "cycle"
through whilst still inside the stored procedure. I am used to asp
where I am able to refer to a RecordSet and simply use RS.MoveNext
(inside a loop) to find the next record and manipulate it.

Can I do similar looping logic inside a SP? I am sure this is
possible, just don't know how!

Thanks in advance,

Steve

 
 
 

Stored Procedure Conundrum

Post by Dejan Sark » Fri, 21 Jun 2002 02:23:54


Steve,

Row processing can be done in SQL Server by using Cursors or While loops.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:> Hi,

> I am just getting to grips with using Stored Procedures for more than
> just basic queries, although I am totally stumped when trying to do
> something quite simple. Please help.....

> I have a query that will produce a recordset that I need to "cycle"
> through whilst still inside the stored procedure. I am used to asp
> where I am able to refer to a RecordSet and simply use RS.MoveNext
> (inside a loop) to find the next record and manipulate it.

> Can I do similar looping logic inside a SP? I am sure this is
> possible, just don't know how!

> Thanks in advance,

> Steve


 
 
 

Stored Procedure Conundrum

Post by Michael MacGrego » Fri, 21 Jun 2002 02:21:30


What you are talking about is a cursor but you probably don't need one so it
might be better if you provide more details on exactly what you are trying
to do with the data, and it would help to provide the tables (CREATE TABLE),
example data (INSERT INTO) and your expected result, you'll probably find
that someone here will provide you with a nice set based solution.

Michael MacGregor
Database Architect
SalesDriver

 
 
 

Stored Procedure Conundrum

Post by Wayne Snyde » Fri, 21 Jun 2002 02:27:09


1. you may use a cursor... Look up "cursor" in books on line... We generally
try to avoid using cursors due to the poor performance...
2. another way is to simply select the next record with the next higher id

Probably you will end up using a cursor.

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), C*te, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org


Quote:> Hi,

> I am just getting to grips with using Stored Procedures for more than
> just basic queries, although I am totally stumped when trying to do
> something quite simple. Please help.....

> I have a query that will produce a recordset that I need to "cycle"
> through whilst still inside the stored procedure. I am used to asp
> where I am able to refer to a RecordSet and simply use RS.MoveNext
> (inside a loop) to find the next record and manipulate it.

> Can I do similar looping logic inside a SP? I am sure this is
> possible, just don't know how!

> Thanks in advance,

> Steve

 
 
 

1. Stored Proc wildcard conundrum - help!

I'm perplexed. I've got a Stored Procedure in MS SQL Server 2000 which takes
a parameter and then uses this in the Where clause of a Select statement.

The procedure is called from Java using the JDBC-ODBC bridge. Prior to the
procedure called the string value which will given as a parameter has "%"
appended to either side of it so that it in effect it becomes an "any part
of string" type query.

The problem is that while the query works when I call it from Query Viewer,
it doesn't work from the Java, i.e. no values are returned. I've checked the
vales that Java sends and also the values that the procedure receives and
they look fine - i.e have "%" on both sides.

I think it's something to do with the fact that the procedure takes the
value as nvarchar, but the Java class is working with Strings. I've even
tried casting to nvarchar when the parameter is received by the procedure,
confusingly this helps a little but still only some of the values are
returned.

Please help - I'm desparate

Steven

2. Help: ODBC with NT

3. US-NJ: Teaneck-Oracle DBA Pharmaceuticals

4. Calling a Java Stored Procedure from another Java Stored Stored Procedure

5. Error 1105 during bcp data import despite plenty free space

6. Stored Procedure calling Stored Procedure

7. Problem: Scan Loop Through Over 30,000

8. Executing a Stored Procedure in a Stored Procedure and selecting on the result

9. Call a stored procedure from another stored procedure

10. Calling a stored procedure with parameters from another stored procedure

11. using SQL stored procedure results in stored procedure

12. Using Resultset in Stored Procedure in another stored procedure