CODEWITHSUNDEEP
sqlsql-server
Chris Wong
Chris Wong

Reputation: 31

How to set multiple values to one declared parameter in SQL?

In SQL Server Management Studio, I want to set multiple values to one declared parameter. I wonder if the script below would work: 

declare @Parameter_A     NVARCHAR(MAX)
set @Parameter_A = 'ABC, DEF, GHI, JKL'

If the above script is not the correct way to achieve what I want, could someone help me? Thank you.

Upvotes: 1

Views: 12939

Answers (2)

Jason W
Jason W

Reputation: 13179

As mentioned in the comment, it completely depends on what you plan to do with the parameter as to the best method to accomplish this (and perhaps to add even how you get the selected values to be stored). If you can elaborate on those 2 things, we can probably give you better suggestions.

One thing you can easily use to store multiple values without having to get into parsing those values back out is to use table variables:

DECLARE @Params TABLE (A NVARCHAR(MAX))
INSERT @Params VALUES
    ('ABC'), ('DEF'), ('GHI'), ('JKL')
SELECT * FROM @Params

If you must iterate over the list, you can modify this to add an identity to simplify the logic.

DECLARE @Params TABLE (A NVARCHAR(MAX), Id INT NOT NULL IDENTITY(1,1))

Now you can iterate with logic like this:

DECLARE @i INT = 0
WHILE EXISTS (SELECT * FROM @Params P WHERE Id >= @i) BEGIN
    SELECT * FROM @Params P WHERE Id = @i
    SET @i = @i + 1
END

Or for scenarios where you only wish to process each unique value once, you can just use a DELETE (no need for additional identity attribute):

DECLARE @A NVARCHAR(MAX)
WHILE EXISTS (SELECT * FROM @Params P) BEGIN
    SET @A = (SELECT TOP 1 A FROM FROM @Params P)
    -- Perform any work
    DELETE @Params WHERE A = @A 
END

Upvotes: 2

user5162677
user5162677

Reputation:

I don't know if this is going to help but... there is a type called cursor, it's like a vector or an array. It's used like this:

CURSOR name IS SELECT something
FROM some_table;

That way you can store a lot of information in a cursor called "name". To iterate through its values, you can use a loop like this:

OPEN name;

LOOP

    FETCH name INTO x;
    EXIT WHEN name%NOTFOUND;

    dbms_output.put_line(x);

END LOOP;

CLOSE name;

This way you print all values stored. Hope that helped!

Upvotes: 0

Related Questions