CODEWITHSUNDEEP
sql-serverstored-procedures
user1111699
user1111699

Reputation: 15

Update record with a stored procedure in SQL Server

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?

Upvotes: 0

Views: 70

Answers (3)

Milica Medic Kiralj
Milica Medic Kiralj

Reputation: 3780

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

Upvotes: 0

Steven Britton
Steven Britton

Reputation: 197

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.

Upvotes: 0

dean
dean

Reputation: 10098

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

Upvotes: 3

Related Questions