Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedures

Author

Topic

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-23 : 10:11:26

If i have this SQL statement "Select * From User"
How do i put this statement in Stored Procedures? How do i loop the results in it. Coz in ASP it is like Do While NOT objRs.EOF. How is this done in Stored Procedure? I need this urgently, so pls help help. Thanks

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-23 : 10:18:04

It is exactly the same. There is no real magic in a stored procedure. It is just a procedure....that is stored.

So in your example

Create Procedure GetUsers

As

Select * from User

in your ASP

Set objRS = objConn.Execute("Exec GetUsers")

Damian

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-23 : 10:47:41

I can create this procedure sucessfully.

CREATE PROCEDURE AppReminder AS
SELECT a.*, c.Email AS Expr1
FROM FIS_Appointment a INNER JOIN
FIS_User b ON a.UserID = b.UserID INNER JOIN
FIS_StaffContact c ON b.StaffContactID = c.StaffContactID
WHERE (DATEDIFF(d, GETDATE(), a.ADate) = 1)
GO

But i cannot loop the results using this

Do While Not AppReminder.EOF
AppReminder.MoveNext
Loop

Any clue?

Nazim
A custom title

1408 Posts

Posted - 2002-04-23 : 11:17:24

Are you getting a Error or there are no records??.

Run the Stored procedure in Query Analyser and check the result .

--------------------------------------------------------------

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-23 : 11:19:56

You might also want to do rs.save and see what is comming back.

You need the full path to teh XML file to save, so I'm thinking this is how to do it in ASP

rs.save Server.MapPath("/") & "recordset.xml", 1 'Save as XML

Michael

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-23 : 18:27:02

quote:

How do i loop the results in it. Coz in ASP it is like Do While NOT objRs.EOF. How is this done in Stored Procedure?

Do NOT loop through records in a stored procedure. This can be done using a CURSOR, but with a little reading on this site, you'll quickly learn that cursors are evil (and very slow). What are you trying to do with this recordset once you have it selected? If you are just trying to display it on an ASP page, then I still would advise againts a DO While Not RS.EOF... LOOP logic and instead look into using RS.GetRows or RS.GetString. These are MUCH faster.

If you need to do some additional processing in your sproc, then tell us what it is and we can help you build a set-based (non-cursor) approach.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-04-23 : 21:15:29

Are you sure your ASP code is calling the stored procedure correctly. Here is one way to populate a recordset with a stored procedure

 
dim objComm
set objComm = server.createobject("ADODB.Command")
dim objRec
set objRec = server.createobject("ADODB.Recordset")

objComm.ActiveConnection = objSqlConnect 
objComm.CommandText = "AppReminder"
objComm.CommandType = adCmdStoredProc

set objRec = objComm.Execute()  'this is the importatn line

while not objRec.EOF() 
  response.write objRec.Fields(0) & " " & objRec.Fields(1).....
  objRec.MoveNext()
wend

If you are not connecting to SQL Server as the SA in your ASP (and I hope you arent) does the login you are connecting as have execute rights on the sproc?

hth,
Justin

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-24 : 07:03:45

I do not want to display it on the ASP page. I want the email addresses that i had got from the procedure and send emails to those emails addresses i had got.

Edited by - BaldEagle on 04/24/2002 07:29:27

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-24 : 11:34:15

Anyone can help me here. I really need this urgently

Nazim
A custom title

1408 Posts

Posted - 2002-04-24 : 11:45:36

what happens when you do this??

quote:

Run the Stored procedure in Query Analyser and check the result .

--------------------------------------------------------------

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-24 : 12:00:54

Where do u find the Query Analyser. Sorry really new to SQL Server

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-24 : 12:21:25

But i am sure there are results

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-24 : 19:16:07

Query Analyzer is one of the programs that comes with SQL Server. You should find it on the Start menu. It opens a window where you can type SQL commands directly in and run them.

If what you are wanting to do is send emails to a group of people, check out this article on mail queues [url]http://www.sqlteam.com/item.asp?ItemID=5908[/url]

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-25 : 07:39:58

quote:

quote:

How do i loop the results in it. Coz in ASP it is like Do While NOT objRs.EOF. How is this done in Stored Procedure?

Do NOT loop through records in a stored procedure. This can be done using a CURSOR, but with a little reading on this site, you'll quickly learn that cursors are evil (and very slow). What are you trying to do with this recordset once you have it selected? If you are just trying to display it on an ASP page, then I still would advise againts a DO While Not RS.EOF... LOOP logic and instead look into using RS.GetRows or RS.GetString. These are MUCH faster.

If you need to do some additional processing in your sproc, then tell us what it is and we can help you build a set-based (non-cursor) approach.

How are you going to do this? i saw the article before but it did not teach me how to loop through the results and get the email addresses.

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-26 : 11:00:53

Can any1 help me pls?

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-27 : 00:22:05

I really need this, pls help me.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-27 : 01:18:13

BaldEagle

It is really hard to help you when you keep changing what you want. In your first post you asked how to put a query in a stored procedure. You got the answer to that question, now you are talking about sending emails.

Why don't you explain exactly what you need to do. Also read the articles on send email and see if they help. Once you have a specific question, we can help.

Damian

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-27 : 02:19:59

ok my final question will be how do i loop through the results from my stored procedure but not using ASP or PHP.

So AjarnMark says "If you need to do some additional processing in your sproc, then tell us what it is and we can help you build a set-based (non-cursor) approach. "

So i tell him i need to loop through to send email.

but now i just want to know how to loop through the results with or without cursors. Thanks

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-27 : 02:23:42

How are you sending the emails ?

If you need to do it all in a stored proc, you might need a cursor to call an SMTP component.

But I would suggest something like the mail queue method, did you read the articles ?

Damian

BaldEagle
Starting Member

23 Posts

Posted - 2002-04-27 : 02:48:20

Yes i did read the articles but i really do not understand. Can give me some pointers.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-27 : 03:00:25

OK
Point 1. Define the problem.
What exactly do you want to do ???????????????????????????????????

Send the same email to lots of people ?
Send custom emails to people. ?

Do you have any method in mind ? DO you have something you are already using ? Do you have any components you already use ? Do you want to use SQL Mail.

If you use a mail queue technique like in the article, you just insert a bunch of rows (one for each user) into the queue table, and the service will send it.

But until you define the problem, and any contraints, I am guessing. And that doesn't help anyone.

You would have had this answer days ago if you had explained yourself properly.

Damian

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources