Re: Stored procedure parameters and default values -- sql server reporting services

Dave
8/30/2004 2:11:22 PM

bringing an old thread back to life....

How about this question:

I have a stored procedure called "get_months" that shows a list of
available months specific to the user who runs the stored proc (you
pass in an @emp_id stored proc parameter). I'd like to show the output
of this as the dropdown box for the user to choose a month. Then they
hit Go and RS shows the main query information (which is not relevant
to my question here).

My question is: how do I call my helper stored proc "select_user_info"
which returns various info about the current logged in user.
select_user_info returns 1 row with various columns, the most important
of which is emp_id. I want to then pass that value to "get_months".

I can't hard code the emp_id query parameter value to get_months, since
it's variable upon who is logged in.

I think I could do this by creating a custom c# assembly and getting
the emp_id value out of that and then passing it to the get_months
DataSet, but I'd like to learn how to do it without a custom assembly.
TIA!

Bruce Loehle-Conger
8/30/2004 4:17:55 PM

The cleanest is to pass the userid to the stored procedure. Then have a
table that maps the userid to the employeeid. The userid is a global
(user!userid) that you can map to the stored procedure parameter.

Bruce L-C

[quoted text, click to view]

sql server reporting services : Re: Stored procedure parameters and default values