Create a stored procedure to insert new data into a table

Question

I want to create a stored procedure to insert a new row in a table 'dbo.Terms'

CREATE PROCEDURE dbo.terms 
       @Term_en                      NVARCHAR(50)  = NULL   , 
       @Createdate                   DATETIME      = NULL   , 
       @Writer                       NVARCHAR(50)  = NULL   , 
       @Term_Subdomain               NVARCHAR(50)  = NULL  
AS 
BEGIN 
     SET NOCOUNT ON 

     INSERT INTO dbo.terms
          (                    
            Term_en                     ,
            Createdate                  ,
            Writer                      ,
            Term_Subdomain                 
          ) 
     VALUES 
          ( 
            @Term_en    = 'Cat'               ,
            @Createdate   = '2013-12-12'      ,
            @Writer         = 'Fadi'          ,
            @Term_Subdomain = 'English'                    
          ) 

END 

GO

But is shows me an error here ( @Term_en = 'Cat') incorrect syntax Any help?

What are you actually trying to do there? – Martin Smith Dec 12 '13 at 13:24 — Martin Smith, Dec 12 '13 at 13:24

score 24 · Accepted Answer · edited May 23 '17 at 12:33

I presume you want to insert the values cat etc into the table; to do that you need to use the values from your procedures variables. I wouldn't call your procedure the same name as your table it will get all kinds of confusing; you can find some good resources for naming standards (or crib from Adventureworks)

CREATE PROCEDURE dbo.terms 
       @Term_en                      NVARCHAR(50)  = NULL   , 
       @Createdate                   DATETIME      = NULL   , 
       @Writer                       NVARCHAR(50)  = NULL   , 
       @Term_Subdomain               NVARCHAR(50)  = NULL  
AS 
BEGIN 
     SET NOCOUNT ON 

     INSERT INTO dbo.terms
          (                    
            Term_en                     ,
            Createdate                  ,
            Writer                      ,
            Term_Subdomain                 
          ) 
     VALUES 
          ( 
            @Term_en,
            @Createdate,
            @Writer,
            @Term_Subdomain
          ) 

END 

GO

And to test it

exec dbo.terms 
    @Term_en    = 'Cat'               ,
    @Createdate   = '2013-12-12'      ,
    @Writer         = 'Fadi'          ,
    @Term_Subdomain = 'English'

@MartinSmith I think that depends how you call your procedures out of your application, If you are always able to pass all parameters over when you call a procedure then you don't need defaults. If your code calls procedures and sometimes don't have values to pass over for all variables the parameters you do need the null otherwise the procedure will expect you to pas the null into the variable. — u07ch, Dec 12 '13 at 13:50
Thanks a lot, now. can anyone tell me how to create a trigger to add a rows in another table when any new rows has inserted in a table 'dbo.terms'? — Ameen Chaabani, Dec 12 '13 at 14:10

score 0 · Answer 2 · answered Dec 12 '13 at 14:19

Here is how to set your defaults for parameters in your proc:

CREATE PROCEDURE dbo.terms 
       @Term_en        NVARCHAR(50)  = 'Cat', 
       @Createdate     DATETIME      = '2013-12-12', 
       @Writer         NVARCHAR(50)  = 'Fadi', 
       @Term_Subdomain NVARCHAR(50)  = 'English'
AS 
BEGIN 
     SET NOCOUNT ON 

     INSERT INTO dbo.terms
          (                    
            Term_en                     ,
            Createdate                  ,
            Writer                      ,
            Term_Subdomain                 
          ) 
     VALUES 
          ( 
            @Term_en,
            @Createdate,
            @Writer,
            @Term_Subdomain                    
          ) 

END 

GO

score 0 · Answer 3 · answered Jul 10 '17 at 10:50

Your code is not correct. You put value in insert part. You should enter value in execution part

CREATE  PROCEDURE  dbo.terms     
   @Term_en                      NVARCHAR(50)  = NULL   ,    
   @Createdate                   DATETIME      = NULL   ,     
   @Writer                       NVARCHAR(50)  = NULL   ,    
   @Term_Subdomain               NVARCHAR(50)  = NULL
AS  
BEGIN  
 SET NOCOUNT ON  
 INSERT INTO dbo.terms  
      (                    
        Term_en,  
        Createdate,  
        Writer,
        Term_Subdomain                 
      )

 VALUES 
      ( 
        @Term_en    ,
        @Createdate ,
        @Writer     ,
        @Term_Subdomain                     
      ) 

 END

execute by this

exec dbo.terms 
@Term_en    = 'Cat'               ,
@Createdate   = '2013-12-12'      ,
@Writer         = 'Fadi'          ,
@Term_Subdomain = 'English' 

GO

score 0 · Answer 4 · 2019-05-28T07:59:38.697

-- =============================================
-- Author:      xxxx 
-- Create date: xx-xx-xxxx
-- Description: Procedure for Inserting Data in table 
-- =============================================


CREATE PROCEDURE [dbo].[SP_Emp_Insert]
(
@Empname nvarchar(250)=null,
@Status int=null,
@LoginUserId nvarchar(50)=null,
@Msg nvarchar(MAX)=null OUTPUT
)
AS
BEGIN TRY

    INSERT INTO tbl_Employee
    VALUES
    (
    @Empname ,
    @Status,
    GETDATE(),
    GETDATE(),
    @LoginUserId
    )

    SET @Msg='Table Detail Saved Successfully.'

END TRY
BEGIN CATCH

    SET @Msg=ERROR_MESSAGE()

END CATCH

GO

Create a stored procedure to insert new data into a table

4 Answers4

Linked