How to Parse a Varchar Passed to a Stored Procedure in SQL Server (2023)

How to parse a VARCHAR passed to a stored procedure in SQL Server?

Another alternative is to use 'indirection' (as I've always called it)

You can then do..

create proc Sp_ReturnPrdoucts
@BrandIds varchar(500) = '6,7,8'
if (isnumeric(replace(@BrandIds,',',''))=1)
exec('SELECT * FROM tbl_Products as p join tbl_Brands b on p.ProductBrandId=b.BrandId WHERE b.BrandId IN ('+@BrandIds+')')

This way the select statement is built as a string, then executed.

I've now added validation to ensure that the string being passed in is purely numeric (after removing all the commas)

Stored procedure to parse a string

One possible solution is use XML

DECLARE @text VARCHAR(1000) 
,@xml xml

SELECT @text = 'City=Hyderabad | Mobile=48629387429 | Role=User | Name =Praveen'

SELECT @text = REPLACE(@text,'|','"')
,@text = REPLACE(@text,'=','="')
,@text = '<row ' + @text + '"/>'

SELECT @xml = CAST(@text AS XML)

line.col.value('@Name[1]', 'varchar(100)') AS Name
,line.col.value('@City[1]', 'varchar(100)') AS City
,line.col.value('@Mobile[1]', 'varchar(100)') AS Mobile
,line.col.value('@Role[1]', 'varchar(100)') AS Role
FROM @xml.nodes('/row') AS line(col)

Pass List of strings to a stored procedure

Convert the comma seperated value to table using the XML. Use this updated procedure.

USE [App]
/****** Object: StoredProcedure [dbo].[GetWorkspaceMapDetailsForUserByGroups]
Script Date: 16/02/2015 10:37:46 ******/
ALTER PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]
@workspaceID int,
@viewMap nvarchar(256)

GeoAppMapDef m
m.workspaceID = @workspaceID
and m.IsDeleted = 0
and m.ViewMap IN
Split.a.value('.', 'VARCHAR(100)') AS CVS
SELECT CAST ('<M>' + REPLACE(@viewMap, ',', '</M><M>') + '</M>' AS XML) AS CVS
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)

(Video) Stored procedures in sql server Part 18

How to convert varchar to integer in SQL Server stored procedure?

The numbers table solution I posted over on this question will be your most efficient solution. Print bullet before each sentence + new line after each sentence SQL will patch code in once I'm home


The base unit of work is the inline table-valued function. You might have heard about TVF and how they suck in SQL Server but that relates to the multi-statement types. Inlines are fine as the optimizer can make sense of them and not make terrible plans.

dbo.StringSplit returns a single column (varchar) table with the values split based on the supplied delimiter. You can cut down the lines of code required (derived tables L0 to L5) if you already have numbers table or a fast number generator in your data. I assume you don't. The technique of using a numbers table to split data is not mine but I trust the SQL luminaries who have done the analysis.

You asked for a proc so I have supplied dbo.StringSplitToInts to comply but all it's doing is calling the TVF with the proper parameters. You can extract the select statement and cast into inline code or wherever you need it.

-- This function splits a delimited string with good performance
-- characteristics
@input varchar(8000)
, @delimiter char(1) = ','
-- L0 to L5 simulate a numbers table
0 AS C
, L1 AS
0 AS c
, L2 AS
0 AS c
, L3 AS
0 AS c
, L4 AS
0 AS c
, L5 AS
0 AS c
, SOURCE_DATA (ID, content) AS
-- This query simulates your input data
-- This implementation could be simplified as our function
-- only accepts 1 row of data but this could be applied to
-- any category of problem, not just a single line of input
SELECT 1, @input
-- this query is rather important. The current NUMS query generates a
-- very large set of numbers but we only need 1 to maximum lenth of our
-- source data. We can take advantage of a 2008 feature of letting
-- TOP take a dynamic value
-- This query will make many lines out a single line based on the supplied delimiter
-- Need to retain the ID (or some unique value from original data to regroup it
, LTRIM(substring(SD.content, Number, charindex(@delimiter, SD.content + @delimiter, Number) - Number)) AS lines
Number <= len(SD.content)
AND substring(@delimiter + SD.content, Number, 1) = @delimiter

-- This is overkill as the function is more versatile but
-- in the spirit of delivering what was asked for, this proc
-- calls the function and casts the data to the appropriate type
CREATE PROCEDURE dbo.StringSplitToInts
@input varchar(8000)
, @delimiter char(1) = ','
CAST(SS.lines AS int) AS int_tokens
dbo.StringSplit(@input, @delimiter) SS


-- Over 9000!
EXECUTE dbo.StringSplitToInts '100,200,300,500,9000'

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)
  3. Now before calling the stored procedure fill the table

    (Video) Advanced SQL Tutorial | Stored Procedures + Use Cases

     declare @PC Prod_Code;
    insert @PC VALUES ('12012'), ('12011'), ('12014')
  4. Now Call the sp like this

     EXEC dbo.proc_aggregation @PC, @Prod_Desc;

