none
Erro em Sql Dinamica RRS feed

  • Pergunta

  •  

     

    Precisei modificar uma procedure, deixando da seguinte forma:

    ALTER PROCEDURE [dbo].[spGetItems]
    (
            @Name   varchar(50) =null ,
            @Date   datetime    =null ,
            @Active smallint    =null
    )
    AS 
    
     SELECT * FROM tbItems  WHERE 1=1
              CASE WHEN @Name IS NOT NULL THEN 
                   AND dsName = @Name END
              CASE WHEN @Date IS NOT NULL THEN  
                   AND (@Date BETWEEN dtStartDate and dtEndDate) END
              CASE WHEN @Active = 0 THEN 
                   AND GETDATE() NOT BETWEEN dtStartDate AND dtEndDate) 
                   ELSE GETDATE() BETWEEN dtStartDate AND dtEndDate END;
                   
     

    Na query acima existe um erro..

    Logo pensei em mudar, deixar assim:

    ALTER PROCEDURE [dbo].[spGetItems]
    (
            @Name   varchar(50) =null ,
            @Date   datetime    =null ,
            @Active smallint    =null
    )
    AS 
     DECLARE @cmdsql As NVARCHAR(4000) 
    
     SET @cmdsql =   N'SELECT * FROM tbItems' + N' WHERE 1 = 1'
                   + CASE WHEN @Name IS NOT NULL THEN 
                     N' AND dsName = @Name' END
                   + CASE WHEN @Date IS NOT NULL THEN  
                     N' AND (@Date BETWEEN dtStartDate and dtEndDate)' END
                   + CASE WHEN @Active = 0 THEN 
                          N' AND GETDATE() NOT BETWEEN dtStartDate AND dtEndDate)' 
                     ELSE N' GETDATE() BETWEEN dtStartDate AND dtEndDate' END;
                     
    
    
    

    Mas como posso retornar corretamente o resultado ? Estou chamando a sp a partir do C#.

     

    • Editado .NET Dev segunda-feira, 6 de fevereiro de 2012 13:37
    segunda-feira, 6 de fevereiro de 2012 12:32

Respostas

  • testa assim

    ALTER PROCEDURE [dbo].[spGetItems]
    (
            @Name   varchar(50) =null ,
            @Date   datetime    =null ,
            @Active smallint    =null
    )
    AS 
     DECLARE @cmdsql As NVARCHAR(4000) 
     DECLARE @parametros As NVARCHAR(200) 
    
     SET @cmdsql =   N'SELECT * FROM tbItems ' +  N' WHERE 1 = 1'
    
     if @Name IS NOT NULL
        SET @cmdsql =  @cmdsql+N' AND dsName = @pName' 
        
    if @Date IS NOT NULL
        SET @cmdsql =  @cmdsql+ N' AND (@pDate BETWEEN dtStartDate and dtEndDate)' 
     
     if @Active = 0 
        SET @cmdsql =  @cmdsql+' AND GETDATE() NOT BETWEEN dtStartDate AND dtEndDate)'
     ELSE 
        SET @cmdsql =  @cmdsql+' AND GETDATE() BETWEEN dtStartDate AND dtEndDate' 
     
    
     SET @parametros = N'@pName As VARCHAR(50), @pDate AS DATETIME';
                     
     EXEC sp_executesql @cmdsql, @parametros, @pName = @Name, @pDate = @Date   
    

     


    Att.
    Marcelo Fernandes

    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    • Marcado como Resposta .NET Dev terça-feira, 7 de fevereiro de 2012 13:30
    segunda-feira, 6 de fevereiro de 2012 17:37
    Moderador

