Update record with a stored procedure in SQL Server

Question

I am coming back to dealing with db after a while, and have a weird error.

I checked how the syntax suppose to look, and it seems right.

I get an

incorrect syntax near where

error when I try to create this stored procedure:

CREATE PROCEDURE [dbo].[UpdateLanguageToInterpreter]
    @interpreterId varchar(128),
    @languageId int,
    @isSignLanguage bit,
    @prof_level int
AS
    insert into [dbo].[Interpreter_Language_List]
        ([isSignLang], [prof_level_id])
    VALUES(@isSignLanguage, @prof_level)
    WHERE
            [IntrepeterId] = @interpreterId 
            and [LanguageId] = @languageId

    RETURN 1

Have you got any ideas?

Also, note that the default RETURN value for a stored procedure that has run without error is 0 - a non-zero value indicates an error — Julian Joseph
– Julian Joseph, Commented Mar 28, 2014 at 15:48

dean · Accepted Answer · 2014-03-28 14:43:56Z

3

INSERT ... VALUES does not have a WHERE clause.

answered Mar 28, 2014 at 14:43

dean

10.1k2 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steven Britton · Accepted Answer · 2014-03-28 14:54:12Z

0

Are you looking to insert the @interpreterId and @languageId parameters as well, or validate them prior to the insert? If it's the former, just add the fields and parameters to the insert and remove the where clause, otherwise, do your validation prior to calling the SPROC. Alternatively, you could do a select statement before the insert, validate, and decide on inserting or returning/exiting the procedure.

answered Mar 28, 2014 at 14:54

Steven Britton

1972 silver badges5 bronze badges

Comments

Milica Medic Kiralj · Accepted Answer · 2014-03-28 23:02:39Z

Your title says update record. If you want to update a record using a procedure you can use this:

CREATE PROCEDURE [dbo].[cusp_CustomerUpdate]
(
    @interpreterId varchar(128),
    @languageId int,
    @isSignLanguage bit,
    @prof_level int
)
AS
BEGIN
SET NOCOUNT OFF;
UPDATE [dbo].[Interpreter_Language_List]
SET      
[isSignLang]=@isSignLanguage,
[prof_level_id]=@prof_level 

WHERE
[IntrepeterId] = @interpreterId AND
[LanguageId] = @languageId
END
GO

If you want to use INSERT INTO you are either missing something like this after VALUES:

SET 
  [IntrepeterId] = @interpreterId
  [LanguageId] = @languageId

SELECT 
    [isSignLang]=@isSignLanguage,
    [prof_level_id]=@prof_level
FROM [dbo].[Interpreter_Language_List]
 WHERE
    [IntrepeterId] = @interpreterId AND
    [LanguageId] = @languageId
    END
    GO

Either you need to get rid of the WHERE clause:

INSERT INTO [dbo].[Interpreter_Language_List]
        ([IntrepeterId], 
         [LanguageId], 
         [isSignLang], 
         [prof_level_id])
VALUES
(@interpreterId ,  
@languageId, 
@isSignLanguage, 
@prof_level)

Hope this helps

Collectives™ on Stack Overflow

Update record with a stored procedure in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related