can I declare a variable after the query

Jonathan Brotto 420 Reputation points
2024-08-08T15:13:00.9633333+00:00

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.

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,999 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
102 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,656 questions
SQL Server Training
SQL Server Training
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Training: Instruction to develop new skills.
15 questions
{count} votes

Accepted answer
  1. Viorel 118K Reputation points
    2024-08-08T15:31:05.74+00:00

    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:
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Michael Taylor 54,811 Reputation points
    2024-08-08T15:29:28.8633333+00:00

    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.


  2. Erland Sommarskog 112.7K Reputation points MVP
    2024-08-08T21:34:32.34+00:00

    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
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.