Use An Out Variable And Perform Calculation In One Query

Question

I am wanting to use an out variable, but I need to perform calculations as well. Any way I write my query I keep getting this error:

Msg 141, Level 15, State 1, Line 1
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Here is my DDL

Declare @SQL nvarchar(MAX), @stat float

Create Table PPerformance
(
  name varchar(500)
  ,attempts int
  ,success int
)

Insert Into PPerformance (name, attempts, success) Values
('Bob', '22', '10')
,('Red', '14', '12')

And this is my query syntax

Set @SQL = 'Select name, @stat = attempts/success FROM PPerformance where name = Bob'

EXECUTE sp_executesql @SQL, N'@stat float OUTPUT', @stat OUTPUT;
Select @stat As Stat

Drop Table PPerformance

What do I need to change so that I can store the value from the equation in the variable @stat

Answer 1

You need to assign the name column to a variable, and return it as well as the @stat variable:

CREATE TABLE dbo.PPerformance
(
  [name] VARCHAR(500)
  ,attempts INT
  ,success INT
)

INSERT INTO dbo.PPerformance ([name], attempts, success) 
VALUES ('Bob', '22', '10')
    , ('Red', '14', '12');


DECLARE @SQL NVARCHAR(MAX);
DECLARE @name VARCHAR(500);
DECLARE @stat FLOAT;
DECLARE @vars NVARCHAR(MAX);

SET @vars = N'@stat float OUTPUT, @name varchar(500) OUTPUT';

SET @SQL = N'SELECT @name = pp.name
    , @stat = pp.attempts / pp.success 
FROM dbo.PPerformance pp
WHERE pp.name = ''Bob'';';

EXECUTE sys.sp_executesql @SQL, @vars, @stat OUT, @name OUT;

SELECT Name = @name
    , Stat = @stat;

DROP TABLE dbo.PPerformance;

The error is coming from this part of your code:

Select name, @stat = attempts/success 

This is attempting to assign a value to the @stat value and show the content of the name column. You cannot perform both operations in a single statement.

There are a couple of items in your source code that are potentially troublesome:

1. You should always specify the schema name when referencing objects in T-SQL. So, select column1, column2 from mytable should be select column1, column2 from schema.mytable.

2. End each statement with a semi-colon: select column1, column2 from schema.mytable;

3. Get in the habit of using an alias for tables referenced in T-SQL statements. For example: select mt.column1, mt.column2 from schema.mytable mt;

4. Don't use reserved names as column or table names. You've named the first column in your table name, which is a reserved word; I'd change that to PerfName or something.

Answer 2

If you need to store just one column value in a variable but return both as a result set, you could also consider using a table variable. First you use it to store the query's results:

DECLARE @results TABLE
(
  name varchar(500),
  stat float
);

SET @SQL = N'...';

INSERT INTO
  @results
EXECUTE
  sp_executesql @SQL
;

Once the results are stored, you can retrieve whatever column value you need from the table variable into your ordinary, scalar, variable:

DECLARE @stat float;
SELECT
  @stat = stat
FROM
  @results
;

and then, when necessary, return the results as a data set:

SELECT
  *
FROM
  @results
;