Control Result of ExecuteScalar

bes7252

The user is calling a Stored Procedure with ExecuteScalar. When the SQL doesn't find a match, I'd like to return the results of a different SQL.

For Example:

If this doesn't find a match:

select amount from Lookup Where application = @app

Then I'd like to return:

select amount from Lookup Where application = "DEFAULT"

My actual situation is more complex than this. The first SQL is in a CASE statement. After my CASE is done, can I check the current ExecuteScalar return value Or someone determine how many records are in the last SQL to execute

Also, ExecuteScalar always seems to get the 1st column of the 1st query. Can I have it get the 1st column from the 3rd query

Arnie Rowland

ExecuteScalar returns only one value -what that value is depends upon the stored procedure.

Yes, you can return any one value from any combination of queries.

To best assist you, please post the entire stored procedure, a description of what results are desired.

bes7252

I'm including my SP below. The Lookup table has a column named amount. If the CASE statement, when method="LIST", I'd like to return Lookup.amount if there are no matching records in the LookupList table.

USE [SharedDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PR_LookupPrice]

(

@app varchar(8),

@billcode varchar(4),

@code varchar(20),

@value sql_variant

)

AS

SET NOCOUNT OFF;

SELECT CASE

WHEN method = 'SET'

THEN (select amount from Lookup Where application = @app AND billcode = @billcode AND code = @code)

WHEN method = 'VAR'

THEN (Select LookupVariable.amount from Lookup INNER JOIN LookupVariable

On Lookup.lookupid = LookupVariable.lookupid

Where application = @app AND billcode = @billcode AND code = @code and low <= @value and high >= @value)

WHEN method = 'COMP'

THEN (select SUM(LookupCompounding.amount) from Lookup INNER JOIN LookupCompounding

On Lookup.lookupid = LookupCompounding.lookupid

Where application = @app AND billcode = @billcode AND code = @code)

WHEN method = 'LIST'

THEN (select LookupList.amount from Lookup INNER JOIN LookupList

On Lookup.lookupid = LookupList.lookupid

Where application = @app AND billcode = @billcode AND code = @code and lookuplist.value = @value)

END AS 'result'

FROM Lookup WHERE application = @app AND billcode = @billcode AND code = @code

Arnie Rowland

Thanks, that helps.

I've revised the procedure for readibiltiy.

Code Snippet

ALTER PROCEDURE [dbo].[PR_LookupPrice]
( @App varchar(8),
@BillCode varchar(4),
@Code varchar(20),
@Value sql_variant
)
AS

BEGIN

SET NOCOUNT OFF;

DECLARE @ReturnValue decimal(10,2)

SELECT @ReturnValue = CASE Method
WHEN 'SET'
THEN Amount
WHEN 'VAR'
THEN (SELECT lv.Amount
FROM Lookup l
INNER JOIN LookupVariable lv
ON l.LookupID = lv.LookupID
WHERE ( l.Application = @App
AND l.BillCode = @BillCode
AND l.Code = @Code
AND ( lv.Low <= @Value
AND lv.High >= @Value
)
)
)
WHEN 'COMP'
THEN (SELECT sum( lc.Amount )
FROM Lookup l
INNER JOIN LookupCompounding lc
ON l.LookupID = lc.LookupID
WHERE ( l.Application = @App
AND l.BillCode = @BillCode
AND l.Code = @Code
)
)
WHEN 'LIST'
THEN (SELECT isnull( ll.Amount, l.Amount )
FROM Lookup l
INNER JOIN LookupList ll
ON l.LookupID = ll.LookupID
WHERE ( l.Application = @App
AND l.BillCode = @BillCode
AND l.Code = @Code
AND ll.Value = @Value
)
)
END
FROM Lookup
WHERE ( Application = @App
AND BillCode = @BillCode
AND Code = @Code
)

IF @ReturnValue IS NULL
SELECT Amount
FROM Lookup
WHERE Application = "DEFAULT"

RETURN @ReturnValue

END

The changes are in yellow, I think that everything else is just formatting.

Control Result of ExecuteScalar

Re: Control Result of ExecuteScalar

Re: Control Result of ExecuteScalar

Re: Control Result of ExecuteScalar