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)
 Something is wrong here in this statement
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

tanu
Yak Posting Veteran

57 Posts

Posted - 08/10/2008 :  22:52:41  Show Profile  Reply with Quote
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)

Singapore
17429 Posts

Posted - 08/10/2008 :  23:02:32  Show Profile  Reply with Quote
there is a mismatch of BEGIN with END. You have an extra END in there


KH
Time is always against us

Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/10/2008 :  23:17:16  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17429 Posts

Posted - 08/10/2008 :  23:23:32  Show Profile  Reply with Quote
begin trans does not required corresponding END

You use BEGIN TRANS with COMMIT or ROLLBACK


KH
Time is always against us

Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/10/2008 :  23:38:05  Show Profile  Reply with Quote
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.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/10/2008 :  23:44:12  Show Profile  Reply with Quote
that may be because you didnt close cursor somewhere. now it looks ok though.
Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/11/2008 :  00:05:17  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/11/2008 :  00:11:45  Show Profile  Reply with Quote
always specify a length when you cast to varchar types
cast(@NewID as nvarchar(length))
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17429 Posts

Posted - 08/11/2008 :  00:12:13  Show Profile  Reply with Quote
print out the value of @NewID and @adid1 before the set @SQL = 'INSERT . . ' and verify it.


KH
Time is always against us

Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/11/2008 :  00:36:09  Show Profile  Reply with Quote

@newID is fine
@adid1 did not return any value.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17429 Posts

Posted - 08/11/2008 :  00:38:36  Show Profile  Reply with Quote
check your insert statement for table Add


KH
Time is always against us

Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/11/2008 :  01:33:46  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/11/2008 :  03:02:15  Show Profile  Reply with Quote
Still the same error?
Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/11/2008 :  08:45:48  Show Profile  Reply with Quote
yes, I tried on different database and now the transaction rolled back also.

Please help me correct this statement.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17429 Posts

Posted - 08/11/2008 :  09:41:17  Show Profile  Reply with Quote
change this line to @@IDENTITTY. SCOPE_IDENTITY() is out of scope

Select @adid1 = SCOPE_IDENTITY() @@IDENTITTY




KH
Time is always against us

Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/11/2008 :  10:54:33  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/11/2008 :  10:56:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why are you using dynamic SQL in the cursor loop?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/11/2008 :  11:14:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 08/11/2008 11:15:26
Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/11/2008 :  11:47:45  Show Profile  Reply with Quote
I never wanted to do that. But we have to create new system ID for each record for faculty table.

Exec GetNextId @NewID OUTPUT
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 08/11/2008 :  11:51:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 08/11/2008 11:53:12
Go to Top of Page

tanu
Yak Posting Veteran

57 Posts

Posted - 08/11/2008 :  11:57:49  Show Profile  Reply with Quote
"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.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.12 seconds. Powered By: Snitz Forums 2000