I have a stored sprocedure with two parameters that currently searches a table for a matching record see SQL below:
**************************************************************
@.WorkOrderNum numeric(18,0)
,@.StackNum numeric(18,0)
AS
--This procedure is used in iFIX. It looks for matching
--Work Order Number / Stack Number combination.
SELECT
work_order_no
,stack_no
FROM
dbo.prod_data
WHERE
(dbo.prod_data.work_order_no = @.WorkOrderNum)
AND (dbo.prod_data.stack_no = @.StackNum)
******************************************************************************
What I need is a stored procedure that will look for a matching criteria and if it finds some it will return a value of "2", if it does not find any criteria it needs to return a value of "1". This value does not need to be stored, for display only.
Any help would be greatly appreciated
You could do below:
Code Snippet
select case when exists(
SELECT *
FROM
dbo.prod_data as p
WHERE
p.work_order_no = @.WorkOrderNum
AND p.stack_no = @.StackNum
) then 2 else 1 end as matched
Thanks it worked perfectly
