Set Value Variable Local SQL Server

3

I would like to assign the value of the local variable within SELECT , as in the example below, but it 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

Can you do this?

    
asked by anonymous 18.06.2014 / 21:18

2 answers

3

The message is clear. Just do not 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
    
18.06.2014 / 21:30
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 do select after this:

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

Run Sp_Test

EXEC Sp_Teste 1

References:

19.06.2014 / 03:12