2

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

4

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.

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.