| Author |
 Topic  |
|
|
SQLWhippingBoy
Starting Member
Canada
3 Posts |
 Posted - 02/24/2013 : 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 UnversityStudent
RETURN 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!
|
Edited by - SQLWhippingBoy on 02/24/2013 08:54:36
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 02/24/2013 : 16:29:36
|
I think you're on the right track here.
CREATE FUNCTION fn_GenerateUserID
(@StudentFirstName NVARCHAR,
@StudentLastName NVARCHAR)
RETURNS NVARCHAR
BEGIN
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 Num
FROM UniversityStudent
GROUP BY LOWER(LEFT(StudentFirstName,1)) as Fname
,LOWER(SstudentLastName) as LName
) t1
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
SQLWhippingBoy
Starting Member
Canada
3 Posts |
Posted - 02/24/2013 : 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
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 02/24/2013 : 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.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
SQLWhippingBoy
Starting Member
Canada
3 Posts |
Posted - 02/24/2013 : 18:21:26
|
| Oh yeah, totally understand. And it's much appreciated. I just didn't know how else to proceed with it. |
|
|
| |
Topic |
|
A hello + scalar functions
