Set Value Variable Local SQL Server

Question

Set Value Variable Local SQL Server

Navigation

#1 by (3 votes)
#2 by (0 votes)

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?

sql-server procedure

asked by anonymous 18.06.2014 / 21:18

2 answers

How to store state of a checkbox Validation of fields in the Client in ASP .NET MVC 4

score 3 · Answer 1

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

score 0 · Answer 2

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: