Stored procedure - Store query into parameter

Question

I'm trying to store a query into a variable and here is what I'm doing :

@check int = [return_code] FROM [dbo].[RESULT] WHERE [code_test] = 'T3005_das'

The return code field only contains the 0 or 1 value. When I'm trying to do this, here's the error message I've got :

Msg 156, Level 15, State 1, Procedure V116_T3005, Line 7
Incorrect syntax near the keyword 'FROM'.

I don't really understand why it's not working properly as I've followed samples of code on the internet.

Any idea?

EDIT : Here's my edited procedure :

CREATE PROCEDURE V116_T3005
    DECLARE @check int 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT @check = [code_retour] FROM [dbo].[RESULTAT] WHERE [code_test] = 'T3005_das'
    --logical code
END

Now I'm getting these errors :

Msg 156, Level 15, State 1, Procedure V116_T3005, Line 7
Incorrect syntax near the keyword 'DECLARE'.

Msg 156, Level 15, State 1, Procedure V116_T3005, Line 9
Incorrect syntax near the keyword 'AS'.

Remove Declare from your Stored Proc. Is @check your output variable? — Anup Agrawal
– Anup Agrawal, Commented Mar 31, 2014 at 15:42

juergen d · Accepted Answer · 2014-03-31 13:49:41Z

1

declare @check int

select @check = [return_code] 
FROM [dbo].[RESULT] 
WHERE [code_test] = 'T3005_das'

answered Mar 31, 2014 at 13:49

juergen d

205k40 gold badges304 silver badges377 bronze badges

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

1 Comment

Traffy Over a year ago

Thank for your help. However, I still got errors. Could you check my edit?

avb · Accepted Answer · 2014-03-31 20:23:00Z

0

If @check is an output parameter the word declare should be omitted. If it is not it should be declared after BEGIN and if so, I would suggest:
declare @check integer = (select [code_retour] FROM [dbo].[RESULTAT] WHERE [code_test] = 'T3005_das');
BTW are you sure that select [code_retour] FROM [dbo].[RESULTAT] WHERE [code_test] = 'T3005_das' will always return one value? Otherwise error will be raised.

answered Mar 31, 2014 at 20:23

avb

1,8821 gold badge14 silver badges25 bronze badges

Collectives™ on Stack Overflow

Stored procedure - Store query into parameter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related