SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 declare more than one value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DLTaylor
Posting Yak Master

United Kingdom
136 Posts

Posted - 10/17/2012 :  05:14:05  Show Profile  Reply with Quote
Hi All,

I have an TSQL question that I can’t get my head around & nobody at work uses TSQL – so I’m hoping for some forum help ...again ;-)

I have a table (called #testdata is example below) it contains values that should be turned into dates. However users can enter all sorts into the field (don’t ask me how – its how the data arrived in my database) in the example a value of ‘a’ is in the field.

Im creating a series of T-SQL statements to capture the DQ issues and send to a holding table. Now if I fully code each T-SQL statement I can write a usp that does this…but I was hoping to go one better and declare some vales so as I could control the usp & save lots of copy & pasting…

In the example below that I thought might work I get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

In the simplified example below I have recreated the error – can you advise how I can modify the code to return rogue values?

Any help is always credited to the forum in my code.
Thanks

CREATE TABLE #testdata
(
data nvarchar(20) NULL,
)
Insert into #testdata
select '20121017'UNION all
select '20121015'UNION all
select '20121016'UNION all
select 'zxdf'UNION all
select 'a'


DECLARE @DQ AS NVARCHAR(50)
SET @DQ = (SELECT data FROM [#testdata])


SELECT @DQ
FROM #testdata
WHERE ISNUMERIC(@DQ) = 0

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 10/17/2012 :  05:52:04  Show Profile  Reply with Quote
DECLARE @DQ AS NVARCHAR(50)
SET @DQ = (SELECT data FROM [#testdata]) -- Here You are getting error.

You can check for non-numeric values of the field 'Data'
SELECT data
FROM #testdata
WHERE ISNUMERIC(Data) = 0



See the following example:
declare @test table(strings varchar(50))
Insert into @test
Select '12d3' union all
Select '87234.45' union all
Select '$123,456.00' union all
Select ' 12 ' union all
Select char(10) union all
select '20121017'UNION all
select '20121015'UNION all
select '20121016'UNION all
select 'zxdf'UNION all
select 'a'

select strings,isnumeric(strings)as valid from @test



--
Chandu
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 10/17/2012 :  12:43:16  Show Profile  Reply with Quote
You don't happen to be able to use SQL 2012 do you? If so there are some new parse functions that you could use to make life simpler. If not then you could use the LEN function in combination with a LIKE clause to get the values you want. You might also be able to use the ISDATE function. But, the ISNUMERIC function probably will not work. Here is an example of the differences between ISNUMERIC and a numeric LIKE predicate:
DECLARE @Foo TABLE (Val VARCHAR(50))

INSERT @Foo (Val)
VALUES
('1')
,('1,000')
,('5e3')
,('100')
,('100.00')
,('9781297')
,('9781e297')
,('978w1297')
,('asdfg')
,('.9781297')
,('12d3')
,('$123,456.00')
,(' 12 ')
,(char(10))
,('$')
,(NULL)

SELECT 
    Val
    ,ISNUMERIC(Val) AS IsValNumeric
    ,CASE WHEN Val NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END AS IsInt
FROM @Foo

FOr your specific code, here is something that might work. However, you may still run into issues with when SQL applies teh conversion versus when it applies teh predicate:
DECLARE  @testdata TABLE
(
data nvarchar(20) NULL
)
Insert into @testdata
select '20121017'UNION all
select '20121015'UNION all
select '20121016'UNION all
select 'zxdf'UNION all
select 'a'


SELECT data
FROM @testdata
WHERE LEN(data) <> 8
OR data LIKE '%[^0-9]%'

-- OR

SELECT data
FROM @testdata
WHERE ISDATE(data) = 0




SELECT CAST(data AS DATETIME)
FROM @testdata
WHERE LEN(data) = 8
AND data NOT LIKE '%[^0-9]%'

-- Or

SELECT CAST(data AS DATETIME)
FROM @testdata
WHERE ISDATE(data) = 1
EDIT: I was showing how to get only the dates, changed to show invalid date values

Edited by - Lamprey on 10/17/2012 12:47:20
Go to Top of Page

DLTaylor
Posting Yak Master

United Kingdom
136 Posts

Posted - 10/18/2012 :  10:14:20  Show Profile  Reply with Quote
Sorry for my delay!
Thanks Bandi & Lamprey for your replies – its great to get expert advice.
I have used ideas from both to improve my code.

I sure can’t wait to start using SQL 2012… although the licencing is causing a bit of pricing issue – but I guess that’s for another discussion ;-)

Thanks again.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 10/30/2012 :  08:43:31  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000