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




higgsyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.