Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > HELP!---Stored Procedure in ASP

Reply
Thread Tools

HELP!---Stored Procedure in ASP

 
 
segis bata
Guest
Posts: n/a
 
      04-03-2006
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
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-03-2006
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.


 
Reply With Quote
 
 
 
 
Edgardo Valdez, MCSD, MCDBA
Guest
Posts: n/a
 
      04-03-2006
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

 
Reply With Quote
 
Jack Jackson
Guest
Posts: n/a
 
      04-03-2006
On Mon, 3 Apr 2006 13:37:17 -0400, "segis bata"
<(E-Mail Removed)> 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 ...
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-03-2006
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.


 
Reply With Quote
 
Jack Jackson
Guest
Posts: n/a
 
      04-04-2006
On Mon, 3 Apr 2006 15:27:04 -0400, "Bob Barrows [MVP]"
<(E-Mail Removed)> 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.
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-04-2006
Jack Jackson wrote:
> On Mon, 3 Apr 2006 15:27:04 -0400, "Bob Barrows [MVP]"
> <(E-Mail Removed)> 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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
procedure as argument in procedure AlexWare VHDL 2 10-23-2009 09:14 AM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
regatding stroed procedure in asp.net Yoshitha ASP .Net 4 02-21-2005 12:53 PM
javascript function call a asp.net sub procedure Steven K ASP .Net 3 04-14-2004 10:21 PM



Advertisments