Solved

Stored Proecure statement precedence

Posted on 2003-11-21
13
328 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now