declare more than one value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

declare more than one value

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-10-17 : 05:14:05

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-17 : 05:52:04

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-17 : 12:43:16

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

DLTaylor
Posting Yak Master

136 Posts

Posted - 2012-10-18 : 10:14:20

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 08:43:31

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources