HELP!---Stored Procedure in ASP

[segis bata]

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

 

[Bob Barrows [MVP]]

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.

 

[Edgardo Valdez, MCSD, MCDBA]

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...

 

[Jack Jackson]

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 ...

 

[Bob Barrows [MVP]]

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 ...

 

[Jack Jackson]

??
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.

 

[Bob Barrows [MVP]]

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