By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,617 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,617 IT Pros & Developers. It's quick & easy.

HELP!---Stored Procedure in ASP

P: n/a
Hello everyone,

I'm writing you because I need help in something that's taking too long. I want to build a Stored Procedure to be accessed by an ASP page and I need that Stored Procedure to do a couple of things:
1.. Depending on parameters sent by the ASP page, do a SELECT statement to a table (tblX01) and if it returns ZERO records, the SP should insert some info into other table (tblX02) and if returns ONE record, it won't insert into tblX02
2.. Insert some other info in another table (tblX03)
With #2 I don't have a problem, is with #1, how do I know the select statement returns ZERO or ONE record? how do I capture that value in the Stored Procedure?

Thanks for your prompt response,
SB-R
Apr 3 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
segis bata wrote:
Hello everyone,

I'm writing you because I need help in something that's taking too
long. I want to build a Stored Procedure to be accessed by an ASP
page and I need that Stored Procedure to do a couple of things:
1.. Depending on parameters sent by the ASP page, do a SELECT
statement to a table (tblX01) and if it returns ZERO records, the SP
should insert some info into other table (tblX02) and if returns ONE
record, it won't insert into tblX02
2.. Insert some other info in another table (tblX03)
With #2 I don't have a problem, is with #1, how do I know the select
statement returns ZERO or ONE record? how do I capture that value in
the Stored Procedure?

IF EXISTS (SELECT * FROM tblX01 WHERE ...)
BEGIN
INSERT INTO tblX02 ...
END
HTH,
Bob Barrows

PS. Please don't crosspost to so many groups. sqlserver.programming was the
only really relevant group, although .asp.db would have been all right as
well, despite the fact that the question was answerable without even
mentioning asp.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Apr 3 '06 #2

P: n/a
on 1. you can use the following

if (SELECT count(*) from tblX01 where [your condition]) = 0
insert into tblX02

you can add the 2. part after it

Let me know if it answers what you asked...

"segis bata" wrote:
Hello everyone,

I'm writing you because I need help in something that's taking too long. I want to build a Stored Procedure to be accessed by an ASP page and I need that Stored Procedure to do a couple of things:
1.. Depending on parameters sent by the ASP page, do a SELECT statement to a table (tblX01) and if it returns ZERO records, the SP should insert some info into other table (tblX02) and if returns ONE record, it won't insert into tblX02
2.. Insert some other info in another table (tblX03)
With #2 I don't have a problem, is with #1, how do I know the select statement returns ZERO or ONE record? how do I capture that value in the Stored Procedure?

Thanks for your prompt response,
SB-R

Apr 3 '06 #3

P: n/a
On Mon, 3 Apr 2006 13:37:17 -0400, "segis bata"
<se*******@hotmail.com> wrote:
Hello everyone,

I'm writing you because I need help in something that's taking too long. I want to build a Stored Procedure to be accessed by an ASP page and I need that Stored Procedure to do a couple of things:
1.. Depending on parameters sent by the ASP page, do a SELECT statement to a table (tblX01) and if it returns ZERO records, the SP should insert some info into other table (tblX02) and if returns ONE record, it won't insert into tblX02
2.. Insert some other info in another table (tblX03)
With #2 I don't have a problem, is with #1, how do I know the select statement returns ZERO or ONE record? how do I capture that value in the Stored Procedure?


If you don't care how many records exist:

IF NOT EXISTS(SELECT ?? FROM TABLE ....)
INSERT ....
ENDIF

If you need to know how many exist:

DECLARE @cnt int
SELECT @cnt = COUNT(*) FROM ...
Apr 3 '06 #4

P: n/a
Jack Jackson wrote:
If you don't care how many records exist:

IF NOT EXISTS(SELECT ?? FROM TABLE ....)
INSERT ....
ENDIF

??
Are you sure you are talking about SQL Server?
True, the OP did not mention which database he was using, but judging from
the newsgroups in his crosspost ...

To the OP: it is always a good idea to tell us the type and version of
database you are using ...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Apr 3 '06 #5

P: n/a
On Mon, 3 Apr 2006 15:27:04 -0400, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:
Jack Jackson wrote:
If you don't care how many records exist:

IF NOT EXISTS(SELECT ?? FROM TABLE ....)
INSERT ....
ENDIF

??
Are you sure you are talking about SQL Server?
True, the OP did not mention which database he was using, but judging from
the newsgroups in his crosspost ...

To the OP: it is always a good idea to tell us the type and version of
database you are using ...


I, like you, assumed the poster wanted a SQL Server stored procedure.
My code is exactly like yours, except yours did not say NOT EXISTS
which is what I think he wanted.

Or maybe you thought I meant to actually put the ?? in the SELECT
statement - I didn't, I intended that to be a place where he would put
some field name.
Apr 4 '06 #6

P: n/a
Jack Jackson wrote:
On Mon, 3 Apr 2006 15:27:04 -0400, "Bob Barrows [MVP]"
<re******@NOyahoo.SPAMcom> wrote:
Jack Jackson wrote:
If you don't care how many records exist:

IF NOT EXISTS(SELECT ?? FROM TABLE ....)
INSERT ....
ENDIF
??
Are you sure you are talking about SQL Server?
True, the OP did not mention which database he was using, but
judging from the newsgroups in his crosspost ...

To the OP: it is always a good idea to tell us the type and version
of database you are using ...


I, like you, assumed the poster wanted a SQL Server stored procedure.
My code is exactly like yours, except yours did not say NOT EXISTS
which is what I think he wanted.

:-)
Exactly? Look again. I do not believe the "NOT" keyword is the difference I
am talking about. :-)
And yes, you are right: I did neglect to put in the "NOT" keyword, but at
least my code would compile :-)

Or maybe you thought I meant to actually put the ?? in the SELECT
statement - I didn't, I intended that to be a place where he would put
some field name.


1. That's only one of the differences.
2. Using a field name is totally unnecessary. Granted, it won't harm
anything - SQL Server ignores the select clause when performing an EXISTS
subquery.

Look, I'm not trying to put you on the defensive, or start an argument. I
just thought you might get a chuckle when you spotted the error I was
talking about. (I've made the same mistake, especially after attempting to
write t-sql code after an extended period writing VB - although your mistake
looks a little more pascal-ish)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Apr 4 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.