November 27, 2008 at 6:15 am
Hello...i am new here and want to ask something 🙂
I need to create a report for our customers statement. I have created a view that returns to me a table with customers and their balances (1 customer per line). I have also created the stored procedure that gives me the statement for customers (list of credit purchases per customer - one customer, many lines)
Is there any way to use the view in the stored procedure in order to create later the report? (To have member, the balance and his list), or i need to use these two (view and stored procedure) separated in the report designing?
Or is better to create two stored procedures? (And if yes, how i use the one in the other?)
November 27, 2008 at 7:05 am
I'm not sure exactly what you are asking, but you can use a view within a stored procedure just like you use a table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 28, 2008 at 1:11 am
You need to convert your stored procedure into either a view or a table valued function so that you can use the data it contains in another stored procedure.
In the case of a a view, you would then have two views - vwCustomer for the customer details and vwDetails for the summary and the lines. You would need a common link field e.g. customer ID and then join the two views to produce a third view vwCustomerandDetails or a stored procedure which would then be the source of your report.
If you are using a stored procedure because you need a parameter e.g. a date range then you need to consider a table valued function which can also take a parameter. You would wrap the whole thing up in a stored procedure which would take the date range parameter something like
ProcCustomerAndDetails
@DateRange suitable data type
AS
BEGIN
SELECT bits from vwCustomer, bits from fnDetails
FROM
vwCustomer INNER JOIN
dbo.fnDetails(@DateRange) AS fnDetails
ON
vwCustomer.CustomerID = fnDetails.CustomerID
END
GO
November 28, 2008 at 8:29 am
A stored procedure is bound to whatever tables/views/etc. that it was originally written for. you cannot apply a view to a stored procedure to get a modified result.
With Views and Tables you can use Joins and Unions to combine and re-filter them into a new composite data set, however, I do not believe that there is anyway to do the same thing from Reporting Services. There is a way to do this with stored procedure(s) in another stored procedure, but it is tricky and has some restrictions.
For Reporting Services, your best bet is probably to write a new report (or stored procedure) that uses the view, but just duplicates the logic of the original stored procedure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2008 at 6:01 am
Thanks guys for the replies...got the point...
I have an other one question. At last i have 3 stored procedures and need to use the 2 of them in the third. But all stored procedures have parameter. There is a way to convert the whole stored procedure in view to have its results in a table and use them as fields in the third stored procedure?
What is the easiest way?
(Its a Period Stock Report and i have 3 stored procedures for sales, purchases and adjustments - for reporting we use stored procedures in an other program)
December 1, 2008 at 6:03 am
Read the post about table valued functions
December 1, 2008 at 6:08 am
You could also just call your procedure and insert it into a table variable or temp table from your main procedure, that way you don't have to create the functions. You will however still need to pass in the params
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply