Something is wrong here in this statement

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)

Something is wrong here in this statement

Author

Topic

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-10 : 22:52:41

Hi,

I am trying to insert record in three tables. faculty, address and there is a link table faculty_address. I was able to insert record in faculty and address table but I was not able to insert record in the link Table. I don't get any error either.

Declare @NewID int
Declare @id smallint
DECLARE @Q NVARCHAR(1000)
Declare @adid1 int
Declare @adid2 int

Begin Tran

Declare mycursor cursor static for
Select id from Employee
open mycursor
--
fetch first from mycursor into @ID
--
while @@Fetch_Status = 0
Begin
Exec GetNextId @NewID OUTPUT

set @NewID = (select @NewID)

--
-- /* create new record */
set @Q ='insert into dbo.faculty(facultyid,salutation,lastname,firstname,Middlename,HirestatusID,Rank,Gender,RaceID,ReligionID,Facultyssn,workstatusid,
WorkPhone,PhoneAccount,DepartmentID,MaxLoad,CurrentLoad,Tenure,Insertuserid,Inserttime,Updateuserid,campusid,Facultytypeid,
FacultySuffix,FacultyTitle,Active,ShowOnDirectory,PercentTimeTeaching)Select ' + cast(@NewID as nvarchar)+
','' '',last, first,isnull(MI,''''),0,'' '','' '',0,0,SSN,0,'' '','' '',isnull(g.uniqueid,0),0,0,''No'',''user'',getdate(),'' '',
0,isnull(g1.uniqueid,0),'' '','' '',1,0,0 from Employee a left join dbo.gloss g
on a.dept = g.text and category =6 left join dbo.gloss g1 on
a.Role = g1.text and g1.category = 20
where id ='+cast(@id as nvarchar)
Print @Q
EXEC sp_executesql @Q

IF @@ERROR <> 0
BEGIN
GOTO on_error
END

--Create new address record
Declare @SQLAd1 nvarchar(1000)
set @SQLAd1 = 'INSERT INTO dbo.Add (AddressTypeID,Address1,Address2,Address3,City,County,
StateID,CountryID,ZipCode,Phone1,Phone2,WorkPhone1,Fax1,Email1,Email2,Email3,ActiveFlag,ResponsibleParty,
PhoneOfficeCode,PhoneExtension,BoxNumber,InsertUserID,InsertTime,UpdateUserID,MobilePhone,InternationalFlag)
Select 287,address,'' '','' '',city,'' '',isnull(g2.uniqueid,0),0,zip,'' '','' '','' '','' '','' '','' '','' '',''Yes'','' '','' '',
'''','''',''user'',getdate(),'''','''',''No'' from Employee a
left join dbo.gloss g2 on
a.state = g2.displaytext and g2.category = 10
where a.id ='+cast(@id as nvarchar)

exec sp_executesql @SQLAd1

IF @@ERROR <> 0
BEGIN
GOTO on_error
END

Select @adid1 = SCOPE_IDENTITY()

DECLARE @SQL NVARCHAR(1000)

set @SQL = 'INSERT INTO Faculty_ADD (FacultyID,AddressID)values(' + cast(@NewID as nvarchar)+ ', ' + cast(@adid1 as nvarchar)+')'

print @SQL

exec sp_executesql @SQL

IF @@ERROR <> 0
BEGIN
GOTO on_error
END

fetch next from mycursor into @ID
--
end
--
CLOSE mycursor
DEALLOCATE mycursor

COMMIT TRANSACTION

End

on_error:
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
CLOSE mycursor
DEALLOCATE mycursor
Return

END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-10 : 23:02:32

there is a mismatch of BEGIN with END. You have an extra END in there

KH
[spoiler]Time is always against us[/spoiler]

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-10 : 23:17:16

I think there was one end for begin trans and one end for while loop.
If there is an error it should come out of the loop so I put an extra one there.

Which one should I remove? The third insert does not work at all.
Thanks for help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-10 : 23:23:32

begin trans does not required corresponding END

You use BEGIN TRANS with COMMIT or ROLLBACK

KH
[spoiler]Time is always against us[/spoiler]

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-10 : 23:38:05

I didn't have it earlier but I was getting error "The cursor is already open." I got that error after code inserted all the records in first two tables. That's why I put it there.

Even that time also there was no success in importing record in the third table. I am doing something wrong there too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 23:44:12

that may be because you didnt close cursor somewhere. now it looks ok though.

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-11 : 00:05:17

fetch next from mycursor into @ID
--
end
--
CLOSE mycursor
DEALLOCATE mycursor

COMMIT TRANSACTION

End

on_error:
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
CLOSE mycursor
DEALLOCATE mycursor
Return

END

It was already there. I added the second end because of that. And I will remove it as per Khtan's suggestion.
But the code for third import does not work at all. How do I troubleshoot?
I put the print statement

Select @adid1 = SCOPE_IDENTITY()

DECLARE @SQL NVARCHAR(1000)

set @SQL = 'INSERT INTO Faculty_ADD (FacultyID,AddressID)values(' + cast(@NewID as nvarchar)+ ', ' + cast(@adid1 as nvarchar)+')'

print @SQL

But it does not print this statement and neither does it give me any error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 00:11:45

always specify a length when you cast to varchar types
cast(@NewID as nvarchar(length))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 00:12:13

print out the value of @NewID and @adid1 before the set @SQL = 'INSERT . . ' and verify it.

KH
[spoiler]Time is always against us[/spoiler]

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-11 : 00:36:09

@newID is fine
@adid1 did not return any value.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 00:38:36

check your insert statement for table Add

KH
[spoiler]Time is always against us[/spoiler]

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-11 : 01:33:46

Insert is working fine for table add. There is an identity column in that table addressid.
I can see data is right in that table.
what am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 03:02:15

Still the same error?

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-11 : 08:45:48

yes, I tried on different database and now the transaction rolled back also.

Please help me correct this statement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 09:41:17

change this line to @@IDENTITTY. SCOPE_IDENTITY() is out of scope

Select @adid1 = ~~SCOPE_IDENTITY()~~ @@IDENTITTY

KH
[spoiler]Time is always against us[/spoiler]

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-11 : 10:54:33

We have 100 users in the database. I am confused with @@identity.
I read it some where that it is safer to use SCOPE_IDENTITY(), but maybe it won't work here.

Another question: Do you think something is wrong here in this part of statement? It didn't give me error but the whole transaction rolled back.

end
--
CLOSE mycursor
DEALLOCATE mycursor

COMMIT TRANSACTION

on_error:
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
CLOSE mycursor
DEALLOCATE mycursor
Return

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:56:56

Why are you using dynamic SQL in the cursor loop?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 11:14:01

Get it all done without cursors!

-- Prepare staging
declare @f table (facultyid int, empid int)
declare @a table (AddressID int, empid int)

-- Create all Faculty records
INSERT		dbo.Faculty
		(
			salutation,
			lastname,
			firstname,
			Middlename,
			HirestatusID,
			Rank,
			Gender,
			RaceID,
			ReligionID,
			Facultyssn,
			workstatusid,
			WorkPhone,
			PhoneAccount,
			DepartmentID,
			MaxLoad,
			CurrentLoad,
			Tenure,
			Insertuserid,
			Inserttime,
			Updateuserid,
			campusid,
			Facultytypeid,
			FacultySuffix,
			FacultyTitle,
			Active,
			ShowOnDirectory,
			PercentTimeTeaching
		)
output		inserted.facultyid,
		inserted.id
into		@f
SELECT		' ',
		last,
		first,
		isnull(MI, ''),
		0,
		' ',
		' ',
		0,
		0,
		SSN,
		0,
		' ',
		' ',
		isnull(g.uniqueid, 0),
		0,
		0,
		'No',
		'user',
		getdate(),
		' ',
		0,
		isnull(g1.uniqueid, 0),
		' ',
		' ',
		1,
		0,
		0
from		Employee AS a
left join	dbo.gloss AS g on g.text = a.dept
			and category = 6
left join	dbo.gloss AS g1 on g1.text = a.Role
			and g1.category = 20


-- Create all Address records
INSERT		dbo.Add
		(
			AddressTypeID,
			Address1,
			Address2,
			Address3,
			City,
			County,
			StateID,
			CountryID,
			ZipCode,
			Phone1,
			Phone2,
			WorkPhone1,
			Fax1,
			Email1,
			Email2,
			Email3,
			ActiveFlag,
			ResponsibleParty,
			PhoneOfficeCode,
			PhoneExtension,
			BoxNumber,
			InsertUserID,
			InsertTime,
			UpdateUserID,
			MobilePhone,
			InternationalFlag
		)
output		inserted.AddressID,
		inserted.id
into		@a
Select		287,
		address,
		' ',
		' ',
		city,
		' ',
		isnull(g2.uniqueid, 0),
		0,
		zip,
		' ',
		' ',
		' ',
		' ',
		' ',
		' ',
		' ',
		'Yes',
		' ',
		' ',
		'',
		'',
		'user',
		getdate(),
		'',
		'',
		'No'
from		Employee as a
left join	dbo.gloss as g2 on g2.displaytext = a.state
			and g2.category = 10

-- Create all link records
INSERT		Faculty_ADD
		(
			FacultyID,
			AddressID
		)
SELECT		f.facultyid
from		@f as f
inner join	@a as a on a.empid = f.empid

E 12°55'05.25"
N 56°04'39.16"

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-11 : 11:47:45

I never wanted to do that. But we have to create new system ID for each record for faculty table.

Exec GetNextId @NewID OUTPUT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 11:51:03

You wrote Faculty table had an IDENTITY column named FacultyID?
If Faculty table doesn't have an IDENTITY column, you should make the column an IDENTITY.

You wrote Add table had an IDENTITY column named AddressID?

Then why use CURSOR in your code, when three simple INSERT statements will do your work faster than anything?

E 12°55'05.25"
N 56°04'39.16"

tanu
Yak Posting Veteran

57 Posts

Posted - 2008-08-11 : 11:57:49

"You wrote Faculty table had an IDENTITY column named FacultyID?
If Faculty table doesn't have an IDENTITY column, you should make the column an IDENTITY"

No FacultyID is not an identity column. It is system wide id and I can't change faculty table because we bought this software.

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources