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.
Author |
Topic |
SQLWhippingBoy
Starting Member
3 Posts |
Posted - 2013-02-24 : 08:44:14
|
Hi all, I'm new to SQL coding and programming in general, but I'm enjoying it so far, for the most part...other then what we're doing now with our database programs in class. I'm trying to work on scalar functions, and I sort of understand it(I was out with the flu the week we learned about them), but right now one particular example question is kicking my ass. I'm kind of hoping for a bit of help here(I know your policy on homework questions but I'm at a loss): I have to create a Scalar function named fn_GenerateUserID that's supposed to generate a unique username for new students in our database table - it takes the input parameters for FirstName and LastName and returns a lower case VARCHAR userID which is the first letter of the first name concatenated to the last name and then a digit or more depending on the number of similar names in the table. If you could give me a hand it would be GREATLY appreciated. I apologize in advance if this is a stupid question. Here is the small amount of code that is most likely wrong, that I have so far. I'm just really stumped: CREATE FUNCTION fn_GenerateUserID(@StudentFirstName NVARCHAR, @StudentLastName NVARCHAR)RETURNS NVARCHAR BEGIN SELECT COUNT(StudentFirstName, StudentLastName) FROM UniversityStudent SELECT LOWER(StudentFirstName + ' ' + StudentLastName) FROM UnversityStudentRETURN StudentUserID END EDIT: I know I'm probably hopeless with this, haha, but I am trying..not just going to come here and copy paste my questions to you like some idiots do! |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-24 : 16:29:36
|
I think you're on the right track here.CREATE FUNCTION fn_GenerateUserID(@StudentFirstName NVARCHAR,@StudentLastName NVARCHAR)RETURNS NVARCHARBEGIN RETURN SELECT FName + Lanme + RIGHT('000'+convert(varchar(3),ISNULL(Num,0), 3)FROM(SELECT LOWER(LEFT(StudentFirstName,1)) as Fname ,LOWER(SstudentLastName) as LName + COUNT(*) as NumFROM UniversityStudentGROUP BY LOWER(LEFT(StudentFirstName,1)) as Fname ,LOWER(SstudentLastName) as LName ) t1JimEveryday I learn something that somebody else already knew |
|
|
SQLWhippingBoy
Starting Member
3 Posts |
Posted - 2013-02-24 : 16:37:39
|
Thank you VERY much, Jim. I've been plugging away at the other things(stored procedures, transactions, etc) and got through them with minimal problems, but this one has been bugging me all day! Thank you again. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-24 : 17:56:27
|
Glad I could help. We don't do homework here, but when you do most of the heavy lifting, we're glad to get you the rest of the way. Just make sure you understand the answer and realize that there are probably better ways to do this.JimEveryday I learn something that somebody else already knew |
|
|
SQLWhippingBoy
Starting Member
3 Posts |
Posted - 2013-02-24 : 18:21:26
|
Oh yeah, totally understand. And it's much appreciated. I just didn't know how else to proceed with it. |
|
|
|
|
|
|
|