Procédure stockée avec le nom de la table comme un paramètre parmi d'autres

J'ai essayé de mettre à jour une procédure stockée qui a bien fonctionné, sans avoir à utiliser sp_executesql. Maintenant, je veux avoir le nom de la table comme un paramètre que j'ai un certain nombre de tables avec la même structure et ne souhaitez pas créer de nouvelles procédures stockées pour chacun d'eux.

Le problème que j'ai c'est que cette version semble exiger de tous les paramètres, alors que la précédente accepté un certain nombre de paramètres. Par exemple, si je supprime tous les paramètres et juste le paramètre @TableName il fonctionne très bien. J'ai essayé de regarder pour un exemple, mais je ne peux pas trouver quelque chose comme cela. Tous les exemples de l'analyse du nom de la table ont seulement ce paramètre.

CREATE PROCEDURE cafgTenantNamesTEST2
    @TableName sysname,
    @Square nvarchar(100) = null,
    @Location nvarchar(100) = null,
    @Name nvarchar(100) = null,
    @NormalizedName nvarchar(100) = null,
    @SharedLand int = 0,
    @FieldNumber int = 0,
    @Description nvarchar(255) = null,
    @Dwelling nvarchar(100) = null
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX)
    SET @sql = 'SELECT * FROM [' + @TableName + ']' + 
    'WHERE ([Square] LIKE ''' + @Square + ''' OR ''' + @Square + ''' IS NULL)' + 
    'AND ([Location] = ''' + @Location + ''' OR ''' + @Location + ''' IS NULL)' +
    ...
    ...
--PRINT @sql
EXEC sp_executesql @sql
END

Suggestions s'il vous plaît.

Vérifier la valeur de @sql et assurez-vous qu'il peut exécuter. Pouvez-vous construire une chaîne sql par coder en dur le nom de la table et obtenir la clause where de travail?
Essayez d'ajouter un espace avant le WHERE et la AND que vous concaténez sans les espaces.

OriginalL'auteur Peter C | 2013-06-15

  1. 10

    Suggestion 1:
    Utilisation QUOTENAME() pour gérer correctement s'échapper du nom de la table.

    Suggestion 2: Vous insérez la valeur du paramètre @sql. Ne pas le faire. Au lieu de cela, vous devez utiliser pameterized sql.

    Suggestion 3: Éliminer le OU la logique conditionnelle de la construction de la requête de la clause where.

     CREATE PROCEDURE cafgTenantNamesTEST2
    @TableName sysname,
    @Square nvarchar(100) = null,
    @Location nvarchar(100) = null,
    @Name nvarchar(100) = null,
    @NormalizedName nvarchar(100) = null,
    @SharedLand int = 0,
    @FieldNumber int = 0,
    @Description nvarchar(255) = null,
    @Dwelling nvarchar(100) = null
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX)
    SET @sql = N'SELECT * FROM ' + QUOTENAME(@TableName ) + 
    ' WHERE 1=1 '
    IF  @Square IS NOT NULL
      SET @sql = @sql + ' AND ([Square] LIKE   @Square )'  -- still patameterized
   IF @Location IS NOT NULL
      SET @sql = @sql + N'  AND ([Location] = @Loc )'
    ...
    ...
--PRINT @sql
EXEC sp_executesql @sql, N'@Square nvarchar(100), @Loc nvarchar(100)...', @square=@square, @loc=@location  -- the param names can be the same or different, sp_executesql has it's own scope.
END

    Sp_executesql peut exécuter sql paramétrée en outre à la plaine de sql. C'est le système sous-jacent de la procédure stockée qui est utilisé par les bibliothèques client pour exécuter paramétrée code. Par exemple, le Système de.Les données.SqlClient.SqlCommand appellera sp_executesql si vous avez ajouté tous les paramètres. Il est atypique en ce qu'il accepte un nombre variable de paramètres. La msdn docs sur sp_executesql fournir de bonnes informations, mais n'est pas clair. La capture de l'activité dans le générateur de profils SQL est le moyen le plus facile de voir sp_executesql dans l'action.

    Brillant. Merci pour cette @StrayCatDBA. J'ai utilisé de simples sql depuis des années, mais j'ai seulement commencé à utiliser de la sp et j'ai encore de la difficulté avec eux. Je vais travailler sur ça demain maintenant que je suis lié jusqu'à aujourd'hui. Puis-je assumer avec cette méthode, puis que je peux inclure aucun ou un nombre quelconque de paramètres lors de l'appel de cette proc?
    Désolé, pourriez-vous aussi s'il vous plaît expliquer le EXEC ligne? I. e. pourquoi les déclarations.
    Cela a fonctionné, merci. Sauf, je ne vois toujours pas ce que la ligne exec n'est que si je mets les paramètres, j'obtiens une erreur: Procédure s'attend à ce paramètre '@des paramètres de type 'ntext/nchar/nvarchar" .
    assurez-vous que vous utilisez le type de données Nvarchar pour les paramètres au lieu de varchars. c'est à dire N'...' au lieu de '...'
    Merci à tous pour votre aide @StrayCatDBA, beaucoup apprécié.

    OriginalL'auteur StrayCatDBA