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
 General SQL Server Forums
 New to SQL Server Programming
 A hello + scalar functions

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 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!


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 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
Go to Top of Page

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.
Go to Top of Page

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.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -