Function results

I am trying to convert a string value, from a parameter field, into a date t
o
insert into a SQL2005 db smalldatetime field. The function returns a bit
value, which I test by using the isdate function, and if true convert this t
o
a smalldatetime value, else I set to null.
I get error that "Syntax incorrect at @.test".
What is incorrect about what I am trying to do'
...
DECLARE @.RecNumber bigint,
@.AGM1 smalldatetime,
@.AGM2 smalldatetime,
@.SDt smalldatetime,
@.Test bit
SET NOCOUNT ON;
@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
if @.test = 1 then
@.AGM1 = convert(varchar(10),@.Adv_Met1,101)
else
@.AGM1 = null
end if
Any help is appreciated.@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
Yes, there is! This is not VBScript, you need to use SET or SELECT
SET @.test = dbo.fn_testdate(@.Adv_Met1);
SELECT @.test = dbo.fn_testdate(@.Adv_Met1);
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message
news:ABD935E6-40A8-4F73-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
>to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
> to
> a smalldatetime value, else I set to null.
> I get error that "Syntax incorrect at @.test".
> What is incorrect about what I am trying to do'
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
> Any help is appreciated.|||> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
Also a bunch of problems here. I'm going to assume you come from a VB or
VBScript background?
IF @.Test = 1 -- there is no THEN with IF in T-SQL
SET @.AGM1 = ... -- again, need SET or SELECT
ELSE -- this line was okay!
SET @.AGM1 = NULL -- you don't really need to do this though, it is
already NULL!
-- END IF -- there is no such thing as END IF in T-SQL
A more structured way, and my prefered way, if you really needed the ELSE
clause, would be:
IF @.Test = 1
BEGIN
SET @.AGM1 = ...
END
ELSE
BEGIN
SET @.AGM1 = NULL
END
While it makes the code more verbose, I see two benefits:
(1) you really know where the boundaries of the conditional are, unless your
indenting / coding conventions are so bad that a BEGIN/END struct don't
help.
(2) if you need to add more statements to the result of a conditional,
you're already set. I find the lazier syntax leads to unexpected behavior,
not sure if people coming from lisp or cobol expect indentation and white
space to mean more than the code itself, but they can't seem to understand
why the following always prints 'bar' no matter what the value of @.foo:
DECLARE @.foo TINYINT;
SET @.foo = 2;
IF @.foo = 1
PRINT 'foo';
PRINT 'bar'; -- they expect this to run
-- with BEGIN / END it would be much more intuitive
IF @.foo = 2
PRINT 'blat';|||>> I am trying to convert a string value, from a parameter field, into a date to insert int
o a SQL2005 db smalldatetime field [sic] The function returns a bit [sic] value, which I te
st by using the isdate function, and if true [sic] convert this to a smal
ldatetime value, else I set to null. <<
Your whole approach is wrong. Columns are not fields -- nothing alike.
Formatting of data is done in the front end and not in the database.
Good SQL do not write with BIT data since it is too low-level, avoid
BIGINT since it is absurdly large and avoid the proprietary
SMALLDATETIME type. SQL has no BOOLEAN data types. The ISO-11179
Standard prohibits that silly "fn-" affix on name. The use of numbers
in a data element name are a sign of repeating groups and 1NF
violations.
And you are full of syntax and conceptual errors in your effort to
mimic a non-SQL procedural language. Why did you use CONVERT()? To
get a string!! Temporal datatypes do not exist in the 3GL language you
are trying to mimci and you do not understand them. That is too
abstract, so you nee to see a picture.
Why did you use IF-THEN_ELSE constructs? Because you do not understand
CASE expressions. You do not understand declarative programming, so
you avoid it with the proprietary 20+ year old T-SQL 4GL instead.
If you must do this kind of kludging, try something like this:
SET @.agm = CAST (@.adv_met1 AS DATETIME);
Let the CAST() catch the errors then handle them. I also hope you know
about ISO-8601 formats for temporal data.|||It seems like your functions 'should' be doing the test and the conversion a
nd handing you back the date value you desire. Something like this:
CREATE FUNCTION dbo.fn_testdate
( @.StringDateToConvert as varchar(25) )
RETURNS datetime
AS
IF isdate( @.StringDateToConvert )
RETURN convert( datetime, @.StringDateToConvert, 101 )
ELSE
RETURN NULL
GO
This eliminates the entire 'If @.Test' block.
Check in Books on Line for the proper 'Style' value (the 101 above.) Look up
CAST and CONVERT and note the 'Style' indicators.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message news:ABD935E6-40A8-4F7
3-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
to
> a smalldatetime value, else I set to null.
>
> I get error that "Syntax incorrect at @.test".
>
> What is incorrect about what I am trying to do'
>
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
>
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
>
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
>
> Any help is appreciated.|||>> A more structured way, and my prefered way, if you really needed the ELSE
clause, would be: <<
A more **declarative** way and my prefered way, if you really needed
the ELSE
clause, would be:
SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
Why encourage newbies to mimic a 3GL in SQL when you do not have to?|||Thank you
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also a bunch of problems here. I'm going to assume you come from a VB or
> VBScript background?
> IF @.Test = 1 -- there is no THEN with IF in T-SQL
> SET @.AGM1 = ... -- again, need SET or SELECT
> ELSE -- this line was okay!
> SET @.AGM1 = NULL -- you don't really need to do this though, it is
> already NULL!
> -- END IF -- there is no such thing as END IF in T-SQL
> A more structured way, and my prefered way, if you really needed the ELSE
> clause, would be:
> IF @.Test = 1
> BEGIN
> SET @.AGM1 = ...
> END
> ELSE
> BEGIN
> SET @.AGM1 = NULL
> END
> While it makes the code more verbose, I see two benefits:
> (1) you really know where the boundaries of the conditional are, unless yo
ur
> indenting / coding conventions are so bad that a BEGIN/END struct don't
> help.
> (2) if you need to add more statements to the result of a conditional,
> you're already set. I find the lazier syntax leads to unexpected behavior
,
> not sure if people coming from lisp or cobol expect indentation and white
> space to mean more than the code itself, but they can't seem to understand
> why the following always prints 'bar' no matter what the value of @.foo:
> DECLARE @.foo TINYINT;
> SET @.foo = 2;
> IF @.foo = 1
> PRINT 'foo';
> PRINT 'bar'; -- they expect this to run
> -- with BEGIN / END it would be much more intuitive
> IF @.foo = 2
> PRINT 'blat';
>
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151678883.305430.63220@.p79g2000cwp.googlegroups.com...
> clause, would be: <<
> A more **declarative** way and my prefered way, if you really needed
> the ELSE
> clause, would be:
> SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
> Why encourage newbies to mimic a 3GL in SQL when you do not have to?
Because unlike you, I don't expect them to become SQL gurus overnight. :-)
Let's let them get the syntax figured out, then the optimal way to do
things. Much of this they'll find out on their own, because as you and I
both know, the "experts" don't always agree on the best way to do something.
A