How to call a stored proc?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

megabyte - 29 Jul 2006 23:46 GMT

What is the syntax to call a SQL server stored procedure from a report?
Say if I wanted to create a custom field that would be based on an
expression that calls the stored proc (which would return a single
value from a single field - a sum in this case).

What would be the syntax for doing this within a UDF?

Thanks for any help!

Reply to this Message

Shlomi - 30 Jul 2006 16:11 GMT

Hi
it very similar with sending an sql statment.
from the new dataset window choose command type=storsd procedure, and press
OK.
now you will get the stored procedures list. select the wanted SP and run
the query.
now, go back to the dataset window, you will find there your SP parameters.
you can give some values to those parameters.

hope it was helpful

Shlomi

> What is the syntax to call a SQL server stored procedure from a report?
> Say if I wanted to create a custom field that would be based on an
[quoted text clipped - 4 lines]
>
> Thanks for any help!

Reply to this Message

megabyte - 30 Jul 2006 17:01 GMT

Thanks for your reply - I think maybe I did not explain this well
enough: The place where I need to call the stored proc is not while
building the original datasets for the report - this stored proc needs
to run during the execution of the report, for each record returned by
one of the datasets - so really the best place for me to call it would
be in the custom "code" section of the report, in a
User-Defined-Function - do you know what the syntax would be for that?
(this stored proc would return one single value, a sum).

> Hi
> it very similar with sending an sql statment.
[quoted text clipped - 17 lines]
> >
> > Thanks for any help!

Reply to this Message

Shlomi - 30 Jul 2006 18:43 GMT

Hi
I can think about two ways to do this:
1. you can write a custom code for reading from DB, and call it from the
development envionment.

2. you can retrive all data and write client function for dealing the
aggregations. (i did dit once by retrive an XML and write some XML function
to deal with the aggregation).

if you need the exact solution, i'll try to hel you.

Shlomi

> Thanks for your reply - I think maybe I did not explain this well
> enough: The place where I need to call the stored proc is not while
[quoted text clipped - 26 lines]
> > >
> > > Thanks for any help!

Reply to this Message

megabyte - 30 Jul 2006 18:59 GMT

Yes, 1 is what I'm after - writing custom code for reading from the DB
at runtime and calling it from the dev. environment - that's exactly
what I don't know how to do - it's VB.Net language, right? I have no
idea how to do that - do you? Do you know of sample code out there?

> Hi
> I can think about two ways to do this:
[quoted text clipped - 39 lines]
> > > >
> > > > Thanks for any help!

Reply to this Message

Shlomi - 30 Jul 2006 20:11 GMT

Hi
This MSDN article will tell you everything you want to know about it:
http://msdn2.microsoft.com/en-us/library/ms153561.aspx
if you have more questions, you will be more than welcome.
P.S.
Use the Microsoft enterprise library to read your data.

> Yes, 1 is what I'm after - writing custom code for reading from the DB
> at runtime and calling it from the dev. environment - that's exactly
[quoted text clipped - 44 lines]
> > > > >
> > > > > Thanks for any help!

Reply to this Message