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:
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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:
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.
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