If Exists capture value returned from stored proc

I have a stored proc with a query which checks whether an identicalvalue is already in the database table. If so, it returns a value of 1.How do I caputure this value in an asp.net page in order to display amessage accordingly? (using ASP.NET 1.1)

Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE...
)
Return 1
Else
...INSERT INTO... code here.Did this exact thing for someone already in the past 2 weeks, search the forums.|||

Motley wrote:

Did this exact thing for someone already in the past 2 weeks, search the forums.

Thanks, will do so.sql

if else with WHERE

Hi,
I'm trying to create a store proc and use a WHERE clause only if a certain
condition is met. When I use the WHERE after an if condition, the sql
enterprise tool tells me
it's invalid. "Invalid syntax near WHERE clause"
Here is my proc:
CREATE PROCEDURE [dbo].GetContractorsList
@.characterFilter char(1)
AS
SELECT
[ContractorCode],
[ContractorName],
FROM
[dbo].[Contractors]
if @.characterFilter !='*'
begin
WHERE ContractorName LIKE @.characterFilter + '%'
end
Any ideas on how to do this?
Thanks,
OpaOpa wrote:
> Hi,
> I'm trying to create a store proc and use a WHERE clause only if a
> certain condition is met. When I use the WHERE after an if
> condition, the sql enterprise tool tells me
> it's invalid. "Invalid syntax near WHERE clause"
>
http://www.sommarskog.se/dyn-search.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks for your reply Bob.
The article you posted is quite lengthy and refers to Dynamic SQL.
Can I do this without it?
"Bob Barrows [MVP]" wrote:

> Opa wrote:
> http://www.sommarskog.se/dyn-search.html
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||Yes, one of the methods described in the article should suit your purpose:
WHERE (@.characterFilter != '*' OR ContractorName LIKE @.characterFilter +
'%')
I suggest you read the entire article. It is quite informative.
Opa wrote:
> Thanks for your reply Bob.
> The article you posted is quite lengthy and refers to Dynamic SQL.
> Can I do this without it?
>
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

IF @@ROWCOUNT > 200 Return Nothing

Hi, I am trying to write a stored proc that will return data for use on a
webpage. I would like the SP to return nothing if the ROWCOUNT > 200. How do
I just return an error and no data?
TIAHi
Look up RAISERROR in SQL Server BOL
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"AlCoast" wrote:

> Hi, I am trying to write a stored proc that will return data for use on a
> webpage. I would like the SP to return nothing if the ROWCOUNT > 200. How
do
> I just return an error and no data?
> TIA|||Ok. Thank you. Will do
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Look up RAISERROR in SQL Server BOL
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "AlCoast" wrote:
>|||Try,
...
if (select count(*) from ...) > 200
raiserror('more than 200.', 16, 1)
else
select c1, ..., cn from ...
...
AMB
"AlCoast" wrote:

> Hi, I am trying to write a stored proc that will return data for use on a
> webpage. I would like the SP to return nothing if the ROWCOUNT > 200. How
do
> I just return an error and no data?
> TIA

If / Else Stored Proc -- What Am I doing wrong?

Bellow is the stored procedure. I wanted to do an If / Else statement. I'm getting a syntax error that something is wrong around my Begin / Else statements. If anyone knows what is wrong I would greatly appriciate it.

Thanks in advance as always.

RB

<code>

Create Proc UpdateFundsAndTotals
AS
IF (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPledges.fundID AND FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)
Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
END
ELSE (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPayments.fundID AND FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)
Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPayments = TotalPayments.TotalPayments
END
Else
END

</code>

what you have is pseudo code..the syntax is :

IF (Select somecolumnfrom sometablewhere condition2 <>select anothercolumnfrom anothertablewhere condition2)BeginUpdate sometable1set somecol= somevaluewhere theconditionEndelsebeginUpdate sometablesset somecol= somevaluewhere theconditionendNote that when you are checking for A <> B in the IF statement A and B should have only 1 values each..you cannot have a result set comparing to another result set.

|||

Are you sure you can do <code>if <condition> else <condition></code> ? I think you need an extra "if" block

|||

This isn't valid SQL:

Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
END

That's one issue. You seem to be trying to piggy-back onto the selects in the IF statement. They're separate statements; that doesn't work.

|||I don't 100% understand your IF/ELSE needs, but try something like this:
CREATE PROC UpdateFundsAndTotals
AS
IFEXISTS(SELECT NULLFROM FundsAndTotals INNER JOINTotalPledges ONFundsAndTotals.fundID = TotalPledges.fundID WHERE FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)

BEGIN

UPDATE
FundsAndTotals
SET
FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
FROM
FundsAndTotals
INNER JOIN
TotalPledges ONFundsAndTotals.fundID = TotalPledges.fundID
WHERE
FundsAndTotals.TotalPledges != TotalPledges.TotalPledges

END

ELSE

BEGIN

IFEXISTS(SELECT NULLFROM FundsAndTotals INNER JOINTotalPayments ONFundsAndTotals.fundID = TotalPayments.fundID WHERE FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)

BEGIN

UPDATE
FundsAndTotals
SET
FundsAndTotals.TotalPayments = TotalPayments.TotalPayments
FROM
FundsAndTotals
INNER JOIN
TotalPayments ONFundsAndTotals.fundID = TotalPayments.fundID
WHERE
FundsAndTotals.TotalPayments != TotalPayments.TotalPayments

END

END

|||wow Terri you have tremendous patience..you go to exceptional levels to understand a user's requirements..I always admire you for that.. Good job.|||

Thanks a lot Terri just wanted to post that your suggestion did the trick.

Thanks again.

RB

|||I am glad that helped! It seems to me that you don't really need the IF(EXISTS) parts of that since the WHERE condition in the UPDATEstatement should take care of the filtering.