locked
parameters return null RRS feed

  • 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


    Sunday, May 6, 2012 10:13 PM

Answers

  • 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

    • Proposed as answer by Eshani Rao Tuesday, May 8, 2012 4:28 AM
    • Marked as answer by mortaza.mkr Wednesday, May 9, 2012 5:09 PM
    Monday, May 7, 2012 1:37 AM

All replies

  • 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

    Monday, May 7, 2012 12:42 AM
  • 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

    • Proposed as answer by Eshani Rao Tuesday, May 8, 2012 4:28 AM
    • Marked as answer by mortaza.mkr Wednesday, May 9, 2012 5:09 PM
    Monday, May 7, 2012 1:37 AM
  • 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.
    Wednesday, May 9, 2012 4:16 PM
  • 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

    Wednesday, May 9, 2012 4:37 PM
  • Yes I made it ON and it worked.No there is no trigger.
    Wednesday, May 9, 2012 5:08 PM