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 protected]', '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_ID nvarchar(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_ID uniqueidentifier
Declare @UserType_ID uniqueidentifier

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

You need to assign the value you retrieve from the select to some variable:

   select @Class_ID = Class_ID from tblClass where Class_Name = @Class_Name


Why are you putting selects into transactions? If all you are doing is selecting data, what is there to roll back?

-Peter

Peter,

I was told by someone that if all I wanted was to do an insert that used two variables retrieved using select statements, it could be written far simpler. I was then given the following code, but it fails, and I havent been able to figure it out. Here she is:
-------------------------------------------------------------------
INSERT INTO tblUser (login, password, usertype_id, email, firstname, lastname, fullname, add1, add2, pin, city, phone, booleanField, HintQuestion, HintAnswer, DOB, ClassID)
SELECT @login, @password, ut.usertype_id, @email, @firstname, @lastname, fullname, @add1, @add2, @pin, @city, @phone, 'True', @HintQuestion, @HintAnswer, @DOB, c.Class_ID
INNER JOIN tblClass c on (Class_Name = @Class_Name)
INNER JOIN tblUserType ut on (UserType_Name = 'student')

The error message I get when I use this inside a stored proc is
Msg 156, Level 15, State 1, Procedure Set_NewUserName, Line 38
Incorrect syntax near the keyword 'INNER'.

Missing FROM clause...

--Jeff Moden