How to create a view with select statement passing 3 variable values

1

I want to create a view based on select query, which has three variables. 2 dates and 1 int value.

Thanks a lot for the helpful info.

---declare @startdate as varchar(10)
--declare @enddate as varchar(10)
---declare @progid as int
set @startdate = '04/01/2016'
set @enddate = '04/11/2016'

set @progid = 71

SELECT rm.rmid,
rm.ProgID,
rm.ProjID,
rm.ContractID,
pg.progno,
pj.projno,
ctr.contractno,
rm.ContractorRefNo,
rm.sequenceno,
rm.barcode,
rm.destdepartment,
rm.origsender,
rm.documentdate,
rm.ReceivedDate,
rm.rmloggeddate,
rm.acknowledgeddate,
rm.documenttypeid ,
rm.LogTypeID,
rm.documentsourceid,
rm.SubjectID,
rm.origorganization
FROM Tab_ccsnetrm rm
inner join TAB_ccsNetPrograms pg on(rm.ProgID = pg.ProgID)
inner join TAB_ccsNetProjects pj on(rm.ProjID = pj.ProjID)
inner join TAB_ccsNetContracts ctr on(rm.ContractID=ctr.ContractID)
where rm.ProgID <> 70
and (rm.progid = @progid)
and rm.rmloggeddate >= CONVERT(varchar(10), @startdate, 1)
and rm.rmloggeddate < CONVERT(varchar(10), dateadd(day, 1, @enddate), 1)

2

you can't do this in a view. you will need to use a TVF which is like a parameterized view or a stored procedure.

3

If may not be what you want, and I'm sure it s obvious, but just in case not you can do:

CREATE VIEW MyViewName
AS
SELECT rm.rmid,
       rm.ProgID,
...
       rm.rmloggeddate,
...
       rm.origorganization 
FROM Tab_ccsnetrm rm
inner join TAB_ccsNetPrograms pg on(rm.ProgID = pg.ProgID)
inner join TAB_ccsNetProjects pj on(rm.ProjID = pj.ProjID)
inner join TAB_ccsNetContracts ctr on(rm.ContractID=ctr.ContractID) 
where rm.ProgID <> 70
GO

and then in your APP etc.

SELECT Col1, Col2, ...
FROM MyViewName AS rm
WHERE (rm.progid = @progid)
      and rm.rmloggeddate >= CONVERT(varchar(10), @startdate, 1) 
      and rm.rmloggeddate < CONVERT(varchar(10), dateadd(day, 1, @enddate), 1)
4

If you want a Stored Procedure then:

CREATE PROCEDURE MyProcName
    @startdate varchar(10),
    @enddate varchar(10),
    @progid int
AS
SET NOCOUNT ON
SELECT rm.rmid,
...
...
       rm.origorganization 
FROM Tab_ccsnetrm rm
inner join TAB_ccsNetPrograms pg on(rm.ProgID = pg.ProgID)
inner join TAB_ccsNetProjects pj on(rm.ProjID = pj.ProjID)
inner join TAB_ccsNetContracts ctr on(rm.ContractID=ctr.ContractID) 
where rm.ProgID <> 70
and (rm.progid = @progid)
and rm.rmloggeddate >= CONVERT(varchar(10), @startdate, 1) 
and rm.rmloggeddate < CONVERT(varchar(10), dateadd(day, 1, @enddate), 1)
GO

and then in your APP etc.

---declare @startdate as varchar(10)
--declare @enddate as varchar(10)
---declare @progid as int
set @startdate = '04/01/2016'
set @enddate = '04/11/2016'

set @progid = 71
EXEC MyProcName
    @startdate = @startdate,
    @enddate = @enddate,
    @progid = @progid

If you use a Procedure with parameters like that you might choose to do this

CREATE PROCEDURE MyProcName
    @startdate varchar(10) = NULL,
    @enddate varchar(10) = NULL,
    @progid int = NULL
AS

so that the parameters have default values of NULL (i.e. if you exclude them from the EXEC command) and then have a "wildcard" query in the WHERE clause (of your procedure)

where rm.ProgID <> 70
and (@progid IS NULL OR rm.progid = @progid)
and (@startdate IS NULL OR rm.rmloggeddate >= CONVERT(varchar(10), @startdate, 1))
and (@enddate IS NULL OR rm.rmloggeddate < CONVERT(varchar(10), dateadd(day, 1, @enddate), 1))

and then from your APP etc:

EXEC MyProcName
    @startdate = '04/01/2016',
    @progid = 71

would run the query for progid = 71 and the given startdate but with NO specific end date.