Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Stored Procedure error

I created an 'stored procedure' like the below following an example found on google.

When running

      exec SP_TESTE '2012-07-31', NULL, NULL, NULL
      
      
I get the error message:


Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@w_varAAA'.


Please could someone point me what's missing?


Translate from portuguese:

"Eu criei uma 'stored procedure' parecida com a abaixo seguindo um exemplo encontrado no google."
Ao executar, recebo a mensagem de erro :
Por favor, alguem poderia me indicar o que está faltando? "



CREATE PROCEDURE SP_TESTE

@w_par001 datetime,
@w_par002 datetime,
@w_par003 char( 7 ),
@w_par004 char( 5 )

AS

declare @w_sql varchar( 950 ),
           @w_varAAA varchar( 1 ),
          @w_varBBB varchar( 1 ),
          @w_varCCC varchar( 1 )

set @w_varAAA = 'D'
set @w_varBBB = 'S'
set @w_varCCC = ' '

set @w_sql = 'select TABLE.FIELD1, ' +
                         ' TABLE.FIELD2, ' +
                        ' TABLE.FIELD3, ' +
                        ' TABLE.FIELD4, ' +
                        ' TABLE.FIELD5, ' +
                        ' TABLE.FIELD6  ' +
              '   from TABLE ' +
             '  where TABLE.FIELD1 <= @w_par001 ' +
             '   and (  TABLE.FIELD2  = @w_varAAA or ' +
                        '  TABLE.FIELD3  = @w_varBBB or ' +
                      '  TABLE.FIELD4  = @w_varCCC ) '

if ( @w_par002 is not null )

      begin
      
            set @w_sql = @w_sql + ' and TABLE.FIELD5 >= @w_par002 '

      end


if ( @w_par003 is not null )

      begin

            set @w_sql = @w_sql + ' and TABLE.FIELD5 = @w_par003 '

      end


if ( @w_par004 is not null )

      begin
      
            set @w_sql = @w_sql + ' and TABLE.FIELD6 = @w_par004 '
            
      end


exec( @w_sql )
GO
0
etm_wkl
Asked:
etm_wkl
  • 3
  • 2
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
You are missing parentheses. Put them in here:

CREATE PROCEDURE SP_TESTE
(
@w_par001 datetime,
@w_par002 datetime,
@w_par003 char( 7 ),
@w_par004 char( 5 )
)
AS
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
'  where TABLE.FIELD1 <= @w_par001 ' +

inside the dynamic sql does not replace the variables like that.
I would do this without dynamic sql:

CREATE PROCEDURE SP_TESTE

@w_par001 datetime,
@w_par002 datetime,
@w_par003 char( 7 ),
@w_par004 char( 5 )

AS

declare @w_sql varchar( 950 ),
           @w_varAAA varchar( 1 ),
          @w_varBBB varchar( 1 ),
          @w_varCCC varchar( 1 )

set @w_varAAA = 'D'
set @w_varBBB = 'S'
set @w_varCCC = ' '


select TABLE.FIELD1, 
                         TABLE.FIELD2,  
                         TABLE.FIELD3,  
                         TABLE.FIELD4,  
                         TABLE.FIELD5,  
                         TABLE.FIELD6   
                 from TABLE  
               where TABLE.FIELD1 <= @w_par001  
                and (  TABLE.FIELD2  = @w_varAAA or  
                          TABLE.FIELD3  = @w_varBBB or  
                        TABLE.FIELD4  = @w_varCCC ) 

  AND ( @w_par002 is null OR TABLE.FIELD5 >= @w_par002 )
  AND ( @w_par003 is null OR TABLE.FIELD5 = @w_par003 )
  AND ( @w_par004 is null OR TABLE.FIELD6 = @w_par004 )            


exec( @w_sql )
GO 

Open in new window

0
 
etm_wklAuthor Commented:
I did it, but the error message continues.

Tks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dsackerContract ERP Admin/ConsultantCommented:
Did you add the parentheses in your proc heading?
0
 
etm_wklAuthor Commented:
dsacker,

Yes ( at the beginning and end of parameters, as suggested )
0
 
etm_wklAuthor Commented:
angelIII,

This format allowed me to eliminate the variables, keeping only the parameters received.

Thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now