CODEWITHSUNDEEP
sqlsql-serverdatabase
David
David

Reputation: 287

How to store multiple values in a SQL Server variable

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?

Upvotes: 0

Views: 6421

Answers (2)

royal
royal

Reputation: 173

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.

Upvotes: 6

Simon Restrepo
Simon Restrepo

Reputation: 333

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

Upvotes: 0

Related Questions