This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(294.40000000000003, 82%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJB%3C/text%3E%3C/svg%3E)
I have a variable that is a list that I will split and but not anting to put it on top of the SQL file as I like to see my query before.
Do you have further question on this issue?
If any answer is helpful to your question, please accept it as an answer so that other users with similar questions can see it more easily. :)
Maybe like this:
declare @InvNumber varchar(max) goto myvariables myqueries:
-- THE QUERIES
select *
from . . .
--
goto done myvariables:
-- THE VALUES
set @InvNumber = '00272-DF4,01664-QF4,......'
goto myqueries done:
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 32%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
In T-SQL, like most programming languages, you can declare a variable anywhere in your code. If you want to block declare variables at the top or declare before usage is up to you.
Note that, like most programming languages, a variable's scope is the point of declaration until the end of the block that contains it. So you must declare the variable before you can use it in queries. In your sample, @InvNumber must be declared before it can be used in the WHERE clause.
I know it is a sample but personally I would recommend you use a table variable instead of a string variable and just add your values to it. Then use a join to find the matching rows. Your first query, as given, wouldn't work since @InvNumber is a list of values. You also cannot use an IN operator with it because IN requires a table structure. Of course you can use STRING_SPLIT to convert a string to a table if you're using a later version of SQL.
@Michael Taylor what is the difference between a table and a string variable?
@Jonathan Brotto You can read about table variables here. Basically they are lightweight temp tables that require less management to get working. They are useful when you need a table structure inside a script and aren't worried about performance issues (open for debate here). A string stores a single scalar value but a table variable can hold rows of data just like a regular table. They are otherwise equivalent to tables.
DECLARE @invoiceNumbers AS TABLE (Value AS VARCHAR(20))
INSERT INTO @invoiceNumbers VALUES ('123'), ('456')
SELECT * FROM SA15B t JOIN @invoiceNumbers s on s.Value = t.InvoiceSuffix
Of course if you already have a string with all the values then it is just easier to use STRING_SPLIT if your SQL version supports it.
SELECT * FROM SA15B t JOIN STRING_SPLIT(@invNumber, ',') s ON s.value = t.InvolceSuffix
Note that the above code doesn't take trimming of string values into account which may or may not matter for you.
Trying to read the between the lines, I guess this what you want:
DECLARE @str varchar(MAX) = 'A,B,C'
SELECT * FROM tbl WHERE col IN (SELECT value FROM string_split(@str, ','))
Note that this is not meaningful:
SELECT * FROM tbl WHERE col IN (@str)
This is because
SELECT * FROM tbl WHERE col IN ('A', 'B', 'C')
is just a syntactic shortcut for
SELECT * FROM tbl WHERE col = 'A' OR col = 'B' OR col = 'C'
so
SELECT * FROM tbl WHERE col IN (@str)
is the same as
SELECT * FROM tbl WHERE col = @str