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
 General SQL Server Forums
 New to SQL Server Programming
 A hello + scalar functions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLWhippingBoy
Starting Member

Canada
3 Posts

Posted - 02/24/2013 :  08:44:14  Show Profile  Reply with Quote
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
2870 Posts

Posted - 02/24/2013 :  16:29:36  Show Profile  Reply with Quote
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

Canada
3 Posts

Posted - 02/24/2013 :  16:37:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 02/24/2013 :  17:56:27  Show Profile  Reply with Quote
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

Canada
3 Posts

Posted - 02/24/2013 :  18:21:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 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.08 seconds. Powered By: Snitz Forums 2000