A Select And Then An Insert Into Sp

Hi,

I'm trying to use a select statement to retrieve a value and then use this value in an insert. I've written the entire code inside a stored proc. Darn thing refuses to work. Please help
Here is a call to this stored proc
Set_NewUserName 'mm', 'mm', 'student', 'email@em.com', 'mm', 'mm', 'mm', 'add1', 'add2', '600041', 'hybad', '93805', 'city', 'chennai', '13/10/1972', '6'

the code above sends third param 'student' to retrieve UserType_ID from tblUserType table. Also the last parameter is Class ID in the insert statement, for which I am passing the class value which will in turn retrieve the class ID from the tblClass table.

STORED PROCEDURE CODE BELOW HERE

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Set_NewUserName]
-- Following parameters will be sent to this proc by a webpage
-- We need to use the value in UserType_Name to identify if this is a
-- teacher or a student and insert into tblUser the appropriate value
-- similarly for class Id, retrive from class table using the value user
-- selects from drop down
@Login_IDnvarchar(20),@Password nvarchar(30),@UserType_Name nvarchar(30),
@Email nvarchar(30),@FirstName nvarchar(10),@LastName nvarchar(10),
@FullName nvarchar(20),
@add1 nvarchar(10),@add2 nvarchar(10),@pin nvarchar(10),
@city nvarchar(10),@phone nvarchar(10),@HintQuestion nvarchar(MAX),
@HintAnswer nvarchar(50),@DOB nvarchar(10),@Class_Name nvarchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Class_IDuniqueidentifier
Declare @UserType_IDuniqueidentifier

-- retrieve the class id first from the tblClass Table
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
IF NOT EXISTS
(select Class_ID from tblClass where Class_Name = @Class_Name)
RAISERROR('select Class_ID from tblClass where Class_Name',11,1)

BEGIN
ROLLBACK TRANSACTION
RAISERROR('You must provide a valid Class ID',11,1)
END

-- retrieve the usertype id first from the UserType Table
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
IF NOT EXISTS
(select UserType_ID from tblUserType where UserType_Name = 'student')
RAISERROR('select UserType_ID from tblUserType where UserType_Name',11,1)

BEGIN
ROLLBACK TRANSACTION
RAISERROR('You must provide a valid Status ID',11,1)
RETURN
END

-- At last the Insert statements for the procedure
-- is created below here

insert into tblUser
values (
newid(),
@Login_ID,@Password,
@UserType_ID,
@Email,
@FirstName, @LastName, @FullName,
@add1, @add2,@pin, @city,
@phone,
'True',
@HintQuestion,@HintAnswer,
@DOB,
@Class_ID
)
END