locked
unable to create stored procedure RRS feed

  • Question

  • dear all,

    I was using the below stored procedure in my environment.

    USE [Contact]
    GO
    /****** Object:  StoredProcedure [dbo].[spInsertRegisterNewUser]    Script Date: 01/01/2016 05:18:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Sathya
    -- Create date: <Create Date,10th August 2015,>
    -- Description:	<Description, For EMPID - KGFS,>
    -- =============================================
    ALTER PROCEDURE  [dbo].[spInsertRegisterNewUser]
    	-- Add the parameters for the stored procedure here
    	--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
    	@EmpID nvarchar(50),
    	@FirstName nvarchar(50),
    	@LastName nvarchar(50),
    	@LoginName nvarchar(50),
    	@Password nvarchar(200),
    	@Email nvarchar(200)
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	--SET NOCOUNT ON;
    	Declare @count int
    	Declare @ReturnCode int
    	
    	
    	Select @count = COUNT(LoginName)
    	from EmpTable where EmpID = @EmpID
    	If @count >0
    	Begin
    	
    	 Set @ReturnCode = -1
    	 
    	 End
    	 Else
    	 Begin
    	 
    	  Set @ReturnCode = 1
    	  Insert into EmpTable(EmpID,FirstName,LastName,LoginName,[Password],Email)  values
    	  (@EmpID,@FirstName,@LastName,@LoginName,@Password,@Email)
    	  End
    	  Select @ReturnCode as ReturnValue
    	
    
        -- Insert statements for procedure here
    	--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    END
    

    Now I want to use and create another stored procedure for Employee registration

    with few additional fields - when I do so I am getting Incorrect syntax near '@Password'. Expecting  INTEGER

    and this error Incorrect syntax near 200  expecting SELECT, or '(' - I am pasting the modified for your reference. Any inputs to correct this issue are welcome.

    USE [Contact]
    GO
    /****** Object:  StoredProcedure [dbo].[spInsertRegisterNewUser]    Script Date: 01/01/2016 05:18:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Sathya
    -- Create date: <Create Date,10th August 2015,>
    -- Description:	<Description, For EMPID - KGFS,>
    -- =============================================
    CREATE PROCEDURE  [dbo].[spInsertRegisterNewEmployee]
    	-- Add the parameters for the stored procedure here
    	--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
    	(@EmpID nvarchar(50),
    	@FirstName nvarchar(50),
    	@LastName nvarchar(50),
    	@EmpFullName nvarchar(50),
    	@LoginName nvarchar(50),
    	@Gender nvarchar(50),
    	@Location nvarchar(50,
    	@Password nvarchar(200),
    	@Email nvarchar(200),
    	@Dob date,
    	@Marital nvarchar(50),
    	@Doj date)
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	--SET NOCOUNT ON;
    	Declare @count int
    	Declare @ReturnCode int
    	
    	
    	Select @count = COUNT(EmpID)
    	from EmpTable where EmpID = @EmpID
    	If @count >0
    	Begin
    	
    	 Set @ReturnCode = -1
    	 
    	 End
    	 Else
    	 Begin
    	 
    	  Set @ReturnCode = 1
    	  Insert into EmpTable(EmpID,FirstName,LastName,EmpFullName,LoginName,Gender,Location,[Password],Email,Dob,Marital,Doj)  values
    	  (@EmpID,@FirstName,@LastName,@EmpFullName,@LoginName,@Gender,@Location,@Password,@Email,@Dob,@Marital,@Doj)
    	  End
    	  Select @ReturnCode as ReturnValue
    	
    
        -- Insert statements for procedure here
    	--SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    END
    

    Warm Regards,

    Sathya

    Friday, January 1, 2016 11:38 AM

Answers

  • CREATE PROCEDURE  [dbo].[spInsertRegisterNewEmployee]
    -- Add the parameters for the stored procedure here
    --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
    (@EmpID nvarchar(50),
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @EmpFullName nvarchar(50),
    @LoginName nvarchar(50),
    @Gender nvarchar(50),
    @Location nvarchar(50),
    @Password nvarchar(200),
    @Email nvarchar(200),
    @Dobdate date,
    @Martial nvarchar(50),
    @Doj date)

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --SET NOCOUNT ON;
    Declare @count int
    Declare @ReturnCode int


    Select @count = COUNT(EmpID)
    from EmpTable where EmpID = @EmpID
    If @count >0
    Begin

    Set @ReturnCode = -1
     
    End
    Else
    Begin
     
     Set @ReturnCode = 1
     Insert into EmpTable(EmpID,FirstName,LastName,EmpFullName,LoginName,Gender,Location,[password],email,Dobdate,Martial,Doj)  values
     (@EmpID,@FirstName,@LastName,@EmpFullName,@LoginName,@Gender,@Location,@password,@Email,@Dobdate,@Martial,@Doj)
     End
     Select @ReturnCode as ReturnValue


        -- Insert statements for procedure here
    --SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    END
    • Edited by AV111 Friday, January 1, 2016 1:35 PM
    • Marked as answer by sathyaav Saturday, January 2, 2016 9:52 AM
    Friday, January 1, 2016 1:30 PM

All replies

  • CREATE PROCEDURE  [dbo].[spInsertRegisterNewEmployee]
    -- Add the parameters for the stored procedure here
    --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
    (@EmpID nvarchar(50),
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @EmpFullName nvarchar(50),
    @LoginName nvarchar(50),
    @Gender nvarchar(50),
    @Location nvarchar(50),
    @Password nvarchar(200),
    @Email nvarchar(200),
    @Dobdate date,
    @Martial nvarchar(50),
    @Doj date)

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --SET NOCOUNT ON;
    Declare @count int
    Declare @ReturnCode int


    Select @count = COUNT(EmpID)
    from EmpTable where EmpID = @EmpID
    If @count >0
    Begin

    Set @ReturnCode = -1
     
    End
    Else
    Begin
     
     Set @ReturnCode = 1
     Insert into EmpTable(EmpID,FirstName,LastName,EmpFullName,LoginName,Gender,Location,[password],email,Dobdate,Martial,Doj)  values
     (@EmpID,@FirstName,@LastName,@EmpFullName,@LoginName,@Gender,@Location,@password,@Email,@Dobdate,@Martial,@Doj)
     End
     Select @ReturnCode as ReturnValue


        -- Insert statements for procedure here
    --SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    END
    • Edited by AV111 Friday, January 1, 2016 1:35 PM
    • Marked as answer by sathyaav Saturday, January 2, 2016 9:52 AM
    Friday, January 1, 2016 1:30 PM
  • Many thanks.

    Yesterday I had retyped the same code in SQL management studio. It took the details.

    Many thanks for your prompt check and response.

    Cheers

    Sathya

    Saturday, January 2, 2016 9:53 AM