|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Function resultsinsert 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. @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); Show quote "sparty1022" <sparty1***@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 Also a bunch of problems here. I'm going to assume you come from a VB or > @AGM1 = convert(varchar(10),@Adv_Met1,101) > else > @AGM1 = null > end if 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'; >> 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? "--CELKO--" <jcelko***@earthlink.net> wrote in message Because unlike you, I don't expect them to become SQL gurus overnight. :-)news:1151678883.305430.63220@p79g2000cwp.googlegroups.com... >>> 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? 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 Thank you
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > 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 into a SQL2005 db smalldatetime field [sic] The function returns a bit [sic] value, which I test by using the isdate function, and if true [sic] convert this to a smalldatetime 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 and 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. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "sparty1022" <sparty1***@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.  |
|||||||||||||||||||||||
Other interesting topics