March 19, 2013 at 8:04 am
Here is the script for a stored procedure that i have, i want the stored procedure to return a value of 1,if it is a null or if there are no values for it to return ,
Create PROCEDURE [dbo].[GetExtractSiteExtractFileBatchPreviousSuccessInd]
@BatchStatusCd NVARCHAR(5)
,@ExtractSiteCd NVARCHAR (10)
,@ExtractAsOfDate DATETIME
AS
SELECT case when @ExtractAsOfDate = isnull(convert(varchar(10),ExtractAsOfDate,120),'1900-01-01') then 0
when @ExtractAsOfDate <> isnull(convert(varchar(10),ExtractAsOfDate,120),'1900-01-01')then 1 else 1 end
as ExtractSiteExtractFileBatchPreviousSuccessInd FROM ExtractSiteExtractFileBatch
WHERE BatchStatusCd = @BatchStatusCd
and ExtractSiteCd =@ExtractSiteCd
AND ExtractAsOfDate = @ExtractAsOfDate
go
here is the exec statement for the stored procedure, i want it to return a value of 1, if the value does not exist in the sample data.
EXEC@return_value = [dbo].[GetExtractSiteExtractFileBatchPreviousSuccessInd]
@BatchStatusCd = N'sb',
@ExtractSiteCd = N'CEPOK',
@ExtractAsOfDate = N'2013-03-18 00:00:00.000'
SELECT'Return Value' = @return_value
GO
below is the sample data
SELECT '[ExtractSiteExtractFileBatchID]','[ExtractSiteCd]','[ExtractAsOfDate]','[BatchStatusCd]','[InsertedDate]','[UpdatedDate]'
union all
select '1442','CEPOK','2012-07-12 00:00:00.000','SB','2012-07-13 06:04:41.843','2012-07-13 07:30:29.660'
union all
select '1443','CFTRT','2012-07-12 00:00:00.000','SB','2012-07-13 07:30:30.077','2012-07-13 08:29:40.433'
union all
select '5472','CEPED','2012-07-12 00:00:00.000','SBR','2013-03-18 17:34:12.047','2013-03-18 17:34:12.047'
March 19, 2013 at 8:16 am
Create PROCEDURE [dbo].[GetExtractSiteExtractFileBatchPreviousSuccessInd]
@BatchStatusCd NVARCHAR(5)
,@ExtractSiteCd NVARCHAR (10)
,@ExtractAsOfDate DATETIME
AS
BEGIN
SELECT case when @ExtractAsOfDate = isnull(convert(varchar(10),ExtractAsOfDate,120),'1900-01-01') then 0
when @ExtractAsOfDate <> isnull(convert(varchar(10),ExtractAsOfDate,120),'1900-01-01')then 1 else 1 end
as ExtractSiteExtractFileBatchPreviousSuccessInd FROM ExtractSiteExtractFileBatch
WHERE BatchStatusCd = @BatchStatusCd
and ExtractSiteCd =@ExtractSiteCd
AND ExtractAsOfDate = @ExtractAsOfDate
IF @@ROWCOUNT = 0 RETURN 1;
END
March 19, 2013 at 8:50 am
Thanks i will give it a try and let you know the results
March 19, 2013 at 10:58 am
i tried it still returns no value
March 19, 2013 at 11:04 am
klineandking (3/19/2013)
i tried it still returns no value
What do you mean it returns no VALUE?
Regardless of what you have in stored proc it always return a value! By default it's 0:
create proc p_test
as
select 1 as Col;
go
declare @ret int;
exec @ret = p_test;
select @ret;
go
And in your attachment it does return a value too!
March 19, 2013 at 11:09 am
CREATE PROCEDURE [dbo].[GetExtractSiteExtractFileBatchPreviousSuccessInd]
@BatchStatusCd NVARCHAR(5)
,@ExtractSiteCd NVARCHAR(10)
,@ExtractAsOfDate DATETIME
AS
RETURN (
SELECT CASE
WHEN EXISTS(
SELECT 1
FROM dbo.ExtractSiteExtractFileBatch
WHERE
BatchStatusCd = @BatchStatusCd
AND ExtractSiteCd = @ExtractSiteCd
AND ISNULL(ExtractAsOfDate, '19000101') = @ExtractAsOfDate
)
THEN 0
ELSE 1
END
)
GO
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 19, 2013 at 11:13 am
Ough, I can see now!
You need to learn and use proper terminology!
The stored procedure you have does return an integer value (as I've pointed out in the previous post by default it's zero).
You asked that you want your stored proc to return value of 1 when no records found, my changes made it to do exactly that.
What you really want is to return 1 in the recordset not as value!
You can change you proc to do so by replacing your select with following:
Edited: refer to the next post.
March 19, 2013 at 11:16 am
Even shorter version:
CREATE PROCEDURE [dbo].[GetExtractSiteExtractFileBatchPreviousSuccessInd]
@BatchStatusCd NVARCHAR(5)
,@ExtractSiteCd NVARCHAR(10)
,@ExtractAsOfDate DATETIME
AS
SELECT MIN(Res) AS ExtractSiteExtractFileBatchPreviousSuccessInd
FROM
(
SELECT 1 AS Res
UNION ALL
SELECT case when @ExtractAsOfDate = isnull(convert(varchar(10),ExtractAsOfDate,120),'1900-01-01') then 0
else 1
end as Res
FROM ExtractSiteExtractFileBatch
WHERE BatchStatusCd = @BatchStatusCd
and ExtractSiteCd =@ExtractSiteCd
and ExtractAsOfDate = @ExtractAsOfDate
) q
GO
Edited, so it can be used to create stored proc
March 19, 2013 at 12:19 pm
Thanks but i want the stored procedure to reture a single column 'GetExtractSiteExtractFileBatchPreviousSuccessInd' with the value, your script would only return the result as the return value
March 19, 2013 at 12:21 pm
klineandking (3/19/2013)
Thanks but i want the stored procedure to reture a single column 'GetExtractSiteExtractFileBatchPreviousSuccessInd' with the value, your script would only return the result as the return value
My last post does exactly that - it returns recordset with one column and one row.
March 19, 2013 at 12:33 pm
can this script be re written for it to return the values as a bit datat type?
March 19, 2013 at 12:52 pm
Yes it can. Just use CAST function.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply