View with parameters without external Where?

Asked

Viewed 1,016 times

2

I need to receive parameters for a view, but how she got the COUNT(DISTINCT DTA_HOR) I can’t with the where external. Would it have some other way? 

SELECT EX1 ,EX2, EX3, EX4,
       (SELECT COUNT(DISTINCT DTA_HOR) FROM TB_1
        WHERE EX1 = EX5 AND DTA_HOR >= '!!PARAM_DATA_INI!!!'
        AND DTA_HOR <= '!!PARAM_DATA_FIM!!!') AS FREQUENCIA 
FROM TB_2 
WHERE DTA >= '!!PARAM_DATA_INI!!!' AND DTA <= '!!PARAM_DATA_FIM!!!' AND LOCAL = '!!PARAM_LOCAL!!!'
sql sql-server view

  • Could you tell us which is the database manager? And version?

  • Hello Jose Says , is SQL Server 2008 R2 (SP3)

  • What you need is to transform the code you posted into a view, without WHERE clause, so that the restriction can be informed in the call to view? // Needs to be view or it could be a function of the inline table-Valued? // Noted that for the same value of EX1/EX5 the FREQUENCY value will always be the same?

1 answer

4

I need to receive parameters for a view,

To remind me there is no way to use parameters for a display (view). What is possible is to add, in the call to the display, clauses such as WHERE and ORDER BY.

Would there be some other way?

This approach using function. I didn’t have the opportunity to test the suggested codes, but I hope there are no errors.

FUNCTION
To pass the values as parameter, the suggestion is to use a function of type inline table-Valued. 

-- código #1 v2
CREATE FUNCTION fnFreq (@dataIni datetime, @dataFim datetime, @local xxx)
     returns table 
return
SELECT EX1 ,EX2, EX3, EX4,
       (SELECT count(distinct DTA_HOR) 
          from TB_1 as T1
          where T2.EX1 = T1.EX5 
                and T1.DTA_HOR >= @dataIni
                and T1.DTA_HOR <= @dataFim) as FREQUENCIA
  from TB_2 as T2
  where T2.DTA >= @dataIni
        and T2.DTA <= @dataFim
        and T2.LOCAL = @local;
go

As the type of data in the PLACE column has not been reported, it is necessary that xxx is replaced by the correct information.

To call the function, a form is

-- código #2
SELECT EX1, EX2, EX3, EX4, FREQUENCIA
  from dbo.fnFreq (@PARAM_DATA_INI, @PARAM_DATA_FIM, @LOCAL) as F;

Variables to be passed as a parameter must be declared in advance and have the values checked.

  • Yesssssss !!!! Thank you very much :D

Browser other questions tagged sql sql-server view

You are not signed in. Login or sign up in order to post.