MS SQL Server ODBC - HELP!---Stored Procedure in ASP

Author

HELP!---Stored Procedure in ASP

segis bata

2006-04-03, 1:23 pm

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]

2006-04-03, 1:23 pm

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.

Edgardo Valdez, MCSD, MCDBA

2006-04-03, 1:23 pm

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

Jack Jackson

2006-04-03, 8:23 pm

On Mon, 3 Apr 2006 13:37:17 -0400, "segis bata"
<segisbata@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 ...

Bob Barrows [MVP]

2006-04-03, 8:23 pm

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.

Jack Jackson

2006-04-03, 8:23 pm

On Mon, 3 Apr 2006 15:27:04 -0400, "Bob Barrows [MVP]"
<reb01501@NOyahoo.SPAMcom> wrote:

>Jack Jackson wrote:
>??
>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]

2006-04-04, 9:23 am

Jack Jackson wrote:
> On Mon, 3 Apr 2006 15:27:04 -0400, "Bob Barrows [MVP]"
> <reb01501@NOyahoo.SPAMcom> wrote:
>
>
> 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.

Sponsored Links