CODEWITHSUNDEEP
sqlsql-servert-sqlstring-aggregation
hmahdavi
hmahdavi

Reputation: 2354

How to put result of one column inside a string variable?

I try to select number of rows and then put them into string variable such as 1,2,3,4,5, but get this error :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I use MSSQL SERVER

    DECLARE @CodeNameString VARCHAR(MAX)

SELECT @CodeNameString = STUFF(
           (
               SELECT dbo.CharterReference.TicketNo+','
               FROM   dbo.CharterReference

           ),
           1,
           1,
           ''
       )

SELECT @CodeNameString

How can i fix this?

Upvotes: 2

Views: 486

Answers (3)

BeanFrog
BeanFrog

Reputation: 2315

The error message is complaining because you have multiple rows returned, and that is not allowed in the way you have done this.

Rearranging to a normal looking select statement, and then lopping off the last comma afterwards would work around this:

DECLARE @CodeNameString VARCHAR(MAX);
set @CodeNameString = '';

SELECT @CodeNameString = TicketNo + ','
FROM   dbo.CharterReference;

SELECT left(@CodeNameString, len(@CodeNameString)-1) as CodeNameString;

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81950

If you want the values in @CharterReference, you can use the following

Declare @CharterReference table (TicketNo int)
Insert Into @CharterReference values
(1),(2),(3),(4),(5),(6),(7),(8)

Declare @CodeNameString  varchar(Max) = '>>>'
Select @CodeNameString =replace(concat(@CodeNameString ,',',TicketNo),'>>>,','')
 From  @CharterReference
 Where TicketNo between 1 and 5
 Order By TicketNo

Select @CodeNameString

Returns

1,2,3,4,5

Or you can use a little XML

Select @CodeNameString=Stuff((Select ',' +cast(TicketNo as varchar(25)) 
                               From  @CharterReference 
                               Where TicketNo between 1 and 5 
                               For XML Path ('') 
                              ),1,1,'')

Upvotes: 3

Erwin Smout
Erwin Smout

Reputation: 18408

The SQL-based solution requires you to use recursive SQL for this. The syntax is typically DBMS-specific, and guessing by the syntax you're using in the example, O believe your engine calls this feature "recursive CTE".

The alternative is to cursor over the result set with the individual row and construct the string append in your client program.

Yet another alternative is to use the PL/SQL dialect of your system. You can then write an SQL procedure where you do the cursoring over the result set and the string appending. You can expose this SQL procedure as a callable module to your client programs.

Upvotes: 0

Related Questions