3
votes

Enregistrer les résultats de la requête existe dans une variable

Je souhaite enregistrer le résultat de cette requête EXISTS dans une variable:

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');

Lorsque je l'exécute, j'ai une erreur

nom de colonne non valide @ DatabaseName.SalesReport

La raison de l'erreur est que SQL Server n'accepte pas de variable après FROM.

J'ai cherché sur Google et essayé de supprimer 'CASE WHEN' mais il ne fonctionne toujours pas. Pouvez-vous s'il vous plaît m'aider avec cette requête? Comment dois-je le modifier?

Voici ma table de test:

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

sql sql-server

1 commentaires

Avez-vous déjà entendu parler du SQL dynamique?

4 Réponses :

0
votes

Voici le code corrigé, qui stocke le résultat dans la variable @max_date La dernière sélection est pour afficher le résultat

    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

3 commentaires

Je vous remercie. Mais je veux utiliser une variable pour databaseSchema après la clause FROM car je veux rendre la requête dynamique

Dans ce cas, vous devez utiliser un script dynamique, stocker le script dans une variable et l'exécuter

Merci Kemal, je vais le voir

0
votes

Si vous souhaitez utiliser un paramètre pour le nom de votre schéma, vous devrez utiliser du SQL dynamique. Il convertit votre chaîne en requête, vous pouvez donc concaténer votre paramètre de chaîne avec une requête de chaîne que vous avez écrite. Vous pouvez également envoyer la valeur à un paramètre.

J'ai simplifié votre requête en utilisant coalesce. Il prend la première valeur sinon NULL et la seconde si la première est 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

3 commentaires

Merci Tim. J'ai beaucoup lu sur la requête dynamique avec sp executesql, mais jusqu'à présent, je ne sais pas comment l'utiliser. Dans votre requête ci-dessus, pouvez-vous expliquer pourquoi vous définissez la sortie comme ceci: N '@ Output DATETIME OUT', @MaxDate OUT. Je ne comprends vraiment pas

sp_executesql vous permet de générer des valeurs à partir de la requête que vous exécutez. Vous pouvez avoir autant de paires que vous le souhaitez. Les premiers paramètres (Output dans ce cas) existent dans le contexte de la requête, donc vous les ajoutez comme un nvarchar (d'où le N au début) et séparez par des virgules les paramètres à l'intérieur de cette chaîne, ainsi que leurs types. MaxDate existe dans votre contexte principal puisque vous l'avez déclaré au début. En l'associant à la sortie, vous définissez la valeur sur une variable existant dans le contexte dont vous avez besoin

Le SQL dynamique est dangereux, car il rend vos entrées insalubres, vous ne devez donc jamais l'utiliser avec vos projets Web. Une requête paramétrée ne remplace pas simplement une chaîne comme cela se fait dans le SQL dynamique. L'optimiseur établit d'abord la requête, puis n'autorise que les paramètres où ils peuvent être comparés littéralement. De cette façon, le paramètre ne peut pas étendre la requête et introduire quoi que ce soit de malveillant. Le SQL dynamique prend simplement votre chaîne entière et se convertit en requête, donc si un paramètre ajoute quelque chose de malveillant qui altère le but de la requête, il sera exécuté.

0
votes

Les variables

ne sont pas autorisées dans les noms de serveur, de schéma et de table, ainsi que dans quelques autres endroits (comme dans ALTER, CREATE, DROP ..etc).

Dans votre cas, vous essayez de l'utiliser dans le nom du schéma, pour l'utiliser comme ça, vous aurez deux options.

Option 1:

Définissez chaque instruction de sélection et utilisez IF pour obtenir l'instruction de sélection souhaitée en fonction de l'entrée de variable.

Exemple:

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)

Option 2 (pas toujours recommandée car elle comporte ses propres risques):

Utilisez plutôt daynamic sql.

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

0 commentaires

1
votes

En supposant que vous utilisez SSMS comme IDE, vous pouvez simplement activer Sql Command Mode et vous pouvez alors avoir une valeur de schéma dynamique. Essayez le code suivant:

: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

0 commentaires