Сохранение результатов существующего запроса в переменную

ProgramBox

Сохранение результатов существующего запроса в переменную

Post author:admin
Запись опубликована:7 февраля, 2023
Post category:Вопросы по программированию

#sql #sql-server

#sql #sql-сервер

Вопрос:

Я хочу сохранить результат этого EXISTS запроса в переменную:

 DECLARE @max_date DATETIME2
DECLARE @DatabaseSchema VARCHAR(MAX)

SET @DatabaseSchema = 'SALES_MART'
SET @max_date = CASE 
                   WHEN EXISTS(SELECT MAX(SaleDate)
                               FROM  @DatabaseSchema.SalesReport
                               WHERE Categoryname = 'Asia')
                      THEN (SELECT MAX(SaleDate)
                            FROM  @DatabaseSchema.SalesReport
                            WHERE Categoryname = 'Asia')
                      ELSE GETDATE()
                END

EXEC @max_date

Когда я его выполняю, я получаю сообщение об ошибке

недопустимое имя столбца @DatabaseName.Отчет о продажах

Причина ошибки в том, что SQL Server не принимает переменную после FROM .

Я погуглил и попытался удалить «СЛУЧАЙ, КОГДА», но он все еще не работает. Не могли бы вы помочь мне с этим запросом? Как мне его отредактировать?

Это моя тестовая таблица:

 CREATE SCHEMA SALES_MART;

CREATE TABLE SALES_MART.SalesReport  
( 
     id INT,
     SaleDate DATETIME2,
     sales INT,
     Categoryname VARCHAR(100)
);

INSERT INTO SALES_MART.SalesReport  
VALUES (1, '1905-07-08 00:00:00.000', 50, 'Asia'),
       (2, '1905-07-08 00:00:00.000', 90, 'Asia'),
       (3, '1905-07-08 00:00:00.000', 100, 'EU');

Ответ №1:

Предполагая, что вы используете SSMS в качестве IDE, вы можете просто включить командный режим Sql, а затем получить значение динамической схемы. Попробуйте следующий код:

 :SetVar DatabaseSchema "SALES_MART"

CREATE SCHEMA $(DatabaseSchema);
GO

CREATE TABLE $(DatabaseSchema).SalesReport  
( 
     id INT,
     SaleDate DATETIME2,
     sales INT,
     Categoryname VARCHAR(100)
);
GO

INSERT INTO $(DatabaseSchema).SalesReport  
VALUES (1, '1905-07-08 00:00:00.000', 50, 'Asia'),
       (2, '1905-07-08 00:00:00.000', 90, 'Asia'),
       (3, '1905-07-08 00:00:00.000', 100, 'EU');

DECLARE @max_date DATETIME2

SET @max_date = CASE 
                   WHEN EXISTS(SELECT MAX(SaleDate)
                               FROM  $(DatabaseSchema).SalesReport
                               WHERE Categoryname = 'Asia')
                      THEN (SELECT MAX(SaleDate)
                            FROM  $(DatabaseSchema).SalesReport
                            WHERE Categoryname = 'Asia')
                      ELSE GETDATE()
                END

PRINT @max_date

-- Clean up
DROP TABLE $(DatabaseSchema).SalesReport;
GO

DROP SCHEMA $(DatabaseSchema);
GO

Ответ №2:

Если вы хотите использовать параметр для имени вашей схемы, вам нужно будет использовать динамический SQL. Он преобразует вашу строку в запрос, поэтому вы можете объединить свой строковый параметр со строковым запросом, который вы написали. Вы также можете вывести значение в параметр.

Я упростил ваш запрос, используя coalesce. Он принимает первое значение, если оно не равно NULL, и второе, если первое значение равно NULL

 Declare @DatabaseSchema varchar(max)
SET @DatabaseSchema ='SALES_MART'
DECLARE @MaxDate DATETIME
DECLARE @Query NVARCHAR(MAX) = 'SELECT COALESCE((SELECT max(SaleDate) FROM '  @DatabaseSchema   '.SalesReport WHERE Categoryname = ''Asia''), GETDATE())' 
EXECUTE sp_executesql @Query, N'@Output DATETIME OUT', @MaxDate OUT
PRINT @MaxDate

1. Спасибо, Тим. Я много читал о динамическом запросе с помощью sp executesql, но до сих пор не понимаю, как его использовать. В приведенном выше запросе не могли бы вы объяснить, почему вы задаете вывод следующим образом: N’@Output DATETIME OUT’, @maxDate OUT . Я действительно не понимаю

2. sp_executesql позволяет выводить значения из выполняемого запроса. У вас может быть столько пар, сколько вы хотите. Первые параметры (в данном случае выходные) существуют в контексте запроса, поэтому вы добавляете их как один nvarchar (отсюда N в начале) и разделяете параметры внутри этой строки запятой вместе с их типами. maxDate существует в вашем основном контексте с тех пор, как вы объявили его в начале. Связывая его с выводом, вы присваиваете значение переменной, существующей в нужном вам контексте

