{"id":495,"date":"2015-05-14T09:36:40","date_gmt":"2015-05-14T14:36:40","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=495"},"modified":"2015-05-17T10:47:33","modified_gmt":"2015-05-17T15:47:33","slug":"passing-multiple-values-into-a-variable","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/passing-multiple-values-into-a-variable\/","title":{"rendered":"Passing Multiple Values into a Variable"},"content":{"rendered":"<p>Passing multiple values into a variable is a little more difficult than it should be. In other languages you can use functions such as Lists or Arrays, but SQL makes it a bit more complicated. To show you what I mean, let\u2019s look at an example.<\/p>\n<p>First, let\u2019s create a Demo table:<\/p>\n<div>\n<pre>CREATE TABLE [dbo].[Demo](\r\n[ID] [int] NULL,\r\n[Name] [varchar](50) NULL,\r\n[Address] [varchar](50) NULL,\r\n[State] [varchar](50) NULL,\r\n[Zip] [int] NULL\r\n)<\/pre>\n<\/div>\n<p>Next, populate it with some data:<\/p>\n<div>\n<pre>INSERT INTO [dbo].[Demo]\r\nVALUES (1, 'Brady', '123 Main Street', 'TN', 12345)<\/pre>\n<\/div>\n<p>&nbsp;<\/p>\n<div>\n<pre>INSERT INTO [dbo].[Demo]\r\nVALUES (2, 'Tommy', '124 Main Street', 'TN', 12345)<\/pre>\n<\/div>\n<p>&nbsp;<\/p>\n<div>\n<pre>INSERT INTO [dbo].[Demo]\r\nVALUES (3, 'Jonny', '125 Main Street', 'TN', 12345)<\/pre>\n<\/div>\n<p>Now that we have some data, let\u2019s try a query using variables. I want to define a variable on the column ID.<\/p>\n<div>\n<pre>DECLARE @MultipleValue varchar(200)\r\nSET @MultipleValue = '1,2'\r\n\r\nSELECT * FROM Demo WHERE ID IN (@MultipleValue)<\/pre>\n<\/div>\n<p>After running this query, I get 0 results and an error:<\/p>\n<p>Msg 245, Level 16, State 1, Line 24<br \/>\nConversion failed when converting the varchar value &#8216;1,2&#8217; to data type int.<\/p>\n<p>Why? I know the ID\u2019s 1 and 2 are in the table, but SQL is looking at this variable as one string. So unless I have 1,2 in the same ID column, it will show 0 results.<\/p>\n<p>One way to get around this is to use a UDF, user defined function. In this function, we\u2019re going to convert the comma separated values (1,2) into a table, then query from that.<\/p>\n<div>\n<pre>CREATE FUNCTION [dbo].[MultipleValues] (@InStr VARCHAR(MAX))\r\nRETURNS @TempTable TABLE\r\n(id int not null)\r\nAS\r\nBEGIN\r\n\r\nSET @InStr = REPLACE(@InStr + ',', ',,', ',')\r\nDECLARE @SP INT\r\nDECLARE @VALUE VARCHAR(1000)\r\nWHILE PATINDEX('%,%', @INSTR ) &lt;&gt; 0\r\n\r\nBEGIN\r\n\r\nSELECT @SP = PATINDEX('%,%',@INSTR)\r\nSELECT @VALUE = LEFT(@INSTR , @SP - 1)\r\nSELECT @INSTR = STUFF(@INSTR, 1, @SP, '')\r\nINSERT INTO @TempTable(id) VALUES (@VALUE)\r\nEND\r\nRETURN\r\nEND\r\nGO<\/pre>\n<\/div>\n<p>Now that we have a UDF, let\u2019s use this in the query:<\/p>\n<div>\n<pre>DECLARE @MultipleValue varchar(200)\r\nSET @MultipleValue = '1,2'\r\n\r\nSELECT * FROM Demo WHERE ID IN (SELECT * FROM dbo.MultipleValues(@MultipleValue))<\/pre>\n<\/div>\n<p>Ta da!<\/p>\n<p>We now have two results. ID 1 and 2:<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2015\/05\/Passing-multiple-values-into-a-variable.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-497\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2015\/05\/Passing-multiple-values-into-a-variable.png\" alt=\"Passing multiple values into a variable\" width=\"306\" height=\"91\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2015\/05\/Passing-multiple-values-into-a-variable.png 306w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2015\/05\/Passing-multiple-values-into-a-variable-300x89.png 300w\" sizes=\"auto, (max-width: 306px) 100vw, 306px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Passing multiple values into a variable is a little more difficult than it should be. In other languages you can use functions such as Lists or Arrays, but SQL makes it a bit more complicated. To show you what I mean, let\u2019s look at an example. First, let\u2019s create a Demo table: CREATE TABLE [dbo].[Demo]( [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[94],"tags":[59,40],"class_list":["post-495","post","type-post","status-publish","format-standard","hentry","category-development","tag-development","tag-tsql"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/495","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=495"}],"version-history":[{"count":5,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/495\/revisions"}],"predecessor-version":[{"id":502,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/495\/revisions\/502"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=495"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}