Throw error if date fails the conversion or invalid date string
-
Hi All, I am using a stored proc which is using a depending upon that it is pulling some values and make some changes in the database, what I need to do is to throw an error when date conversion from that string or text fails and should not perform further action. Can anybody please help me with this, any code snippet, a link or even a suggestion helps me, thanks in advance. Below is my code
ALTER procedure [dbo].[TrackUpdatedColumnNamesProc]
(@DateToTrack nvarchar(max))
as
begin
--declare @DateToTrack nvarchar(max)='2016-03-18'
delete from TrackUpdatedColumnNames;
--check here (as soon as conversion of @DateToTrack fails I shouldn't be doing any logic
--doing the logic here
end
GO
declare @DateToTrack nvarchar(max)='2016-03-18'
exec [dbo].[TrackUpdatedColumnNamesProc] @DateToTrack
GOThanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I am using a stored proc which is using a depending upon that it is pulling some values and make some changes in the database, what I need to do is to throw an error when date conversion from that string or text fails and should not perform further action. Can anybody please help me with this, any code snippet, a link or even a suggestion helps me, thanks in advance. Below is my code
ALTER procedure [dbo].[TrackUpdatedColumnNamesProc]
(@DateToTrack nvarchar(max))
as
begin
--declare @DateToTrack nvarchar(max)='2016-03-18'
delete from TrackUpdatedColumnNames;
--check here (as soon as conversion of @DateToTrack fails I shouldn't be doing any logic
--doing the logic here
end
GO
declare @DateToTrack nvarchar(max)='2016-03-18'
exec [dbo].[TrackUpdatedColumnNamesProc] @DateToTrack
GOThanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
So use try/catch in the stored proc. However you are not processing sequentially, that requires a cursor or while or a CTE, so you should test the date conversion before performing the delete. Pretty sure there is and IsDate function in TSQL.
Never underestimate the power of human stupidity RAH
-
So use try/catch in the stored proc. However you are not processing sequentially, that requires a cursor or while or a CTE, so you should test the date conversion before performing the delete. Pretty sure there is and IsDate function in TSQL.
Never underestimate the power of human stupidity RAH
-
Hi All, I am using a stored proc which is using a depending upon that it is pulling some values and make some changes in the database, what I need to do is to throw an error when date conversion from that string or text fails and should not perform further action. Can anybody please help me with this, any code snippet, a link or even a suggestion helps me, thanks in advance. Below is my code
ALTER procedure [dbo].[TrackUpdatedColumnNamesProc]
(@DateToTrack nvarchar(max))
as
begin
--declare @DateToTrack nvarchar(max)='2016-03-18'
delete from TrackUpdatedColumnNames;
--check here (as soon as conversion of @DateToTrack fails I shouldn't be doing any logic
--doing the logic here
end
GO
declare @DateToTrack nvarchar(max)='2016-03-18'
exec [dbo].[TrackUpdatedColumnNamesProc] @DateToTrack
GOThanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
The simplest option is to change your parameter's type to
date. Then, if the caller tries to pass in a value that isn't a date, an error will be thrown, and the procedure will not be executed. You should always use an appropriate data type for the data you're dealing with. Storing dates in strings is a sign of a bad design.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
