Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Stored Proecure statement precedence

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
higgsy
Asked:
higgsy
  • 4
  • 3
  • 3
  • +3
1 Solution
 
wsteegmansCommented:
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
 
mnyeCommented:
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
 
Anthony PerkinsCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
namasi_navaretnamCommented:
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
 
namasi_navaretnamCommented:
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
 
higgsyAuthor Commented:
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
 
arbertCommented:
higgsy, click on your name and you will see all the comments and questions you have asked....
0
 
Anthony PerkinsCommented:
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
 
arbertCommented:
Easy 500 points huh :)
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
arbert,

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

Anthony
0
 
arbertCommented:
lmao....
0
 
namasi_navaretnamCommented:
> 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now