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'
AS
BEGIN
if (isnumeric(replace(@BrandIds,',',''))=1)
begin
exec('SELECT * FROM tbl_Products as p join tbl_Brands b on p.ProductBrandId=b.BrandId WHERE b.BrandId IN ('+@BrandIds+')')
end
END
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 xmlSELECT @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)
select
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]
GO
/****** Object: StoredProcedure [dbo].[GetWorkspaceMapDetailsForUserByGroups]
Script Date: 16/02/2015 10:37:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]
@workspaceID int,
@viewMap nvarchar(256)
AS SELECT
m.*
FROM
GeoAppMapDef m
WHERE
m.workspaceID = @workspaceID
and m.IsDeleted = 0
and m.ViewMap IN
(
SELECT
Split.a.value('.', 'VARCHAR(100)') AS CVS
FROM
(
SELECT CAST ('<M>' + REPLACE(@viewMap, ',', '</M><M>') + '</M>' AS XML) AS CVS
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
)
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
Edit
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
CREATE FUNCTION dbo.StringSplit
(
@input varchar(8000)
, @delimiter char(1) = ','
)
RETURNS
table
RETURN
-- L0 to L5 simulate a numbers table
-- http://billfellows.blogspot.com/2009/11/fast-number-generator.html
WITH L0 AS
(
SELECT
0 AS C
UNION ALL
SELECT
0
)
, L1 AS
(
SELECT
0 AS c
FROM
L0 AS A
CROSS JOIN L0 AS B
)
, L2 AS
(
SELECT
0 AS c
FROM
L1 AS A
CROSS JOIN L1 AS B
)
, L3 AS
(
SELECT
0 AS c
FROM
L2 AS A
CROSS JOIN L2 AS B
)
, L4 AS
(
SELECT
0 AS c
FROM
L3 AS A
CROSS JOIN L3 AS B
)
, L5 AS
(
SELECT
0 AS c
FROM
L4 AS A
CROSS JOIN L4 AS B
)
, NUMS AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
FROM
L5
)
, 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
)
, MAX_LENGTH AS
(
-- 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
SELECT TOP (SELECT MAX(LEN(SD.content)) AS max_length FROM SOURCE_DATA SD)
N.number
FROM
NUMS N
)
, MULTI_LINES AS
(
-- 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
-- http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum
SELECT
SD.ID
, LTRIM(substring(SD.content, Number, charindex(@delimiter, SD.content + @delimiter, Number) - Number)) AS lines
FROM
MAX_LENGTH
CROSS APPLY
SOURCE_DATA SD
WHERE
Number <= len(SD.content)
AND substring(@delimiter + SD.content, Number, 1) = @delimiter
)
SELECT
ML.lines
FROM
MULTI_LINES ML
GO-- 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) = ','
)
AS
BEGIN
SET NOCOUNT ON
SELECT
CAST(SS.lines AS int) AS int_tokens
FROM
dbo.StringSplit(@input, @delimiter) SS
END
GO
-- 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
Define new type as follows
CREATE TYPE Prod_Code AS TABLE ( ProductCode varchar );then use this type in your stored procedure
create procedure [dbo].[proc_aggregation]
@Prod_Code Prod_Code READONLY,
@Prod_Desc varchar (30)
as
......Now before calling the stored procedure fill the table
declare @PC Prod_Code;
insert @PC VALUES ('12012'), ('12011'), ('12014')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:
CREATE TABLE Numbers
(Number int NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END
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
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN/**
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"
EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')
returns:
ListValue
-----------
1
12
123
1234
54321
6
A
*
|||
B
(10 row(s) affected)
**/
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
(ListValue)
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
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!=''
RETURN
END --Function FN_ListToTable
you can use this function as a table in a join:
SELECT
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
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
Sql: Finding the Closest Lat/Lon Record on Google Bigquery
FAQs
How to pass list of strings to stored procedure in SQL Server? ›
- using (SqlConnection conn = new SqlConnection(connstring))
- {
- conn.Open();
- using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", conn))
- {
- cmd.CommandType = CommandType.StoredProcedure;
- var STableParameter = cmd.Parameters.AddWithValue("@QueryTable", QueryTable);
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.
- First, create a stored procedure that uses multiple parameters to execute some task and return the result.
- Next, store the result returned by a stored procedure in a table variable.
- In the end, use the SELECT statement to fetch some data from the table variable.
- SELECT OBJECT_NAME(id)
- FROM SYSCOMMENTS.
- WHERE [text] LIKE '%type here your text%'
- AND OBJECTPROPERTY(id, 'IsProcedure') = 1.
- GROUP BY OBJECT_NAME(id)
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.
- Use of STRING_SPLIT function to split the string.
- Create a user-defined table-valued function to split the string,
- Use XQuery to split the string value and transform a delimited string into XML.
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? ›- Right Click and select Execute Stored Procedure.
- If the procedure, expects parameters, provide the values and click OK.
- 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? ›- 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. ...
- Generate an updated script - The same script is used to update all the eligible SP's with replace function.
- Create alias for linked servers.
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.
- SPLIT(value[, delimiter]) The function takes the string and the delimiter as the arguments. ...
- The function will split a string based on a comma delimiter by default. However, you must specify the target delimiter for bytes. ...
- SELECT. SPLIT('a b c d e f g', ' ') AS arr; ...
- arr. ...
- SELECT. ...
- arr. ...
- SELECT. ...
- arr.
- create function dbo.fn_generate_numbers.
- (@numrows int)
- returns @returntable table (rownum int primary key)
- as.
- begin.
- declare @idt int.
- set @idt = 0.
- while (@idt < @numrows)
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.
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? ›- str = "aaaabbbbcccc";
- #Stores the length of the string.
- length = len(str);
- #n determines the variable that divide the string in 'n' equal parts.
- n = 3;
- temp = 0;
- chars = int(length/n);
- #Stores the array of string.
How can I get return values and output values from a stored procedure with EF core? ›
- var parameterReturn = new SqlParameter { ParameterName = "ReturnValue", SqlDbType = System.Data.SqlDbType.Int, Direction = System. Data. ...
- var result = db. ...
- var procs = new NorthwindContextProcedures(db); var returned = new OutputParameter<int>(); await procs.
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? ›- DECLARE @Script VARCHAR(MAX)
- SELECT @Script = definition FROM manged.sys.all_sql_modules sq.
- where sq.object_id = (SELECT object_id from managed.sys.objects.
- Where type = 'P' and Name = 'usp_gen_data')
- Declare @Pos int.
- SELECT @pos=CHARINDEX(CHAR(13)+CHAR(10),@script,7500)
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.
Yes its possible..you 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? ›- Create the Staging query. Connect to the raw database table. ...
- Create the parameter table and the fnGetParameter query.
- Create a query that references the Staging query and filters the department to the one pulled via the fnGetParameter query.
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.