CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Ameen Chaabani
Ameen Chaabani

Reputation: 191

Create a stored procedure to insert new data into a table

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?

Upvotes: 8

Views: 182545

Answers (4)

user11565652
user11565652

Reputation: 

-- =============================================
-- 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

Upvotes: 0

Bha15
Bha15

Reputation: 271

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

Upvotes: 0

Metaphor
Metaphor

Reputation: 6405

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

Upvotes: 0

u07ch
u07ch

Reputation: 13702

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'

Upvotes: 24

Related Questions