parameters return null

Question

Hi,
I don't know T-SQL scripts,so I created a new language :)  to write my stored procedure.And it became something like code below.could you tell me what is wrong with this operation and how can I fix it.I have declared 2 variables(I think) but when populating them using my SELECT statements they return null (@ClassNameID,@ClassID) and therefor the whole process not working completely.Again I know SQL but not T-SQL commands.I think the problem is in the syntax of my code.Thank you

ALTER PROCEDURE dbo.InsertStudent
	(
	@StudentFirstName nvarchar(MAX),
	@StudentLastName nvarchar(MAX),
	@StudentNumber nchar(10),
	@SaleTahsili nchar(5),
	@ClassName nvarchar(MAX)
	)
AS

INSERT INTO Student
						 (StudentFirstName,StudentLastName,StudentNumber)
VALUES        (@StudentFirstName,@StudentLastName,@StudentNumber)

DECLARE @ClassNameID smallint
SELECT @ClassNameID FROM dbo.ClassName WHERE ClassName=@ClassName

DECLARE @ClassID smallint
SELECT @ClassID FROM dbo.Class WHERE ClassNameID=@ClassNameID AND SaleTahsili=@SaleTahsili

INSERT INTO StudentClass
						 (StudentNumber, ClassID)
VALUES        (@StudentNumber,@ClassID)	

	SET NOCOUNT OFF
	 
RETURN

Answer 1

You are selecting parameters from the tables not the columns

Please try below

ALTER PROCEDURE dbo.InsertStudent
	(
	@StudentFirstName nvarchar(MAX),
	@StudentLastName nvarchar(MAX),
	@StudentNumber nchar(10),
	@SaleTahsili nchar(5),
	@ClassName nvarchar(MAX)
	)
AS

INSERT INTO Student
						 (StudentFirstName,StudentLastName,StudentNumber)
VALUES        (@StudentFirstName,@StudentLastName,@StudentNumber)


SELECT ClassNameID FROM dbo.ClassName WHERE ClassName=@ClassName


SELECT ClassID FROM dbo.Class WHERE ClassNameID=@ClassNameID AND SaleTahsili=@SaleTahsili

INSERT INTO StudentClass
						 (StudentNumber, ClassID)
VALUES        (@StudentNumber,@ClassID)	

	SET NOCOUNT OFF
	 
RETURN

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Try:

ALTER PROCEDURE dbo.InsertStudent
	(
	@StudentFirstName nvarchar(MAX),
	@StudentLastName nvarchar(MAX),
	@StudentNumber nchar(10),
	@SaleTahsili nchar(5),
	@ClassName nvarchar(MAX)
	)
AS

SET NOCOUNT ON ;
DECLARE @ClassNameID smallint, @ClassID smallint

INSERT INTO Student
						 (StudentFirstName,StudentLastName,StudentNumber)
VALUES        (@StudentFirstName,@StudentLastName,@StudentNumber)


SELECT @ClassNameID = ClassNameID FROM dbo.ClassName WHERE ClassName=@ClassName

SELECT @ClassID = ClassID FROM dbo.Class WHERE ClassNameID=@ClassNameID AND SaleTahsili=@SaleTahsili

INSERT INTO StudentClass
						 (StudentNumber, ClassID)
VALUES  (@StudentNumber,@ClassID)	

	 
RETURN

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Thanks Naomi N.
It worked and I think my problem is solved. But after a successful insertion I got a message that says "No rows affected". However I can see the affected rows in tables.

Answer 4

With SET NOCOUNT ON you should not see such messages. Does your table have a trigger?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Yes I made it ON and it worked.No there is no trigger.