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