Passing a varchar full of comma delimited values to a SQL Server IN function

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

(Number int NOT NULL,
DECLARE @x int
SET @x=0
WHILE @x<8000
SET @x=@x+1

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000) --REQUIRED, the list to split apart
@ParsedList table
ListValue varchar(500)

Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.

Returns a table, one row per item in the list, with a column name "ListValue"

SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')


(10 row(s) affected)


--SINGLE QUERY-- --this will not return empty rows
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''

(Video) SQL Server - Pass multiple values to singe parameter in stored procedure pass integers


END --Function FN_ListToTable

you can use this function as a table in a join:

Col1, COl2, Col3...
FROM YourTable
INNER JOIN FN_ListToTable(',',@YourString) s ON YourTable.ID = s.ListValue

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)

Related Topics

Select Columns with Particular Column Names in Postgresql

What Happens with Duplicates When Inserting Multiple Rows

Join Two Different Tables and Remove Duplicated Entries

Slick 3.0 - Update Columns in a Table and Return Whole Table Object

This SQL 'Order By' Is Not Working Properly

Determine the Size of a SQL Result Set in Kb

(Video) Pass an array into a SQL Server stored procedure - C#

MySQL Syntax Explanation

Natural Sort Supporting Big Numbers

Remove Ascii Extended Characters 128 Onwards (Sql)

Updating Row with Subquery Returning Multiple Rows

Sql: Syntax Error with Intersect

How to Check the Query Is Using Index

Simple Update Statement So That All Rows Are Assigned a Different Value

Put Pg_Try_Advisory_Xact_Lock() in a Nested Subquery

Select Top N Records Ordered by X, But Have Results in Reverse Order

SQL Server:Return Column Names Based on a Record's Value

Output Inserted.Id and Another Field

(Video) How To Pass More than one Parameter in Stored procedures in T SQL

Sql: Finding the Closest Lat/Lon Record on Google Bigquery


How to pass list of strings to stored procedure in SQL Server? ›

