Creating View, but also need to Declare, use a Procedure to call within the view?

Question

Creating View, but also need to Declare, use a Procedure to call within the view?

quinn.jay

SSCertifiable

Points: 5505
More actions
September 12, 2017 at 2:35 pm

#349120

Hello, I have a select that used declare, and now I need to make that select a view, and I can't use declare, should I create a procedure and then call that procedure from the create view or should I create the view from within the procedure? My declares look like this:
DECLARE @actvt_dt1 DATE = '20150815';
DECLARE @actvt_dt2 DATE = '20150912';
DECLARE @actvt_dt3 DATE = '20150926';
DECLARE @actvt_dt4 DATE = '20150813';
DECLARE @actvt_dt5 DATE = '20150910';
DECLARE @actvt_dt6 DATE = '20150917';
DECLARE @actvt_dt7 DATE = '20150819';
DECLARE @actvt_dt8 DATE = '20150916';
DECLARE @actvt_dt9 DATE = '20150923';
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

What output are you expecting exactly?
Just guessing as you provided insufficient information:


CREATE VIEW StaticDataView AS

SELECT actvt_dt1 = Cast('20150815' AS date)
        , actvt_dt2 = Cast('20150912' AS date)
        , actvt_dt3 = Cast('20150926' AS date)
        , actvt_dt4 = Cast('20150813' AS date)
        , actvt_dt5 = Cast('20150910' AS date)
        , actvt_dt6 = Cast('20150917' AS date)
        , actvt_dt7 = Cast('20150819' AS date)
        , actvt_dt8 = Cast('20150916' AS date)
        , actvt_dt9 = Cast('20150923' AS date);

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

An iTVF may do what you need.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Need more information. Anything we say now are just shots in the dark.

quinn.jay SSCertifiable Points: 5505 More actions · Answer 4

Phil Parkin - Tuesday, September 12, 2017 2:52 PM
An iTVF may do what you need.

I found a way to get around using declare and hence a function within a view. I looked at the link, and if I need to use a function in a view, this is what I will use as a resource. Thanks

If I used the function, to call the declare, it would in a sense be hard coding a a date to end up getting a number value of a date period, and it would have been wrong. Ended up using datediff and date math and have the proper increments based off a moving date, as it should be.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Okay, so share your solution. It may benefit others to see what you did.