SQL Server Procedure Declare a List

SQL Server procedure declare a list

You could declare a variable as a temporary table like this:

declare @myList table (Id int)

Which means you can use the insert statement to populate it with values:

insert into @myList values (1), (2), (5), (7), (10)

Then your select statement can use either the in statement:

select * from DBTable
where id in (select Id from @myList)

Or you could join to the temporary table like this:

select *
from DBTable d
join @myList t on t.Id = d.Id

And if you do something like this a lot then you could consider defining a user-defined table type so you could then declare your variable like this:

declare @myList dbo.MyTableType

Declare a variable list in SQL Server stored procedure

You will need a table anyways, but at least you avoid tons of processing by doing a like everytime:

-- create a table variable
declare @ids table
(
  id int not null
)

-- insert the id into the table variable
insert into @ids
select id from table1 where column1 like '%something%'

-- delete
delete from tablen where id in (select * from @ids)

You can also use a temporary table, it looks like the same, but instead of @ids, you will need #ids, and you need to drop the temporary table after the job is done.

To choose between a temporary table (physical table) or table variable (memory like table) you will really need to do some tests, but by definition complex data works better in temporary tables. If you just need to hold a small numbers of ids for a short period I'm very sure that table variables are better.

What's the difference between a temp table and table variable in SQL Server?

How to pass a list of strings as a parameter in a stored procedure in SQL?

You will have to use table valued parameters

1. Define new type as follows

   CREATE TYPE Prod_Code AS TABLE ( ProductCode varchar );
   

2. then use this type in your stored procedure

    create procedure [dbo].[proc_aggregation]
    @Prod_Code Prod_Code READONLY,
    @Prod_Desc varchar (30)
    as
    ......
   

3. Now before calling the stored procedure fill the table

    declare @PC Prod_Code;
    insert @PC VALUES ('12012'), ('12011'), ('12014')
   

4. Now Call the sp like this

    EXEC dbo.proc_aggregation @PC, @Prod_Desc;
   

Use list of values in procedure

If the password string was an argument, then perhaps STRING_SPLIT. Instead, I start with a local table. This is not a procedure yet, but it can be adapted. Also, the use of lower fn in the where will prevent the the use of an index - research sargable if interested. I removed the fn, but if your database is case sensitive, it would be time to really think about life and performance.

DECLARE @_email AS VARCHAR(128)

DECLARE @hashedPasswordList as table (
    ID int IDENTITY,
    HashedPassword varchar(128)
)

DECLARE @randomIndex int
DECLARE @hashedPassword varchar(128)

INSERT INTO @hashedPasswordList (HashedPassword) VALUES ('{bcrypt}$2a$10'),('{bcrypt}$2a$10$Kh8YS.'),('{bcrypt}$2a$10$safds'),('{bcrypt}$2a$10$Wl8ZKTF2YGobQ6yi')

SET @randomIndex = 1 + FLOOR( (SELECT COUNT(*) FROM @hashedPasswordList) * RAND() )
SELECT @hashedPassword = HashedPassword FROM @hashedPasswordList WHERE ID = @randomIndex

UPDATE accounts 
SET passwd = @hashedPassword
WHERE email = @_email

How to create a variable of list of strings in stored procedure and how to use it in another query?

create procedure store_validated_sku_id(variable_sku_id OUT VARCHAR2)
 as
BEGIN
  SELECT listagg(vsku.sku_id, ',') within group(order by vsku.sku_id)
    INTO variable_sku_id
    FROM chelsea_prdcataloga.vs_sku_discont_details_test discontd
    JOIN chelsea_prdcataloga.vsx_dcs_sku vsku ON discontd.swap_sku = vsku.jda_sku_id
    JOIN chelsea_prdcataloga.auto_ship_view bcc ON bcc.sku_id = vsku.sku_id
   WHERE vsku.web_eligible = 1        
     AND vsku.discontinued = 0
     AND bcc.auto_ship_eligible = 1;

  dbms_output.put_line('variable_sku_id = ' || variable_sku_id);

END store_validated_sku_id;

C# SQL Server - Passing a list to a stored procedure

If you're using SQL Server 2008, there's a new featured called a User Defined Table Type. Here is an example of how to use it:

Create your User Defined Table Type:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

Next you need to use it properly in your stored procedure:

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

Finally here's some sql to use it in c#:

using (var con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        using (var table = new DataTable()) {
          table.Columns.Add("Item", typeof(string));

          for (int i = 0; i < 10; i++)
            table.Rows.Add("Item " + i.ToString());

          var pList = new SqlParameter("@list", SqlDbType.Structured);
          pList.TypeName = "dbo.StringList";
          pList.Value = table;

          cmd.Parameters.Add(pList);

          using (var dr = cmd.ExecuteReader())
          {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
          }
         }
    }
}

To execute this from SSMS

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')
INSERT INTO @list VALUES ('Banana')
INSERT INTO @list VALUES ('Orange')

-- Alternatively, you can populate @list with an INSERT-SELECT
INSERT INTO @list
   SELECT Name FROM Fruits

EXEC sp_UseStringList @list

Use a stored procedure to get a list of nvarchar(20) values as a parameter

you can use a temporary table to recuperate your list from the stored procedure, like the example below :

create proc Test
AS BEGIN

SELECT CAST('jkj' AS NVARCHAR(20)) value 

END

DECLARE @tmp TABLE(value nvarchar(20))

INSERT INTO @tmp EXEC GhaziTest

SELECT * from @tmp

SQL Stored Procedure data type for list of rows

Use BULK COLLECT, example:

DECLARE
TYPE emp_typ IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
all_employees emp_typ;
BEGIN
SELECT * BULK COLLECT INTO all_employees FROM employees;

A SELECT ... BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.

---

Related Topics