Set Value Local SQL Server Variable

Asked

Viewed 10,109 times

3

I would like to assign the value of the local variable within the SELECT, as in the example below, but displays the following error message:

"A SELECT statement that assigns a value to a variable must not be Combined with data-Retrieval Operations." 

CREATE PROCEDURE [dbo].[TESTE]
    @Param INT
AS
BEGIN    
    SET NOCOUNT ON;
    DECLARE @VariavelLocal DECIMAL,@VariavelLocal2 DECIMAL   
    SELECT  (@VariavelLocal1 = SELECT COUNT(TesteId) FROM Table)
            ,(@VariavelLocal2 = 1)
            ,@VariavelLocal1 + @VariavelLocal2
            ,T2.T2Teste    
    FROM Table2 T2
END

You can do this?

sql-server variables procedure

2 answers

3

The message is clear. Just don’t use SELECT to assign value and to return data:

SELECT @VariavelLocal1 = SELECT COUNT(TesteId) FROM Table), 
       @VariavelLocal2 = 1

SELECT @VariavelLocal1 + @VariavelLocal2, T2.T2Teste
    FROM Table2 T2

0

To create variables use DECLARE and to assign values use SET

Example:

DECLARE @VariavelLocal DECIMAL, @VariavelLocal2 DECIMAL,@VariavelLocal1 DECIMAL
SET @VariavelLocal1 = (SELECT COUNT(TesteId) FROM [Table]);
SET @VariavelLocal2 = 1;
SET @VariavelLocal = (@VariavelLocal1 + @VariavelLocal2);

You can select after that:

SELECT @VariavelLocal, 
       @VariavelLocal1, 
       @VariavelLocal2

In your code:

CREATE PROCEDURE Sp_Teste
    @Param INT
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @VariavelLocal DECIMAL, @VariavelLocal2 DECIMAL,@VariavelLocal1 DECIMAL

    SET @VariavelLocal1 = (SELECT COUNT(TesteId) FROM [Table]);
    SET @VariavelLocal2 = 1;
    SET @VariavelLocal = (@VariavelLocal1 + @VariavelLocal2);

    SELECT @VariavelLocal, 
           @VariavelLocal1, 
           @VariavelLocal2
END

Execute Test

EXEC Sp_Teste 1

References:

Browser other questions tagged sql-server variables procedure

You are not signed in. Login or sign up in order to post.