SQL Assign value to local variable take from table

Question

This i my procedure,that determines classId and liveareaId for Animal to insert it into table

CREATE PROCEDURE insertAnimal
@name nvarchar,
@birthyear int,
@classname nvarchar,
@livearea nvarchar
AS
BEGIN
DECLARE @classid int
DECLARE @liveareaid int

SET @classid =  

(SELECT Id
 FROM dbo.Class 
 WHERE dbo.Class.Name = @classname)

SET @liveareaid =
(SELECT Id
 FROM dbo.LiveArea 
 WHERE Name = @livearea)

INSERT INTO dbo.Animal (Name,BirthYear,ClassId,LiveAreaId) VALUES
(
    @name,
    @birthyear,
    @classid,
    @liveareaid
)
END
GO

I have a error:

Cannot insert the value NULL into column 'ClassId', table 'ZOO.dbo.Animal'; column does not allow nulls. INSERT fails.

Why ClassId is null, can you tell me why whis doesn't work.

SET @classid =  

(SELECT Id
 FROM dbo.Class 
 WHERE dbo.Class.Name=@classname)

Answer 1

This is because you have declared @classname as only nvarchar and have not specified a length. When length is not specified in a nvarchar variable declaration statement, the default length is 1.

Declare as:

CREATE PROCEDURE insertAnimal
@name nvarchar(10),
@birthyear int,
@classname nvarchar(10),
@livearea nvarchar(10)
...