Solved

Stored Proecure statement precedence

Posted on 2003-11-21
13
325 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
Comment Utility
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
Comment Utility
Easy 500 points huh :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
lmao....
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
> 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 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

17 Experts available now in Live!

Get 1:1 Help Now