How to store multiple values in a SQL Server variable

Question

I want to store values from a SELECT statement into a variable which is capable of holding more than one value because my SELECT statement returns multiple values of type INT. This is how my SP looks like so far.

ALTER PROCEDURE "ESG"."SP_ADD"
AS
BEGIN

DECLARE @Id table(identifiers VARCHAR);

INSERT INTO @Id (identifiers) VALUES('axaa1aaa-aaaa-a5aa-aaaa-aa8aaaa9aaaa');
INSERT INTO @Id (identifiers) VALUES('bxbb1bbb-bbbb-b5bb-bbb4-bb8bbbb9bbbf');

DECLARE @tranID INT = (SELECT 
DOCUMENT_SET_.DOCUMENT_SET_TRANSACTION_ID 
                            FROM DOCUMENT_SET_TRANSACTION 
                                    WHERE DOCUMENT_SET_TRANSACTION.IDENTIFIER IN (SELECT identifiers FROM @Id));  

END

Variable @tranID should be a list or an array to hold the ids. Is it possible to do it SQL Server?

Why don't you declare it as Table? DECLARE @tranID TABLE (IDs INT);, then insert your data, INSERT INTO @tranID SELECT ID FROM .... — Ilyes
– Ilyes, Commented Nov 7, 2017 at 22:07
It complaints for Incorrect syntax near '='. if I declare it as a table — David
– David, Commented Nov 7, 2017 at 22:08
What are you going to do with @tranID from there? Just return it to the client? If so just do a SELECT and don't "return" a value. — D Stanley
– D Stanley, Commented Nov 7, 2017 at 22:09
Actually once I got the ids I will loop through and I have to do some ore operations. First I need to get the ids — David
– David, Commented Nov 7, 2017 at 22:10

royal · Accepted Answer · 2019-09-03 15:58:54Z

6

You can declare a variable of type table

DECLARE @tblTrans TABLE (
    tranID INT
);

INSERT INTO @tblTrans 
SELECT DOCUMENT_SET_TRANSACTION.DOCUMENT_SET_TRANSACTION_ID 
FROM ESG.DOCUMENT_SET_TRANSACTION 
WHERE DOCUMENT_SET_TRANSACTION.IDENTIFIER 
    IN (SELECT identifiers FROM @envelopeId);

Depending on what you want to do with the values after this, you could declare a cursor to loop through them or select straight from the variable.

You could also look into using a temporary table depending on what scope you need.

edited Sep 3, 2019 at 15:58

answered Nov 7, 2017 at 22:12

royal

1732 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Over a year ago

Looks like it is working but how can I make sure that @tranId has the values.

royal Over a year ago

@David You may need to declare the size of the INT column if it's saying the data would be truncated. I think the default is 4 places so just increase the number depending on what the size of the column you select from is. To check the values are correct, you can just SELECT * FROM @tblTrans in SSMS at the end of the procedure, it should print the results to the output window.

David Over a year ago

Awesome. I've increased the size. Thanks sRoyal.

Simon Restrepo · Accepted Answer · 2017-11-07 22:16:16Z

0

Try this, only take the firs row of example. Do u try this?

select DOCUMENT_SET_TRANSACTION.DOCUMENT_SET_TRANSACTION_ID,
(STUFF((SELECT '-' + convert(varchar(max),DOCUMENT_SET_TRANSACTION.DOCUMENT_SET_TRANSACTION_ID) 
         FROM  ESG.DOCUMENT_SET_TRANSACTION  
         FOR XML PATH ('')), 1, 2, '')) AS example
FROM ESG.DOCUMENT_SET_TRANSACTION

answered Nov 7, 2017 at 22:16

Simon Restrepo

3431 gold badge2 silver badges16 bronze badges

1 Comment

Simon Restrepo Over a year ago

convert the values for xml and then take everthing into a string concated with "-". that what you want to achieve?

Collectives™ on Stack Overflow

How to store multiple values in a SQL Server variable

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related