Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 View with parameters?

Author  Topic 

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-29 : 11:55:26
I have a report running that takes a bit of time to run. I noticed that the main table used, is actually a view, and it reads about 125,000 rows. I was wondering if you can place parameters to this view, since the vast majority of the reads are not needed.

X002548
Not Just a Number

15586 Posts
Posted - 2009-07-29 : 11:56:13
SELECT * FROM view WHERE Col = 'Something'

???



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-29 : 11:58:53
Yes but the report is parameter driven so it wouldn't be a fixed = to Something. Can you include parameters in a view?
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-29 : 14:00:31
Could i put in the View for example, that I never need a row that has a date field of not in the current year or the current year minus 1. I only am processing these 2 years.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-07-29 : 15:05:01
You can not include parameters inside the view definition. You need to use a function instead.

Can your reports instead use a stored procedure?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-29 : 15:18:13
possibly this can be done in a stored proc. it already exists as the view tho.
I was thinking to at least have the view only process the past 2 years iow, this report is comparing this year with last year by month.
Can I have the view only pick this year and this year minus 1?
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-29 : 15:59:18
Also the view is used in various ways for different parts of the report. It is a fairly complicated report so that may be why.

Taking out the years would help a bit.
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-29 : 16:17:51
This is the view. Can this be transformed into a stored procedure?

ALTER VIEW [dbo].[Vw_Details]
AS
SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, RM00101.CPRCSTNM, IV40600.UserCatLongDescr AS UserCatLongDescr_IV40600,
CATS.UserCatLongDescr AS UserCatLongDescr_CATS, SOP10200.ITEMNMBR, SOP10200.SOPNUMBE, SOP10200.QUANTITY, SOP10200.OXTNDPRC,
SOP10200.SOPTYPE, SOP10100.DOCDATE, 'Current' AS source, MONTH(SOP10100.DOCDATE) AS ReportMonth, YEAR(SOP10100.DOCDATE)
AS ReportYear
FROM dbo.sop10200 AS SOP10200 INNER JOIN
dbo.iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.sop10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN
dbo.iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
dbo.rm00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
UNION ALL
SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, RM00101.CPRCSTNM, IV40600.UserCatLongDescr, CATS.UserCatLongDescr AS Expr1,
SOP30300.ITEMNMBR, SOP30300.SOPNUMBE, SOP30300.QUANTITY, SOP30300.OXTNDPRC, SOP30300.SOPTYPE, SOP30200.DOCDATE,
'History' AS source, MONTH(SOP30200.DOCDATE) AS ReportMonth, YEAR(SOP30200.DOCDATE) AS ReportYear
FROM dbo.sop30300 AS SOP30300 LEFT OUTER JOIN
dbo.iv00101 AS IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.sop30200 AS SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOIN
dbo.iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL LEFT OUTER JOIN
dbo.iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
dbo.rm00101 AS RM00101 ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR

GO
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-29 : 16:20:43
Listen to Tara.
You want a function of you want to use the data for further processing.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-07-29 : 16:21:15
quote:
Originally posted by Adam West

This is the view. Can this be transformed into a stored procedure?

ALTER VIEW [dbo].[Vw_Details]
CREATE PROCEDURE dbo.uspDetails
AS
SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, RM00101.CPRCSTNM, IV40600.UserCatLongDescr AS UserCatLongDescr_IV40600,
CATS.UserCatLongDescr AS UserCatLongDescr_CATS, SOP10200.ITEMNMBR, SOP10200.SOPNUMBE, SOP10200.QUANTITY, SOP10200.OXTNDPRC,
SOP10200.SOPTYPE, SOP10100.DOCDATE, 'Current' AS source, MONTH(SOP10100.DOCDATE) AS ReportMonth, YEAR(SOP10100.DOCDATE)
AS ReportYear
FROM dbo.sop10200 AS SOP10200 INNER JOIN
dbo.iv00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.sop10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL INNER JOIN
dbo.iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
dbo.rm00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
UNION ALL
SELECT IV00101.ITMGEDSC, IV00101.ITEMDESC, RM00101.CUSTNAME, RM00101.CPRCSTNM, IV40600.UserCatLongDescr, CATS.UserCatLongDescr AS Expr1,
SOP30300.ITEMNMBR, SOP30300.SOPNUMBE, SOP30300.QUANTITY, SOP30300.OXTNDPRC, SOP30300.SOPTYPE, SOP30200.DOCDATE,
'History' AS source, MONTH(SOP30200.DOCDATE) AS ReportMonth, YEAR(SOP30200.DOCDATE) AS ReportYear
FROM dbo.sop30300 AS SOP30300 LEFT OUTER JOIN
dbo.iv00101 AS IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.sop30200 AS SOP30200 ON SOP30300.SOPTYPE = SOP30200.SOPTYPE AND SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOIN
dbo.iv40600 AS IV40600 ON IV00101.ITMGEDSC = IV40600.USCATVAL LEFT OUTER JOIN
dbo.iv40600 AS CATS ON IV00101.USCATVLS_2 = CATS.USCATVAL INNER JOIN
dbo.rm00101 AS RM00101 ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR

GO



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-30 : 13:20:50
I ran this create procedure and it came back and said it completed successfully, but I cannot locate this object in the tree of Views for the database.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-07-30 : 13:35:55
Refresh your screen.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-30 : 14:13:55
nope it's still not showing under views. but if I try to create it again, I get an error message.
So, it must be put somewhere, but where?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-07-30 : 14:18:41
quote:
Originally posted by Adam West

nope it's still not showing under views. but if I try to create it again, I get an error message.
So, it must be put somewhere, but where?

procedures can be found under database-> programability-> stored procedures
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-30 : 14:50:08
Thank you I was looking under views my bad.
Go to Top of Page

Adam West
Constraint Violating Yak Guru

261 Posts
Posted - 2009-07-30 : 16:21:53
About the parameter itself. I am getting from the user 2 parameters. One is the Customer Group type which doesn't help much. The other is the Report Date. I take this report date in the Report and take the month they pass as well as 3 months prior for the current year and the previous year. I do this via the Subprograms of my report.

How would I translate this manipulation in the stored proc?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-08-04 : 03:29:13
quote:
Originally posted by Adam West

About the parameter itself. I am getting from the user 2 parameters. One is the Customer Group type which doesn't help much. The other is the Report Date. I take this report date in the Report and take the month they pass as well as 3 months prior for the current year and the previous year. I do this via the Subprograms of my report.

How would I translate this manipulation in the stored proc?

use a subquery to get data for 3 months prior and include it in main select
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site