CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
alejandro carnero
alejandro carnero

Reputation: 1784

Change a View with a procedure

This is the source of a view , i use this view as the base of a procedure named buscacancelados:

 SELECT     NUMERO  FROM dbo.CTRC
 WHERE     (EMITENTE = 504) AND (MONTH(EMISSAODATA) = 3) 
 AND (YEAR(EMISSAODATA) = 2013)

This procedure returns the missing numbers in a set

alter   proc buscarcancelado  (@emp int) as
 begin
 set nocount on; 
 declare @min int   --- declare the  variavels to be used
 declare @max int
 declare @I int

 IF OBJECT_ID ('TEMP..#TempTable') is not null --  Controls if  exists this table
 begin
        drop table #TempTable -- If exist delete
 end

 create table #TempTable 
              (TempOrderNumber int)-- create a temporary table

 SELECT @min = ( SELECT MIN (numero)                           
                 from controlanum  with (nolock))  -- search the min value of the set

 SELECT @max =  ( SELECT Max (numero)                           
                  from controlanum  with (nolock)) -- search the max value of the set    

 select @I = @min  -- control where begins the while

 while @I <= @max --  finish with the high number
       begin
             insert into #TempTable 
             select @I 
             select @I = @I + 1 
       end 
 select tempordernumber from #TempTable 
 left join controlanum O with (nolock)
 on TempOrderNumber = o.numero where o.numero is null 
 end

I want to change the view controlanum with this procedure 

        create proc filtraperiodo (@emp int,@mes int,@ano int)as
        select numero from ctrc where
        EMITENTE = 504
        and MONTH (EMISSAODATA ) = 3 and YEAR (EMISSAODATA)=2013

I want something like this 

          SELECT @min = ( SELECT MIN (numero) from  filtraperiodo 504,2,2013

Upvotes: 2

Views: 83

Answers (1)

Rob.Kachmar
Rob.Kachmar

Reputation: 2188

Create controlanum as a table-valued function instead of a view 

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID('[dbo].[controlanum]') AND XTYPE IN ('FN', 'IF', 'TF'))
        DROP FUNCTION [dbo].[controlanum]
    GO

    CREATE FUNCTION [dbo].[controlanum] (
        @emp int
       ,@mes int
       ,@ano int
    )

    RETURNS @numeros TABLE (numero int)

    AS
    BEGIN

      INSERT @numeros
      SELECT numero
        FROM ctrc WITH (NOLOCK)
       WHERE EMITENTE               = @emp
         AND MONTH (EMISSAODATA )   = @mes 
         AND YEAR (EMISSAODATA)     = @ano

      RETURN

    END
    GO

Everywhere you reference controlanum, pass it your 3 filter values. For instance:

    --...other code here...

    SELECT @min = MIN(numero)
      FROM dbo.controlanum(@emp, @mes, @ano)

    SELECT @max = MAX(numero)
      FROM dbo.controlanum(@emp, @mes, @ano)

    --...other code here...

    SELECT tempordernumber 
      FROM #TempTable A
      LEFT JOIN dbo.controlanum(@emp, @mes, @ano) O
        ON A.TempOrderNumber <> O.numero
     WHERE O.numero IS NULL

Upvotes: 1

Related Questions