[sql] Passing multiple values for same variable in stored procedure

I have a variable that passes multiple values to stored procedure.

When I see through fidler I see values being passed correctly like

    arg1=331     arg1=222     arg1=876     arg1=932 

In my stored procedure I am reading as

    procedure mainValues       @Arg1List     nvarchar(3000)     as begin   --Temp table to store split values   declare @tmp_values table (   value nvarchar(255) not null);        --function splitting values     insert into @tmp_values     select * from f_split(@Arg1List, ',');      --inserting in table value column is int.         insert into t_values (    value   )   select    b.value   from @tmp_values b; 

When I test it, it doesn't add any values in t_values table. I checked the function etc. are all working fine. The problem is @Arg1List. It looks like stored procedure has no values in it. Please let me know how to declare @Arg1List properly so it takes multiple values as it seems to be the problem.

This question is related to sql sql-server tsql

The answer is


You will need to do a couple of things to get this going, since your parameter is getting multiple values you need to create a Table Type and make your store procedure accept a parameter of that type.

Split Function Works Great when you are getting One String containing multiple values but when you are passing Multiple values you need to do something like this....

TABLE TYPE

CREATE TYPE dbo.TYPENAME AS TABLE   (     arg int    )  GO 

Stored Procedure to Accept That Type Param

 CREATE PROCEDURE mainValues   @TableParam TYPENAME READONLY  AS     BEGIN     SET NOCOUNT ON;   --Temp table to store split values   declare @tmp_values table (   value nvarchar(255) not null);        --function splitting values     INSERT INTO @tmp_values (value)    SELECT arg FROM @TableParam      SELECT * FROM @tmp_values  --<-- For testing purpose END 

EXECUTE PROC

Declare a variable of that type and populate it with your values.

 DECLARE @Table TYPENAME     --<-- Variable of this TYPE   INSERT INTO @Table                --<-- Populating the variable   VALUES (331),(222),(876),(932)  EXECUTE mainValues @Table   --<-- Stored Procedure Executed  

Result

╔═══════╗ ║ value ║ ╠═══════╣ ║   331 ║ ║   222 ║ ║   876 ║ ║   932 ║ ╚═══════╝ 

Your stored procedure is designed to accept a single parameter, Arg1List. You can't pass 4 parameters to a procedure that only accepts one.

To make it work, the code that calls your procedure will need to concatenate your parameters into a single string of no more than 3000 characters and pass it in as a single parameter.


Questions with sql tag:

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database Export result set on Dbeaver to CSV How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server IF EXISTS THEN 1 ELSE 2 How to add a boolean datatype column to an existing table in sql? Presto SQL - Converting a date string to date format What is the meaning of <> in mysql query? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Convert timestamp to date in Oracle SQL #1292 - Incorrect date value: '0000-00-00' Postgresql tables exists, but getting "relation does not exist" when querying SQL query to check if a name begins and ends with a vowel Find the number of employees in each department - SQL Oracle Error in MySQL when setting default value for DATE or DATETIME Drop view if exists Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers TypeError: tuple indices must be integers, not str Select Rows with id having even number SELECT list is not in GROUP BY clause and contains nonaggregated column IN vs ANY operator in PostgreSQL How to insert date values into table Error related to only_full_group_by when executing a query in MySql How to select the first row of each group? Connecting to Microsoft SQL server using Python eloquent laravel: How to get a row count from a ->get() How to execute raw queries with Laravel 5.1? In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function Rebuild all indexes in a Database SQL: Two select statements in one query DB2 SQL error sqlcode=-104 sqlstate=42601 What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types How to run .sql file in Oracle SQL developer tool to import database? Concatenate columns in Apache Spark DataFrame How Stuff and 'For Xml Path' work in SQL Server? Fatal error: Call to a member function query() on null

Questions with sql-server tag:

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd SQL Server IF EXISTS THEN 1 ELSE 2 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to add a boolean datatype column to an existing table in sql? How to import an Excel file into SQL Server? How to use the COLLATE in a JOIN in SQL Server? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Drop view if exists Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to update large table with millions of rows in SQL Server? Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers Select Rows with id having even number A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233) SQL Server: Error converting data type nvarchar to numeric How to add LocalDB to Visual Studio 2015 Community's SQL Server Object Explorer? Using DISTINCT along with GROUP BY in SQL Server Rebuild all indexes in a Database How to generate Entity Relationship (ER) Diagram of a database using Microsoft SQL Server Management Studio? The target principal name is incorrect. Cannot generate SSPI context How Stuff and 'For Xml Path' work in SQL Server? How to view the roles and permissions granted to any database user in Azure SQL server instance? How do I create a local database inside of Microsoft SQL Server 2014? Format number as percent in MS SQL Server MSSQL Regular expression How to select all the columns of a table except one column? SQL count rows in a table EXEC sp_executesql with multiple parameters SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Remove decimal values using SQL query How to drop all tables from a database with one SQL query? How to get last 7 days data from current datetime to last 7 days in sql server Get last 30 day records from today date in SQL Server Using Excel VBA to run SQL query No process is on the other end of the pipe (SQL Server 2012) How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server How to join two tables by multiple columns in SQL? The database cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported

Questions with tsql tag:

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL 'MOD' is not a recognized built-in function name Conversion failed when converting the varchar value 'simple, ' to data type int How can INSERT INTO a table 300 times within a loop in SQL? T-SQL split string based on delimiter How to generate a range of numbers between two numbers? Must declare the scalar variable How can I include null values in a MIN or MAX? SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value Error - "UNION operator must have an equal number of expressions" when using CTE for recursive selection SQL Server : Transpose rows to columns SQL Server: how to create a stored procedure In SQL Server, how to create while loop in select SQL Left Join first match only TSQL PIVOT MULTIPLE COLUMNS How to add plus one (+1) to a SQL Server column in a SQL Query Convert varchar dd/mm/yyyy to dd/mm/yyyy datetime How to update Identity Column in SQL Server? How to find difference between two columns data? Unpivot with column name Comma separated results in SQL How do I find ' % ' with the LIKE operator in SQL Server? Conditional WHERE clause in SQL Server Select query to remove non-numeric characters Combining (concatenating) date and time into a datetime how to convert date to a format `mm/dd/yyyy` How to write a foreach in SQL Server? how to know status of currently running jobs SQL variable to hold list of integers Temporary table in SQL server causing ' There is already an object named' error How to get current instance name from T-SQL SQL Server : Columns to Rows Table variable error: Must declare the scalar variable "@temp" Cannot execute script: Insufficient memory to continue the execution of the program SUM OVER PARTITION BY CROSS JOIN vs INNER JOIN in SQL How to convert number of minutes to hh:mm format in TSQL? Select value if condition in SQL Server Conversion failed when converting the varchar value to data type int in sql Get AVG ignoring Null or Zero values How can I use LEFT & RIGHT Functions in SQL to get last 3 characters?