Select problem - 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 2000 Forums

SQL Server Development (2000)

Select problem

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2008-05-09 : 09:55:50

I have a stored procedure shown below. My problem is that the @Town and @Control are returned as blank, but they do contain data.
The field1 in table1 is a varchar(8).

The correct data is returned if field1 in table1 is set to Int(4), but this cannot be done as field1 contains some alphanumeric data like ds435 and if field1 is Int(4) it causes an error converting varchar to int.

Anyone any ideas how to solve this?
macca

SET NOCOUNT ON

DECLARE @TempNumber As Varchar(8)
DECLARE @pluse1_code As Varchar(2)
DECLARE @TempCount As Integer
DECLARE @Townland As Varchar (100)
DECLARE @ControlPoint As Varchar (20)

DECLARE ConnCur CURSOR FOR

SELECT Field0 FROM VIEW_1

OPEN ConnCur

FETCH ConnCur INTO @TempNumber

WHILE @@Fetch_Status = 0

BEGIN

SELECT @Town = Town, @Control = Control
FROM Table1
WHERE field1 = @TempNumber

FETCH ConnCur INTO @TempNumber

END

CLOSE ConnCur
DEALLOCATE ConnCur
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 09:58:47

Do you really need cursor here?Can i ask what your requirement is?

macca
Posting Yak Master

146 Posts

Posted - 2008-05-09 : 10:39:00

I need the cursor because I am doing this for a lot of files.

My problem is that the @Town and @Control are returned as blank, but they actually do contain data. This is happening in the below portion of script. It appears the problem is when @TempNumber is alphanumeric rather than numeric.

SELECT @Town = Town, @Control = Control
FROM Table1
WHERE field1 = @TempNumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 10:46:14

quote:
Originally posted by macca

I need the cursor because I am doing this for a lot of files.

My problem is that the @Town and @Control are returned as blank, but they actually do contain data. This is happening in the below portion of script. It appears the problem is when @TempNumber is alphanumeric rather than numeric.

SELECT @Town = Town, @Control = Control
FROM Table1
WHERE field1 = @TempNumber

even then you could go with set based approach. You didnt specify your requirement yet

macca
Posting Yak Master

146 Posts

Posted - 2008-05-09 : 10:53:36

My reuirement is that when I run the below query, I want @Town and @Control to return the values contained in them rather than returning blanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 11:03:40

quote:
Originally posted by macca

My reuirement is that when I run the below query, I want @Town and @Control to return the values contained in them rather than returning blanks!!!

Nope i was asking your full requirement. The one for which you're using this cursor for.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-12 : 15:00:56

It might be a good idea to provide table structures and sample data as we don't really know what you are trying to do. You could try something like this to prevent non-numeric values from being selected..?

DECLARE @Yak TABLE (Val VARCHAR(8))

INSERT @Yak
SELECT 'A'
UNION ALL SELECT '1234'
UNION ALL SELECT 'foo'
UNION ALL SELECT '98764'
UNION ALL SELECT 'bar'


SELECT *
FROM @Yak
WHERE Val NOT LIKE '[^0-9]%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources