0

I am a novice in SQL and I am following a web tutorial. I encountered a piece of code where SELECT statement assigns a value to a variable. I suspect that such SELECT statements don't make a table as a result. E.g. when I want to mix the assignment with a column name as in:

DECLARE 
    @columns NVARCHAR(MAX) = '';

SELECT 
    @columns += QUOTENAME(category_name) + ',',
    category_name
FROM 
    production.categories
ORDER BY 
    category_name;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

PRINT @columns;

I get the following error:

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

(Following this course on Internet with database)

My question is if there exists another form of data manipulation (as an assignment) that in a SELECT statement don't produce a table (if my assumption is right).

Dale K
  • 25,246
  • 15
  • 42
  • 71
Igor Beuermann
  • 127
  • 1
  • 10
  • The error is telling you the problem. You're trying to assign the value of `@columns` **and** return the value of `category_name`. The error, I'm afraid, can't be more clear. A `SELECT` can only do one, or the other, but not both. – Thom A Oct 24 '19 at 09:26
  • I don't understand your question: _if there exists another form of data manipulation (as an assignment) that in a SELECT statement don't produce a table (if my assumption is right)._. If you took `category_name` out of that statement, it would be a SELECT doing an assignment and not producing a result – Nick.Mc Oct 24 '19 at 09:29
  • https://stackoverflow.com/questions/3945361/set-versus-select-when-assigning-variables – Dale K Oct 24 '19 at 09:29
  • Yes, you are right. But I my question is: "Does there exist another form of data manipulation (as an assignment) that in a SELECT statement don't produce a table (if my assumption is right)." – Igor Beuermann Oct 24 '19 at 09:29
  • If you want to assign all values into variable , the following part of code will be help you; @columns = STRING_AGG(category_name,';') WITHIN GROUP (ORDER BY category_name) – Esat Erkec Oct 24 '19 at 09:35
  • That is not a good "tutorial" - rather, it appears to be a bunch of topics grouped into sections that do not actually guide you or build the skills in a method to encourage success. Find another. A pivot is something you should leave until you master basic sql. What you are trying to do is a dynamic pivot which is even more complex. And this usage uses a "trick" to aggregate strings that is discouraged. – SMor Oct 24 '19 at 11:58
  • Whoever voted this down: Please keep in mind, that this is a new contributor and states clearly to be a novice with the topic. Here's someone trying to learn something. No reason to put one down... The question is clear and not stupid in any way. +1 from my side. – Shnugo Oct 24 '19 at 17:00

3 Answers3

2

No matter what you do, SELECT will always return a TABLE.

Creating a stored procedure with a lots of criteria is giving you the option to either return a TABLE or returns a VARIABLE.

DECLARE @VARIABLE_INPUT VARCHAR(50);
DECLARE @VARIABLE_OUTPUT VARCHAR(50);

SET @VARIABLE_INPUT = 'THIS_IS_INPUT';
EXEC storedProcedureTEST @VARIABLE_INPUT, @VARIABLE_OUTPUT OUTPUT;

PRINT 'The Output of storedProcedureTEST is: ' + @VARIABLE_OUTPUT ;

In your storedProcedureTEST:

CREATE PROCEDURE storedProcedureTEST
    @VARIABLE_INPUT VARCHAR(50),
    @VARIABLE_OUTPUT VARCHAR(50)='' OUTPUT
AS
BEGIN
    SET @VARIABLE_OUTPUT = @VARIABLE_INPUT + '_ABC';
END

Result:

The output of storedProcedureTEST is: THIS_IS_INPUT_ABC

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Raynoceros
  • 386
  • 2
  • 15
  • It might be a matter of taste, but it is - in my eyes - a very bad advise to use a stored procedure for this. Best was an *inline TVF*, which you can `APPLY` to your query. This SP forces you into *procedural thinking*, away from the much better *set-based thinking*... – Shnugo Oct 24 '19 at 10:09
  • Your answer does not answer the question actually... The OP wants to create a concatenated list of all `category_name` values found in a table... I hope you would not suggest to use a `CURSOR` together with this SP... – Shnugo Oct 24 '19 at 10:20
1

To answer your question literally:

When does SELECT not make a table as an output

Never!

The SELECT returns a result set, consisting of rows, and each row consisting of columns.

There is a special situation, when your result set comes back with exactly one row with one single column. This can be taken as a scalar value and therefore be assigned to a variable.

You can enforce a single row with a TOP 1 or with a WHERE-clause that guarantees to return never more than one row. But the engine will not see this in advance. Your provided code mentions two columns, the one with the QUOTENAME() and - after the comma - once more the category_name. That's why you get the exception.

It is a similar situation, when you want to test a list, e.g. with

WHERE SomeID IN(SELECT TheId FROM AnotherTable)

The SELECT within the IN() will return a list of ID values, but there will be just one column.

UPDATE Some hints upon your code...

What you are doing here:

SELECT 
    @columns += QUOTENAME(category_name) + ',',
    category_name
FROM

... is called quirky update and might work without the second mention of category_name. It is roughly an iteration through the table's values where each row adds to the previously set value of @column.

This approach is very dangerous and should be avoided (or used with some backgorund knowledge).

Starting with v2017 there is STRING_AGG() and before I'd prefer the approach with FOR XML PATH('')

Example

Try this:

USE master;
DECLARE @columns VARCHAR(MAX)='';

--Your approach with the quirky update

SELECT 
    @columns += QUOTENAME(COLUMN_NAME) + ',' 
FROM 
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='spt_fallback_db';

SELECT @columns;

--The recommended approach with XML

SET @columns=
STUFF(
(SELECT ','+QUOTENAME(COLUMN_NAME)
 FROM 
 INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='spt_fallback_db'
 FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,'');

 SELECT @columns;

--and - v2017+ needed - with STRING_AGG()

SELECT @columns=STRING_AGG(QUOTENAME(COLUMN_NAME),',')
FROM 
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='spt_fallback_db';

SELECT @columns;
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

You want to convert from row to single line column right

DECLARE 
    @columns NVARCHAR(MAX) = '';

set @columns = (select concat(quotename ,' : ', category_name) from production.categories order by category_name for xml path(''))


PRINT @columns;
Yustian
  • 31
  • 5