Todas as Respostas

  • Bom Dia,

    Não basta declarar, tem que executar. Após a declaração, você deve chamar o comando com a sp_executesql.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 6 de fevereiro de 2012 13:51
  • AT.CM

    Este link deve ajudar...

    http://msdn.microsoft.com/en-us/library/ms188001.aspx


    Att.
    Marcelo Fernandes

    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    segunda-feira, 6 de fevereiro de 2012 15:02
    Moderador
  • então fiz assim:

     

    
    -- =============================================
    ALTER PROCEDURE [dbo].[spGetItems]
    (
            @Name   varchar(50) =null ,
            @Date   datetime    =null ,
            @Active smallint    =null
    )
    AS 
     DECLARE @cmdsql As NVARCHAR(4000) 
    
     SET @cmdsql =   N'SELECT * FROM tbItems ' +  N' WHERE 1 = 1'
                   + CASE WHEN @Name IS NOT NULL THEN 
                     N' AND dsName = @pName' END
                   + CASE WHEN @Date IS NOT NULL THEN  
                     N' AND (@pDate BETWEEN dtStartDate and dtEndDate)' END
                   + CASE WHEN @Active = 0 THEN 
                          ' AND GETDATE() NOT BETWEEN dtStartDate AND dtEndDate)' 
                     ELSE ' AND GETDATE() BETWEEN dtStartDate AND dtEndDate' END;
                     
     EXEC sp_executesql @cmdsql, 'N @pName As VARCHAR(50), @pDate AS DATETIME', @pName = @Name, @pDate = @Date                  
     
    
    

     


    Depois para testar:

    exec [spGetItemsHeader] 'aaaa','10/10/2012 10:10:00',1

    Acontece o erro:

    Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
    Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.


    • Editado .NET Dev segunda-feira, 6 de fevereiro de 2012 16:05
    segunda-feira, 6 de fevereiro de 2012 16:04
  • testa assim

    ALTER PROCEDURE [dbo].[spGetItems]
    (
            @Name   varchar(50) =null ,
            @Date   datetime    =null ,
            @Active smallint    =null
    )
    AS 
     DECLARE @cmdsql As NVARCHAR(4000) 
     DECLARE @parametros As NVARCHAR(200) 
    
     SET @cmdsql =   N'SELECT * FROM tbItems ' +  N' WHERE 1 = 1'
                   + CASE WHEN @Name IS NOT NULL THEN 
                     N' AND dsName = @pName' END
                   + CASE WHEN @Date IS NOT NULL THEN  
                     N' AND (@pDate BETWEEN dtStartDate and dtEndDate)' END
                   + CASE WHEN @Active = 0 THEN 
                          ' AND GETDATE() NOT BETWEEN dtStartDate AND dtEndDate)' 
                     ELSE ' AND GETDATE() BETWEEN dtStartDate AND dtEndDate' END;
    
     SET @parametros = N'@pName As VARCHAR(50), @pDate AS DATETIME';
                     
     EXEC sp_executesql @cmdsql, @parametros, @pName = @Name, @pDate = @Date                  
     
    

     


    Att.
    Marcelo Fernandes

    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba


    segunda-feira, 6 de fevereiro de 2012 16:25
    Moderador
  • Ola Marcelo, fiz como me passou , não deu erro..

    Porém passando todos os registros como NULL, é pra retornar todo conteudo da tabela no caso.

    Porem não retorna nada..

    Porquee?

    segunda-feira, 6 de fevereiro de 2012 16:44
  • testa assim

    ALTER PROCEDURE [dbo].[spGetItems]
    (
            @Name   varchar(50) =null ,
            @Date   datetime    =null ,
            @Active smallint    =null
    )
    AS 
     DECLARE @cmdsql As NVARCHAR(4000) 
     DECLARE @parametros As NVARCHAR(200) 
    
     SET @cmdsql =   N'SELECT * FROM tbItems ' +  N' WHERE 1 = 1'
    
     if @Name IS NOT NULL
        SET @cmdsql =  @cmdsql+N' AND dsName = @pName' 
        
    if @Date IS NOT NULL
        SET @cmdsql =  @cmdsql+ N' AND (@pDate BETWEEN dtStartDate and dtEndDate)' 
     
     if @Active = 0 
        SET @cmdsql =  @cmdsql+' AND GETDATE() NOT BETWEEN dtStartDate AND dtEndDate)'
     ELSE 
        SET @cmdsql =  @cmdsql+' AND GETDATE() BETWEEN dtStartDate AND dtEndDate' 
     
    
     SET @parametros = N'@pName As VARCHAR(50), @pDate AS DATETIME';
                     
     EXEC sp_executesql @cmdsql, @parametros, @pName = @Name, @pDate = @Date   
    

     


    Att.
    Marcelo Fernandes

    MCP, MCDBA, MCSA, MCTS.
    Se útil, classifique!!!
    Me siga no twitter: @marcelodba

    • Marcado como Resposta .NET Dev terça-feira, 7 de fevereiro de 2012 13:30
    segunda-feira, 6 de fevereiro de 2012 17:37
    Moderador