Xtykutl

Can you help me please? I wrote this code and I get an error

Incorrect syntax near the keyword SELECT

Here is my code

CREATE PROCEDURE dbo.spSetCommand

    @Client_id INT,

    @BcNumber INT,

    @ArticleId INT,

    @EntryNumber DECIMAL

AS

    DECLARE @CommandId INT 

BEGIN

    INSERT INTO commands(client_id, bc_number, date_command) 

    VALUES (@Client_id, @BcNumber, GETDATE());



    SET @CommandId = SELECT command_id 

                     FROM commands 

                     WHERE bc_number = @BcNumber;



    INSERT INTO entries(command_id, article_id, entry_number)  

    VALUES (@CommandId, @ArticleId, @EntryNumber);

END

Can someone help find the issue?

-- need to have brackets

set @CommandId = (SELECT command_id FROM commands WHERE bc_number = @BcNumber);

if there are multiple results from the query, this will fail, an error will come.

you can also use this:

SELECT @CommandID = command_id 

FROM commands

WHERE bc_number = @BcNumber

In case of multiple results, this will give you the last value (which may/may not be your logical thing)

In SQL SELECT SCOPE_IDENTITY() Return Recent add record Identity Key in your case command_id

CREATE PROCEDURE dbo.spSetCommand

        @Client_id INT,

        @BcNumber INT,

        @ArticleId INT,

        @EntryNumber DECIMAL

    AS

        DECLARE @CommandId INT 

    BEGIN

        INSERT INTO commands(client_id, bc_number, date_command) 

        VALUES (@Client_id, @BcNumber, GETDATE());

SET @CommandId = SELECT SCOPE_IDENTITY()

INSERT INTO entries(command_id, article_id, entry_number)  

        VALUES (@CommandId, @ArticleId, @EntryNumber);

    END

There is an error in the below line code:

SET @CommandId = SELECT command_id 

                     FROM commands 

                     WHERE bc_number = @BcNumber;

if there are multiples records exists WHERE bc_number = @BcNumber then this will generate an error other wise working fine.

Atrenate way is below:

SELECT @CommandId =command_id  commands WHERE bc_number = @BcNumber;

but the most appropriate way is to get recent added record in current table by current instant you can use SCOPE_IDENTITY() SQL function

SET @CommandId = SELECT command_id 

FROM commands 

WHERE bc_number = @BcNumber;

Instead try

SELECT @CommandId = top 1 command_id 

FROM commands 

WHERE bc_number = @BcNumber order by command_id desc;

I use set @foo = some int like 4; and select @foo = some query;