TSQL 1

[ProgrammingB52]

I have a table Student with fields stdID,fName,lName,email and username. I have another table studentType with a field degType for degreeType. The username field is blank and I am trying to write in TSQL that the username field be the (degreeType initial + _ + fName + lName). For example for a graduate student named Sally Jones, username = g_sallyjones.

My problem is not with the string manipulation that works. My problem is I can't put the @username field (the field with the correct string such as g_sallyjones) into the username field of the Student table. Below is my code.Please help. I have actually been struggling for 2 days on this.

drop procedure prSetUsername
go
create procedure prSetUsername
@username varchar(25)=NULL
as
select @username = (substring(degType,1,1) + '_ ' + fName +lName)
from student,StudentType
where student.stdID=StudentType.stdID
insert into student (username) Values (@username)
GO
execute prsetUsername
GO
select * from student

 

[jbenson001]

What is the outupt.. is there an error?

 

[genomon]

select @username = (substring(degType,1,1) + '_ ' + fName +lName)

**What is going on in the following line of code?
You can only have one table in the FROM statement unless you join the tables......


from student,StudentType
where student.stdID=StudentType.stdID


The Microsoft mascot is a butterfly.
A butterfly is a bug.
Think about it....

 

[amrita418]

I am not sure why you need a procedure to do this?

If all you want to update the username field
Try this:

update     a
set        a.username = substring(b.degType,1,1) + '_ ' + a.fName + a.lName)
from       student a, 
           StudentType b
where      a.stdID = b.stdID
           and a.username is null

Regards,
AA

 

[ProgrammingB52]

The output is:Cannot insert the value NULL into column 'stdID', table 'clarkOnline.dbo.Student'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(9 row(s) affected)
----------------------------------------------------------
When I write this:
select student.stdID,fName,lName,email,username,(substring(degType,1,1) + '_ ' + fName +lName) AS username
from student,StudentType
where student.stdID=StudentType.stdID

The output is:
stdID fName lName username username
123456789 Harry Jones NULL G_ HarryJones
243667777 Ted James NULL G_ TedJames
245547749 Kate Alberts NULL N_ KateAlberts
245674553 George Parks NULL G_ GeorgeParks
435435348 Jane Franks NULL N_ JaneFranks
449604376 Derek Smith NULL G_ DerekSmith
546546644 John Reed NULL G_ JohnReed
565863453 Ron Johnson NULL N_ RonJohnson
568895344 Susan Belmont NULL N_ SusanBelmont


The problem with this is that a separate username field is displayed as a result of the query and doesn't update the table. The username field that I want updated to the table stays NULL.

 

[SQLBill]

@username varchar(25)=NULL

Do you have any fname, lname combinations longer than 23 characters? ( 1 char is for the underline '_' and one for the degree, which only leaves 23 for the concatenated name).

-SQLBill

Posting advice: FAQ481-4875

 

[ProgrammingB52]

Thanks so very much, The update table sql worked. I made it more complicated than it really was.