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.
| Author |
Topic |
|
cwildeman
Starting Member
40 Posts |
 Posted - 2011-03-29 : 13:54:59
|
| ALTER PROCEDURE [dbo].[rpt_HAC_HACType] @StartDate datetime,@EndDate datetime--------------------------------------------------------My where clause is the following:WHERE dbo.vEncounter.DischDate >= @StartDate) AND (dbo.vEncounter.DischDate < @EndDate) ------------------------------------------------------I have a field in the query of my stored procedure that I want to add to the parameters of an SSRS report called dbo.HACList.PayerType. It is varchar(50). I want the user to select a drop down which would have two values and pick one of them in the SSRS report along with the date range. How do I alter the stored procedure and add this field?Thanks,Chuck W |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-03-29 : 14:14:03
|
| ALTER PROCEDURE [dbo].[rpt_HAC_HACType] @StartDate datetime,@EndDate datetime,@PayerType varchar(50) = NULL (might not want the = NULL part if you want to force them to select a value)Your WHERE clause just needs to do the comparison of your new variable against the respected column.AND @PayerType = ColumnNameor possiblyAND ColumnName LIKE '%PayerType%'Hey, it compiles. |
 |
|
|
cwildeman
Starting Member
40 Posts |
Posted - 2011-03-29 : 16:57:01
|
| Thanks for your help. That did work. A couple of questions or problems as a follow up. There are three possible values for the payertype field: Medicare FFS, Other or a Null value. My parameter has a text value when I run it in SSRS so I can type in Medicare FFS or Other and get the results. If I leave it blank I get an error. It would be nice to have a drop down if possible. Also, is there a way to get a report that would have all three types of values?Chuck W |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-03-29 : 19:04:51
|
| Are you using SSRS as your reporting tool? If so, the answers are Yes and Yes. Each parameter can be defined to have a set of values to choose from. This can be from a DataSet where, typically, a select statement, or from an explicit list. Also, the parameter can be defined to accept multiple values. In this case the items in your drop down list will have check boxes. Any checked items will be passed as a CSV string. Your procedure will need to extract the strings.The use of Null as a legitimate value in the list makes things a bit different. You might need to resort to a "magic" value in the list which your sproc recognizes and makes the necessary translation.BTW, if your not using SSRS, there is probably a way to accomplish this. It's a common need which most report tools would need to address.=======================================Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986) |
|
|
|
|
|
|
|
|
Alter existing stored procedure and add field