Can a Stored Procedure, use a View ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Can a Stored Procedure, use a View ?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-04 : 11:40:19

I have a Program that uses a stored procedure to get the fields it needs. Now, we want to go against a database that is in a company that we buy some products from and then plug the data into that program. The field names are different and the structure as well, But I think I can get what I need fields wise.

The thing is, the dba there only wants me to use a particular view that he has created for security reasons. For the view itself, i have asked for the code behind it but so far they haven't sent it. I just have the actual name, and I am able to run it on their server so I can deduce the fields names.

So my Stored proc should call this view. How can yo do that?
the stored proc currently is this:

SELECT SOP10200.XTNDPRCE As Expense, SOP10200.QUANTITY as Quantity, RM00101.CUSTNAME, SOP10200.ITEMDESC as Description,
SOP10100.DOCDATE AS DateCreated,
IV40600.UserCatLongDescr as Category

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.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR INNER JOIN
dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
inner join dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC

where SOP10100.SOPTYPE = '1' AND SOP10100.DOCDATE >= DATEADD (YEAR, - 2, GETDATE()) AND LTrim(RTrim(RM00101.CUSTNAME)) = @customername
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 11:43:31

you can call a view just like you call a table inside procedure. A view is nothing but a virtual table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-04 : 11:44:22

Just join it like any other table.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-04 : 11:44:38

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-04 : 11:49:28

so if the name of the view is V_Dashboard
I would do this:
SELECT V_Dashboard.XTNDPRCE As Expense, V_Dashboard.QUANTITY as Quantity, V_Dashboard.CUSTNAME, V_Dashboard.ITEMDESC as Description,
etc.
FROM V_Dashboard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 11:51:19

exactly. why? is it not working?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-04 : 11:54:04

No I just started this. I am not experienced so much with this so I wasn't sure how to do it.

Thank you for you help once again, I really owe you alot these past months you have been extremely generous with your time and knowledge.
A.W.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 11:56:06

quote:
Originally posted by AdamWest

No I just started this. I am not experienced so much with this so I wasn't sure how to do it.

Thank you for you help once again, I really owe you alot these past months you have been extremely generous with your time and knowledge.
A.W.

No problem at all
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-04 : 12:10:53

One other question, in this statement
SELECT V_Dashboard_Expenses.PRICE * V_Dashboard_Expenses.Quantity As Expense
I now have the result of the multiplication, as well as the fields Price and Qty to use in the program?

IOW, I need it to match the names on the Stored proc. is the way to use an alias?

IOW, this one V_Dashboard_Expenses.CUSTOMER

I need to call it RM00101.Custname

does the table name matter at all?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:14:18

Nope you've already done the operation so you've only result not the source fields for getting that you need to include them separate

SELECT V_Dashboard_Expenses.PRICE,V_Dashboard_Expenses.Quantity,V_Dashboard_Expenses.PRICE * V_Dashboard_Expenses.Quantity As Expense
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-03-04 : 12:20:30

Aha. thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:23:04

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources