x

Stored Procedure - Where to start??

Hi, I've been given my first task to create a stored procedure that creates a unique id number of an employee. The id number will be created from the first three letters of the employees last name, the first letter of the first name, and a sequential three digit number starting with zero. This stored procedure will be embeded in another stored procedure that will be inserting an employee into a table. Honestly, I am determined to get this, but I am looking for a little direction on were to start. I have this so far:

 @firstname,
 @lastname
 
 Declare
 @uniqueid varchar(100);
  SET @uniqueid = @firstname// strip the firtst letter away '+' @lastname//get the first three letters '+' //plus some sql fuction
 //take the result and insert the employee into my table with @uniqueid being the employid.
more ▼

asked Apr 10, 2013 at 02:19 PM in Default

avatar image

marka92
210 8 11 15

What if you have someone with a two-letter surname?

Apr 10, 2013 at 02:32 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

That is true, I didnt think of that. So far I havr this;REATE PROCEDURE [dbo].[prcCreateClientcode] ( @firstname_vc varchar(15), @lastname_vc varchar(30) )

AS BEGIN SET NOCOUNT OFF Declare @uniqueid varchar(100)

Set @uniqueid = SUBSTRING(@lastname_vc, 1,3) + SUBSTRING(@firstname_vc,1,1)

SELECT @uniqueid //now I need to get a sequential number starting with 000 and append it to my letters. Thank you for bringing up the fact about two letter sir names?

more ▼

answered Apr 10, 2013 at 03:30 PM

avatar image

marka92
210 8 11 15

I also need to check my table to ensure that there are no duplicates, correct? For example; Two employees named- "Mark Simmons" and "Mike Simmins". This is a challenge for a new gut like me.

Apr 10, 2013 at 06:17 PM marka92
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x479

asked: Apr 10, 2013 at 02:19 PM

Seen: 731 times

Last Updated: Apr 10, 2013 at 06:17 PM

Copyright 2018 Redgate Software. Privacy Policy