3. Динамический SQL опасен, поскольку он делает ваши входные данные антисанитарными, поэтому вы никогда не должны использовать его в своих веб-проектах. Параметризованный запрос не просто заменяет строку, как это делается в динамическом SQL. Оптимизатор сначала устанавливает запрос, а затем разрешает параметры только там, где их можно сравнивать буквально. Таким образом, параметр не может расширить запрос и внести что-либо вредоносное. Динамический SQL просто берет всю вашу строку и преобразует в запрос, поэтому, если параметр добавляет что-либо вредоносное, что изменяет цель запроса, он будет выполнен.

Ответ №3:

Вот исправленный код, который сохраняет результат в переменной @max_date Последний выбор предназначен для отображения результата

     CREATE SCHEMA SALES_MART;
    go
    create table SALES_MART.SaleReport  (id int,SaleDate datetime2,sales int,Categoryname varchar(100));
    INSERT INTO SALES_MART.SaleReport  
    VALUES
    (1,'1905-07-08 00:00:00.000',50,'Asia'),
    (2,'1905-07-08 00:00:00.000',90,'Asia'),
    (3,'1905-07-08 00:00:00.000',100,'EU');


     Declare @max_date  datetime2
     Declare @DatabaseSchema varchar(max)


        SElect   @max_date=CASE WHEN EXISTS(SELECT max(SaleDate)
                                            FROM  SALES_MART.SaleReport 
                                            WHERE Categoryname = 'Asia')
                             THEN (SELECT max(SaleDate)
                                            FROM  SALES_MART.SaleReport 
                                            WHERE Categoryname = 'Asia' )
                            ELSE getdate()  END

    select @max_date [result in varaiable]


[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/ImC1o.png







**ANOTHER SOLUTION WITH DYNAMIC SCRIPT**
This solution can be used for many schemas, it inserts the result (with the schema name and max date) in Tbale mytable


CREATE SCHEMA SALES_MART;
go
create table SALES_MART.SaleReport  (id int,SaleDate datetime2,sales int,Categoryname varchar(100));
INSERT INTO SALES_MART.SaleReport  
VALUES
(1,'1905-07-08 00:00:00.000',50,'Asia'),
(2,'1905-07-08 00:00:00.000',90,'Asia'),
(3,'1905-07-08 00:00:00.000',100,'EU');


 Declare @max_date  datetime2
 Declare @DatabaseSchema varchar(max)
 set @DatabaseSchema='SALES_MART'
 declare @script as varchar(max)

 create table mytable(myschema varchar(50),maxdate date)
 delete from mytable
 set @script='
    insert into mytable (myschema,maxdate)
    select ''' @DatabaseSchema ''',
    CASE WHEN EXISTS(SELECT max(SaleDate)
                                        FROM  ' @DatabaseSchema '.SaleReport 
                                        WHERE Categoryname = ''Asia'')
                         THEN (SELECT max(SaleDate)
                                        FROM  SALES_MART.SaleReport 
                                        WHERE Categoryname = ''Asia'' )
                        ELSE getdate()  END'

exec(@script)

select * from mytable

1. Спасибо. Но я хочу использовать переменную для databaseSchema после предложения FROM, потому что я хочу сделать запрос динамическим

2. В таком случае вам необходимо использовать динамический скрипт, сохранить скрипт в переменной и выполнить его

3. Спасибо, Кемаль, я это увижу

Ответ №4:

переменные не разрешены в именах серверов, схем и таблиц, а также в нескольких других местах (например, в ALTER, CREATE, DROP .. etc).

В вашем случае вы пытаетесь использовать его в имени схемы, чтобы использовать его таким образом, у вас будет два варианта.

Вариант 1:

Определите каждый оператор select и используйте IF, чтобы получить оператор select, который вы хотите, на основе ввода переменной.

Пример :

 DECLARE
    @max_date       DATETIME2
,   @DatabaseSchema VARCHAR(max) = 'SALES_MART'

SET @max_date = CASE @DatabaseSchema
                    WHEN 'SALES_MART'       THEN (SELECT MAX(SaleDate) FROM SALES_MART.SalesReport WHERE Categoryname = 'Asia')
                    WHEN 'SALES_MART_TWO'   THEN (SELECT MAX(SaleDate) FROM SALES_MART_TWO.SalesReport WHERE Categoryname = 'Asia')
                    ELSE GETDATE()
                END 

SET @max_date = CASE WHEN @max_date IS NULL THEN GETDATE() ELSE @max_date END -- You can replace it with ISNULL(@max_date, GETDATE()) 

SELECT @max_date

Вариант 2 (не всегда рекомендуется, поскольку он имеет свои риски):

Вместо этого используйте daynamic sql.

 DECLARE
    @max_date       DATETIME2
,   @DatabaseSchema VARCHAR(max) = 'SALES_MART'
,   @sql            VARCHAR(max) = ''

    SET @sql = 'ISNULL( (SELECT MAX(SaleDate) FROM @DatabaseSchema.SalesReport WHERE Categoryname = ''Asia''), GETDATE())'

    SET @sql = REPLACE(@sql, '@DatabaseSchema',  @DatabaseSchema)

    EXEC (@sql)