C# SQL Server - Passing a list to a stored procedure
  1. using (SqlConnection conn = new SqlConnection(connstring))
  2. {
  3. conn.Open();
  4. using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", conn))
  5. {
  6. cmd.CommandType = CommandType.StoredProcedure;
  7. var STableParameter = cmd.Parameters.AddWithValue("@QueryTable", QueryTable);

How to split string in SQL stored procedure? ›

The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument.

How do I get the content of a stored procedure in SQL Server? ›

Using SQL Server Management Studio

Expand Stored Procedures, right-click the procedure and then select Script Stored Procedure as, and then select one of the following: Create To, Alter To, or Drop and Create To. Select New Query Editor Window. This will display the procedure definition.

How fetch data from procedure in SQL? ›

SQL Server select from stored procedure with parameters
  1. First, create a stored procedure that uses multiple parameters to execute some task and return the result.
  2. Next, store the result returned by a stored procedure in a table variable.
  3. In the end, use the SELECT statement to fetch some data from the table variable.
Jul 13, 2021

How do I find text in all stored procedures in SQL Server? ›

To find stored procedures name which contain search text, write this query and execute.
  3. WHERE [text] LIKE '%type here your text%'
  4. AND OBJECTPROPERTY(id, 'IsProcedure') = 1.
Aug 22, 2016

How can I get specific data from string in SQL? ›

CHARINDEX function in SQL queries

The CHARINDEX() function returns the substring position inside the specified string. It works reverse to the SUBSTRING function. The substring() returns the string from the starting position however the CHARINDEX returns the substring position.

How to split varchar value in SQL Server? ›

How to Split a String by a Delimited Char in SQL Server?
  1. Use of STRING_SPLIT function to split the string.
  2. Create a user-defined table-valued function to split the string,
  3. Use XQuery to split the string value and transform a delimited string into XML.
Sep 6, 2020

What is Split_part in SQL? ›

The SPLIT_PART() function splits a specific string based on the specified delimiter and returns the appropriate string. The function starts from the left of the given string.

How do you split string values? ›

The split() method splits a string into an array of substrings. The split() method returns the new array. The split() method does not change the original string. If (" ") is used as separator, the string is split between words.

How to return value in stored procedure? ›

What is Return Value in SQL Server Stored Procedure?
  1. Right Click and select Execute Stored Procedure.
  2. If the procedure, expects parameters, provide the values and click OK.
  3. Along with the result that you expect, the stored procedure also returns a Return Value = 0.

How to print variable value in SQL Server stored procedure? ›

Usually, we use the SQL PRINT statement to print corresponding messages or track the variable values while query progress. We also use interactions or multiple loops in a query with a while or for a loop. We can also use the SQL PRINT statement to track the iteration.

How to return a list of values from stored procedure? ›

In order to fetch the multiple returned values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword. You can also make use of the Split function to split the comma separated (delimited) values into rows.

Can you query a stored procedure? ›

Click on your database and expand “Programmability” and right click on “Stored Procedures” or press CTRL+N to get new query window. You can write the SELECT query in between BEGIN and END to get select records from the table. Parameters are used to pass input values and return output values in store procedures.

Which methods retrieves all the data after executing a SQL query? ›

You can use an asterisk character, *, to retrieve all the columns. In queries where all the data is found in one table, the FROM clause is where we specify the name of the table from which to retrieve rows. In other articles we will use it to retrieve rows from multiple tables.

How to check data in stored procedure? ›

First, run SQL Server Management Studio and connect to the Database Engine. Next, under Object Explorer, expand the database in which you have created a procedure, and then expand “Programmability” option. Next, expand “Stored Procedures”, right-click the procedure you want and then select “View Dependencies” option.

How do I find a specific word from a string in SQL Server? ›

The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0. Note: This function performs a case-insensitive search.

How to find and replace text in all Stored Procedures SQL Server? ›

  1. Generate script of all stored procedures - You can use the scripting wizrd to generate the script. Right-click the db –> tasks –> Generate scripts –> go through the wizard. ...
  2. Generate an updated script - The same script is used to update all the eligible SP's with replace function.
  3. Create alias for linked servers.
Jan 15, 2017

How do I read a long stored procedure in SQL Server? ›

Stored procedure is a SQL statement which is precompiled and stored on the database server. A stored procedure can be created on the frequently used SQL script and saved on the database server with a name. The next time when that SQL script is needed to be executed, we just need to call that stored procedure.

How do I extract a specific character from a string? ›

The substr() method extracts a part of a string. The substr() method begins at a specified position, and returns a specified number of characters. The substr() method does not change the original string. To extract characters from the end of the string, use a negative start position.

How to select numeric values from a varchar column in SQL Server? ›

In SQL Server, we can use the ISNUMERIC() function to return numeric values from a column. We can alternatively run a separate query to return all values that contain numeric data.

How do you get a specific character from a string? ›

Java String charAt() Method

The charAt() method returns the character at the specified index in a string. The index of the first character is 0, the second character is 1, and so on.

How to parse string with delimiter in SQL? ›

SQL Split String by Delimiter
  1. SPLIT(value[, delimiter]) The function takes the string and the delimiter as the arguments. ...
  2. The function will split a string based on a comma delimiter by default. However, you must specify the target delimiter for bytes. ...
  3. SELECT. SPLIT('a b c d e f g', ' ') AS arr; ...
  4. arr. ...
  5. SELECT. ...
  6. arr. ...
  7. SELECT. ...
  8. arr.

How do I split a string with multiple delimiters in SQL? ›

Split String Lists Using Multiple Delimiters
  1. create function dbo.fn_generate_numbers.
  2. (@numrows int)
  3. returns @returntable table (rownum int primary key)
  4. as.
  5. begin.
  6. declare @idt int.
  7. set @idt = 0.
  8. while (@idt < @numrows)
Mar 16, 2015

What does varchar 255 mean in SQL? ›

VARCHAR(255) stores 255 characters, which may be more than 255 bytes.

What is the use of %% in SQL? ›

The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

What does Substring_index do in SQL? ›

Definition and Usage

The SUBSTRING_INDEX() function returns a substring of a string before a specified number of delimiter occurs.

What is Patindex function in SQL? ›

The PATINDEX() function returns the position of a pattern in a string. If the pattern is not found, this function returns 0. Note: The search is case-insensitive and the first position in string is 1.

How would you read a string and split it into substring? ›

Use the Split method when the substrings you want are separated by a known delimiting character (or characters). Regular expressions are useful when the string conforms to a fixed pattern. Use the IndexOf and Substring methods in conjunction when you don't want to extract all of the substrings in a string.

How do I split a string into multiple strings? ›

split() The method split() splits a String into multiple Strings given the delimiter that separates them. The returned object is an array which contains the split Strings. We can also pass a limit to the number of elements in the returned array.

How do I split a string into n number of substrings? ›

  1. str = "aaaabbbbcccc";
  2. #Stores the length of the string.
  3. length = len(str);
  4. #n determines the variable that divide the string in 'n' equal parts.
  5. n = 3;
  6. temp = 0;
  7. chars = int(length/n);
  8. #Stores the array of string.

How can I get return values and output values from a stored procedure with EF core? ›

Get a SQL Server stored procedure return value with EF Core
  1. var parameterReturn = new SqlParameter { ParameterName = "ReturnValue", SqlDbType = System.Data.SqlDbType.Int, Direction = System. Data. ...
  2. var result = db. ...
  3. var procs = new NorthwindContextProcedures(db); var returned = new OutputParameter<int>(); await procs.
Nov 2, 2020

What is the difference between output parameter and return value in stored procedure? ›

Generally, use an output parameter for anything that needs to be returned. When you want to return only one item with only an integer data type then it is better to use a return value. Generally, the return value is only to inform the success or failure of the Stored Procedure.

How do you return a Boolean value from a stored procedure in SQL Server? ›

You can't. There is no boolean datatype and the procedure return code can only be an int . You can return a bit as an output parameter though. Depending on what you're doing you could use a function instead.

How to assign value to variable in stored procedure? ›

Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.

How to print varchar in SQL? ›

How to print VARCHAR(MAX) using Print Statement?
  2. SELECT @Script = definition FROM manged.sys.all_sql_modules sq.
  3. where sq.object_id = (SELECT object_id from managed.sys.objects.
  4. Where type = 'P' and Name = 'usp_gen_data')
  5. Declare @Pos int.
  6. SELECT @pos=CHARINDEX(CHAR(13)+CHAR(10),@script,7500)
Jul 30, 2019

How do you print a value stored in a variable? ›

You first include the character f before the opening and closing quotation marks, inside the print() function. To print a variable with a string in one line, you again include the character f in the same place – right before the quotation marks.

Can stored procedure return value in SQL Server? ›

Return Value in SQL Server Stored Procedure

In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.

Can we return multiple values from stored procedure in SQL? ›

Yes its can return multiple value from sproc. For that you need to declare multiple output parameter in stored procedure.

How many values can be returned from a stored procedure *? ›

A stored function can return only one value, unlike a stored procedure, which can return multiple values or an entire result set.

Can we pass a list to a stored procedure in SQL Server? ›

There are several ways to do this. While using older versions of SQL Server, I've used to the XML method to pass array or list to stored procedure. In the latest versions of SQL Server, we can use the User Defined Data Type (UDT) with a base type of table to send array or list through a parameter.

How do I pass multiple records to a stored procedure? ›

We can pass a Table containing multiple records (rows) to a Stored Procedure by making use of Table Valued parameters in SQL Server. I have created a simple table named Customers whose schema is shown below. Once created the User Defined Type will be visible in the Object Explorer as shown below.

How to pass string array as parameter in SQL stored procedure? ›

You can convert your array to string in C# and pass it as a Stored Procedure parameter as below, int[] intarray = { 1, 2, 3, 4, 5 }; string[] result = intarray. Select(x=>x. ToString()).

How do I store a list of strings? ›

To do this we use the split() method in string. The split method is used to split the strings and store them in the list. The built-in method returns a list of the words in the string, using the “delimiter” as the delimiter string.

How do you pass a list of values to a parameter of a stored procedure? ›

CREATE FUNCTION dbo. SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>'). query('.

How do I pass multiple values to a single variable in SQL stored procedure? ›

There's no way to make an NVARCHAR parameter take "more than one value". What I've done before is - as you do already - make the parameter value like a list with comma-separated values. Then, split this string up into its parts in the stored procedure.

How can I get multiple result sets from a stored procedure in SQL Server? ›

In order to get multiple result sets working we need to drop to the ObjectContext API by using the IObjectContextAdapter interface. Once we have an ObjectContext then we can use the Translate method to translate the results of our stored procedure into entities that can be tracked and used in EF as normal.

How do you get the output of a stored procedure in a variable in SQL Server? ›

You can use the return statement inside a stored procedure to return an integer status code (and only of integer type). By convention a return value of zero is used for success. If no return is explicitly set, then the stored procedure returns zero. You should use the return value for status codes only.

Can we pass table variable as parameter to stored procedure? ›

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

How do you pass a value as a parameter in SQL query? ›

How to Pass Parameters to SQL Queries – Method 1
  1. Create the Staging query. Connect to the raw database table. ...
  2. Create the parameter table and the fnGetParameter query.
  3. Create a query that references the Staging query and filters the department to the one pulled via the fnGetParameter query.
Apr 28, 2016

What is the difference between list and array? ›

List is used to collect items that usually consist of elements of multiple data types. An array is also a vital component that collects several items of the same data type. List cannot manage arithmetic operations. Array can manage arithmetic operations.

How do I find a string in a list of strings? ›

You can use the index() method to find the first index of a string in a list. If the string is present in the list, the index() method returns the first index of the string, otherwise it raises a ValueError.

How do you split a string and store it in a list? ›

The split() method splits a string into a list. You can specify the separator, default separator is any whitespace. Note: When maxsplit is specified, the list will contain the specified number of elements plus one.


1. Passing a List of Values to a Stored Procedure | C# | ADO.NET | ASP.NET Core 6
2. how to pass datetime parameter to stored procedure in sql server 2008
(Haritha Computers & Technology)
3. Stored Procedure with dynamic parameters sql server
4. How to Write Stored Procedure in SQL Server for Beginners
(Kindson The Tech Pro)
5. Part4 : Database Testing | Stored Procedure Testing | How To Develop and Test Stored Procedures
(SDET- QA Automation Techie)
6. PowerApps - Pass string array to SQL Stored Procedure from PowerApps using Flow (Tutorial)
(Dave's Tech Tips)
Top Articles
Latest Posts
Article information

Author: Msgr. Refugio Daniel

Last Updated: 06/04/2023

Views: 6078

Rating: 4.3 / 5 (74 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Msgr. Refugio Daniel

Birthday: 1999-09-15

Address: 8416 Beatty Center, Derekfort, VA 72092-0500

Phone: +6838967160603

Job: Mining Executive

Hobby: Woodworking, Knitting, Fishing, Coffee roasting, Kayaking, Horseback riding, Kite flying

Introduction: My name is Msgr. Refugio Daniel, I am a fine, precious, encouraging, calm, glamorous, vivacious, friendly person who loves writing and wants to share my knowledge and understanding with you.