Small Issue With Parameters - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Small Issue With Parameters

Author

Topic

Treusser
Starting Member

3 Posts

Posted - 2011-09-14 : 18:30:39

I have created a query for SQL Reporting Services that will pull data based on the either the location of a bill, or the place of service associated with the charge. The report runs beautifully and will successfully change the output if i change the line (billst.location = @POS) to (LineItemT.LocationID = @POS), with @POS being a parameter value of 1,3, or 4. No problems with the report generated.

SELECT PatientsT.PersonID AS PatID,
'Place of Service' = case
when billst.location = 1
Then 'Fort Worth'
when billst.location = 3
Then 'Weatherford'
when billst.location = 4
Then 'Dallas'
Else ','
End,
PatientsT.UserDefinedID, ChargesT.DoctorsProviders AS ProvID, LineItemT.Date AS TDate,
LineItemT.InputDate AS IDate, LocationsT.Name AS LocationName,
PersonT.Last + ', ' + PersonT.First + ' ' + PersonT.Middle AS PatName, PersonT1.Last + ', ' + PersonT1.First + ' ' + PersonT1.Middle AS ProvName,
ChargesT.ItemCode, LineItemT.Amount AS UnitFee, ChargesT.CPTModifier, LineItemT.Description, ChargesT.Quantity,
dbo.GetChargeTotal(ChargesT.ID) AS Amount
FROM ChargesT

RIGHT JOIN billst
ON Chargest.BillId = Billst.Id

INNER JOIN LineItemT
ON ChargesT.ID = LineItemT.ID

LEFT JOIN LocationsT
ON LineItemT.LocationID = LocationsT.ID

LEFT JOIN PatientsT
ON LineItemT.PatientID = PatientsT.PersonID

INNER JOIN PersonT
ON PatientsT.PersonID = PersonT.ID

LEFT JOIN PersonT PersonT1
ON ChargesT.DoctorsProviders = PersonT1.ID

LEFT JOIN CPTModifierT
ON ChargesT.CPTModifier = CPTModifierT.Number

LEFT JOIN CPTModifierT CPTModifierT2
ON ChargesT.CPTModifier2 = CPTModifierT2.Number

LEFT JOIN GCTypesT
ON ChargesT.ServiceID = GCTypesT.ServiceID

WHERE (
(LineItemT.Deleted = 0)
AND (LineItemT.Type = 10)
AND GCTypesT.ServiceID IS NULL )
AND ((billst.location = @POS)
AND (( LineItemT.Date = @StartDate AND LineItemT.Date <= @EndDate)))

However, if I change (Billst.location = @POS) to (@ReportType = @POS) with @ReportType being a parameter with the string values of LineItemT.LocationId or Billst.location, the report is blank.
SQL profiler shows the query as being excuted properly with the following at the end.

,N'@ReportType nvarchar(19),@POS nvarchar(1),@StartDate datetime,@EndDate datetime',@ReportType=N'billst.location',@POS=N'4',@StartDate=''2011-09-13
00:00:00:000'',@EndDate=''2011-09-13 00:00:00:000''

What I am I missing that @ReportType is causing the report to fail even though it appears to passing the correct value?

bobmcclellan
Starting Member

46 Posts

Posted - 2011-09-16 : 15:26:44

One way that is frowned upon in most cases is dynamic sql. There are risks involved such as sql injection that have plenty of articles you can google for.
But in a nut shell you would store the whole sql string up to 'AND ((' in one param and the remainder in a 2nd param. So if you
Set @ReportType = 'Billst.location' you could then set @param3 = rtrim(@param1) + ReportType + rtrim(@param2)
... then exec (@param3)

if you have jobs built... this is an example of how it would work...
declare @p1 nvarchar(50),@TableSource nvarchar(50),@p2 nvarchar(50), @p3 nvarchar(150)
set @p1 = 'Select top 5 * from '
set @TableSource = 'msdb.dbo.sysjobs'
set @p2 = ' where enabled = 1'

set @p3 = rtrim(@P1) + ' ' + rtrim(@TableSource) + ' ' + rtrim(@P2)

select @p3

exec (@p3)

hth,
..bob

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-16 : 17:30:41

Not sure I follow your exact problem but one thing I see is your t-sql:

AND (( LineItemT.Date = @StartDate AND LineItemT.Date <= @EndDate)))

for these values:
@StartDate=''2011-09-13 00:00:00:000'',@EndDate=''2011-09-13 00:00:00:000''

will only return rows that have the exact datetime of 2011-09-13 00:00:00:000

I would think you want that as (assuming you want all rows for anytime on the 9/13):
LineItemT.Date >= '2011-09-13 00:00:00:000' AND LineItemT.Date < '2011-09-14 00:00:00:000'

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources