is cursor cannot process null values??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

is cursor cannot process null values???

Author

Topic

jeremy96
Starting Member

1 Post

Posted - 2011-03-29 : 03:59:29

Dear All,
is it true that cursor cannot process null values?
i've this syntax

CREATE TABLE tb_coba
( @nama varchar(20),
@umur int,
@keterangan varchar(50),
@kode int)

CREATE TABLE tb_master_almt
(@toko varchar(20),
@alamat varchar(20)
@kode int)
go
insert into tb_coba values ('jery',22,'biasa',1)
insert into tb_coba values ('jhon',23,'extra',2)
insert into tb_coba values ('joe',24,'biasa',3)
insert into tb_master_almt values ('adira','bandung',1)
insert into tb_master_almt values ('supra','jakarta',2)
go
declare @kode numeric, @nama varchar(200), @umur numeric, @val1 numeric, @val2 varchar(200), @val3 varchar(200), @message varchar(200)
declare cursor_tb_coba1 cursor for
select kode, nama, umur from tb_coba
OPEN cursor_tb_coba1
FETCH NEXT FROM cursor_tb_coba1
INTO @kode, @nama, @umur
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Nama : ' + @nama
PRINT @message
DECLARE cursor_master CURSOR FOR
SELECT kode, toko, alamat
FROM tb_master_almt
WHERE kode = @kode -- Variable value from the outer cursor

OPEN cursor_master
FETCH NEXT FROM cursor_master INTO @val1, @val2, @val3
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'Alamat : ' + @val2 + ' ' + @val3
PRINT @message
FETCH NEXT FROM cursor_master INTO @val1, @val2, @val3
END
CLOSE cursor_master
DEALLOCATE cursor_master
FETCH NEXT FROM cursor_tb_coba1
INTO @kode, @nama, @umur
END
CLOSE cursor_tb_coba1
DEALLOCATE cursor_tb_coba1
when i try to run it, it works, but all row where @umur = null, it doesn't come out. please correct my syntax if i am wrong.

thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-03-29 : 06:52:28

Formatting


CREATE TABLE tb_coba
( @nama varchar(20),
 @umur int,
 @keterangan varchar(50),
 @kode int)

CREATE TABLE tb_master_almt
(@toko varchar(20),
@alamat varchar(20)
@kode int)
go
insert into tb_coba values ('jery',22,'biasa',1)
insert into tb_coba values ('jhon',23,'extra',2)
insert into tb_coba values ('joe',24,'biasa',3)
insert into tb_master_almt values ('adira','bandung',1)
insert into tb_master_almt values ('supra','jakarta',2)
go
declare @kode numeric, @nama varchar(200), @umur numeric, @val1 numeric, @val2 varchar(200), @val3 varchar(200), @message varchar(200)
declare cursor_tb_coba1 cursor for
	 select kode, nama, umur from tb_coba
OPEN cursor_tb_coba1
FETCH NEXT FROM cursor_tb_coba1 
	INTO @kode, @nama, @umur
WHILE @@FETCH_STATUS = 0
	BEGIN
	PRINT ' '
	SELECT @message = '----- Nama : ' + 	@nama
	PRINT @message
	DECLARE cursor_master CURSOR FOR 
		SELECT kode, toko, alamat
		FROM tb_master_almt 
		WHERE kode = @kode -- Variable value from the outer cursor

	OPEN cursor_master
	FETCH NEXT FROM cursor_master INTO @val1, @val2, @val3
	IF @@FETCH_STATUS <> 0 
		PRINT ' <>' 
	WHILE @@FETCH_STATUS = 0
		BEGIN
		SELECT @message = 'Alamat : ' + @val2 + ' ' + @val3
		PRINT @message
		FETCH NEXT FROM cursor_master INTO @val1, @val2, @val3
		END
	CLOSE cursor_master
	DEALLOCATE cursor_master
	FETCH NEXT FROM cursor_tb_coba1 
	INTO @kode, @nama, @umur
END 
CLOSE cursor_tb_coba1
DEALLOCATE cursor_tb_coba1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-03-29 : 06:55:19

I don't know what you are trying to do but the actual problem you are expressing is because you can't compare nulls that way.

Consider:




DECLARE @foo INT
DECLARE @bar INT

SET @foo = NULL
SET @bar = NULL

-- Neither of these will work
IF @foo = @bar PRINT 'yo!'
IF @foo != @bar PRINT 'no!'

-- This will
IF @foo IS NULL AND @bar IS NULL PRINT 'NULL'

You cannot compare NULLS. All you can do is test for NULL. Read this:
http://en.wikipedia.org/wiki/Three-state_logic

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources