Is it possible in SQL to use a variable to store query. For example to save time when subquery is used multiple times inside the main query.
Example:
DECLARE @my_query as varchar(250) = select x from my_table where my_table = y.your_table
SELECT
a,b,c,(@my_query),d,e,f
FROM my_table_1
CodePudding user response：
Is it possible in SQL to use a variable to store query.
Depend on your definition of "query". If you mean store the text which we use to execute the command, then the answer is YES. If you mean an object type query, then the answer is not - since there is no data type that fit this.
What I mean is that a variable can store a value which is string. The string can be any query command that you want. Therefore, you can store for example the text "select col1,col2 from table1".
Next you need to ask how can we use this text in order to execute it as part of a query, which is done using dynamic query.
We can execute a text of a query using the build-in stored procedure sp_executesql, which is build for such needs.
For example:
-- DECLARE VARIABLE
DECLARE @MyQuery NVARCHAR(MAX)
-- SET the value of the variable
SET @MyQuery = 'SELECT ''Yes I can'''
-- Executing a dynamic query
EXECUTE sp_executesql @MyQuery
Here is another example which look more close to your question:
-- First let's create a table
CREATE TABLE T(ID INT)
INSERT T(ID) VALUES (1),(2)
GO
-- And here is what you sked about:
-- DECLARE VARIABLE
DECLARE @MyQuery NVARCHAR(MAX)
-- SET the value of the variable
SET @MyQuery = 'select ID from T where ID = ''1'''
-- Let's combine the text to a full query now
DECLARE @FullQuery NVARCHAR(MAX)
SET @FullQuery = '
SELECT
ID,(' @MyQuery ')
FROM T
'
PRINT @FullQuery
-- Executing a dynamic query
EXECUTE sp_executesql @FullQuery
NOTE! Your specific sample of query will return error, which is not related to the question "Is it possible in SQL to use a variable to store query". This is a result of the "query" is not well formatted.
Important! It is HIGHLY recommended to read the document about this stored procedure and learn a bit more of the options it provides us.