Solved

Stored Proecure statement precedence

Posted on 2003-11-21
13
339 Views
Last Modified: 2010-05-18
Hi,

Sometimes when im creating a stored procedure that needs to a) update or insert, b) return data the script which is executed through asp using something like

Set rs = .Execute (within the rest of the SP code)

The script will crash unless i put the select statement before the update or insert statement within the SP. Is there a reason for this, as sometimes this is simply impossible and i end up creating a second sp just to return the data which seems stupid?

Thanks in advance

Al Higgs




0
Comment
Question by:higgsy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9797628
Can you give an example of such a SP?

But ... why would you combine SELECT and UPDATE or INSERT in the same 'SP'?

A reason for that ...:
Because you do an INSERT or UPDATE and immediately after it, you try to do the SELECT (into a Recordset?) it's all the same TransAction. Your records are probably still LOCKED because of the INSERT/UPDATE ...

Does SQL gives you an error-code/message back?

Updating/Inserting/Deleting records and retrieving records, please hold them separated.
0
 
LVL 7

Expert Comment

by:mnye
ID: 9797953
have you done any error handling to return a error message?  could be a syntax error or the way you are passing a variable value.


hth
matt
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9797955
Please let me know if you need any help closing your many old open questions:

1 09/19/2003 500 How to ignore certain where statements i...  Open Microsoft SQL Server
2 09/23/2003 500 CASE Statement in SP  Open Microsoft SQL Server
3 09/26/2003 500 Keeping Int Identity values when transfe...  Open Microsoft SQL Server
4 09/27/2003 500 Streamin Windows Media player or alterna...  Open Web Development
5 10/22/2003 500 CASE Statement problems  Open Microsoft SQL Server
6 10/22/2003 500 Capturing all dud email addresses COM+  Open Web Development
7 10/23/2003 500 Check if variable has been declared in A...  Open Active Server Pages (ASP)
8 09/12/2003 500 Problems with Coalesce!!  Open Microsoft SQL Server
9 10/01/2003 500 Installing SSL certificate on Windows 20...  Open Windows 2000
10 10/03/2003 500 Executing another page without redirect ...  Open Web Development

Thanks,
Anthony
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9797970
Please see parameter object collection. Using parameter collections handle SP the the best possible way.

This code
Set rs = .Execute (within the rest of the SP code)

creashes because you are assigning the result set from SP into rs. When  you select from stored proc this result is placed into rs.  When nothing is restuned from stored proc the above statement generates error.

Within your proc at the end you can code something like
INSERT INTO MYTABLE VALUES (1)
SELECT @@ERROR as errorcode

Then you can read the rs object to check to see if errorcode is 0.

But the best way is to use parameter collection.


0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9798003
Pls handle open questions as Anthony suggests.

See this doc to see how to handle stored procs within ASP.

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q164/4/85.asp&NoWebContent=1
0
 

Author Comment

by:higgsy
ID: 9798168
Hi Anthony,

Ic ould do with a hand actually yeah, is there nowhere within my account i can just see a list of all my open questions rather than having to dig deep in every section to find them - would make life so much easier!!

Al
0
 
LVL 34

Expert Comment

by:arbert
ID: 9798405
higgsy, click on your name and you will see all the comments and questions you have asked....
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 9799187
Thank you for getting back to us.

>>is there nowhere within my account i can just see a list of all my open questions <<
As arbert has mentioned:
1. Go to your profile http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/viewMember.jsp?mid=1144166
2. Click on Question History View http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/viewQuestionHistory.jsp?mid=1144166
3. Click on type to sort the open questions at the top

And to your question:
The problem is that you are not including the SET NOCOUNT ON in your Stored Procedures.  This is causing the recordset to point to the "x record(s) affected" message instead of the Select statement that comes immediately after, so they are getting a null recordset and since they do not appear to have any error handoling in your code it is crashing.

Anthony
0
 
LVL 34

Expert Comment

by:arbert
ID: 9822053
Easy 500 points huh :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9822122
higgsy,

That is the second time you have done that (and I was the recipient in both cases).  Do you need help, closing your open questions?  Please see here to correct this:

I accepted the wrong answer. Now what?
http://www.experts-exchange.com/help/#11

namasi_navaretnam,
Let me know if you need me to post some points for you.  You seem to have lost out on both ocassions.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9822127
arbert,

>>Easy 500 points huh :) <<
I know.  On top of everything else, I am going to be accused of point passing.

Anthony
0
 
LVL 34

Expert Comment

by:arbert
ID: 9822143
lmao....
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9833066
> namasi_navaretnam,
> Let me know if you need me to post some points for you.  You seem to have lost out
> on both ocassions.

It is okay Anthony. There are instances where I do not deserve the points but I get them. There are other instances where I deserve some points, bit I do not get those points. All balance out end of the day.

Thanks for asking.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Percentage Formula 7 33
SQL State HYT00. Timeout expired proplem 8 44
SQL Query 9 29
How do i delete the last node in an xml in T-SQL 7 29
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question