May 3, 2006 at 1:31 am
Hi all
I want to go for the optional parametres where i can select only one of the parameters and i can select all the records corresponding to that particular parameter ,the remaining parametres should not apply for filteration at this time. the same with the other two parameters. i want to go to other parametres if and only if i have additional information about that parametres
i would like to say this with an example
1)unit no
2)invoice date
3)invoice number
if i know only unit no , the records should get filtered based on that filter and the reamining two filters should not play a role in the filteration
Any help would be greatly appreciated
Thank you
Raj Deep.A
May 3, 2006 at 3:25 am
create proc myproc @unit nvarchar(10)=NULL,@invdate datetime=NULL,@invno nvarchar(10)=NULL
as
declare @strsql nvarchar(1000)
set @strsql='select * from mytable where 1=1 '
if @unit is not null
begin
set @strsql=@strsql+' and unit='''+@unit+''''
end
if @invno is not null
begin
set @strsql=@strsql+' and invoice_no='''+@invno+''''
end
if @invdate is not null
begin
set @strsql=@strsql+' and invoice_date='''+convert(nvarchar(11),@invdate)+''''
end
exec @strsql
may not be exactly correct and is definatley not best practice but it's quick and easy
MVDBA
May 4, 2006 at 8:55 am
You can do the same thing without dynamic sql
create proc myproc @unit nvarchar(10)=NULL,@invdate datetime=NULL,@invno nvarchar(10)=NULL
as
select * from mytable
where
(@unit = unit or @unit is null)
and
(@invdate = invoice_date or @invdate is null)
and
(@invno = invoice_no or @invno is null)
May 4, 2006 at 8:56 am
You can default all of the filters to return all records, and then if you select one of the parameters, it will filter by that one.
So, for the unit no and invoice number dropdowns, create a union select all and the "all" is the default.
For the date, default to return all records from 1/1/1900 to 1/1/2999 or something.
Then in the stored proc for the report dataset, do something like:
WHERE (tablename.unitno = @unitno OR @unitno = 'All') AND
(tablename.invoicenum = @invoicenum OR @invoicenum = 'All') AND
(tablename.invoicedate between @start and @end)
There are a couple other posts on this in the forum if you do a search.
Good luck.
-Megan
May 4, 2006 at 10:48 am
I take a different approach here
create proc myproc @param1 varchar(1) = null,@param2 varchar(1) = null
as
SELECT* from mytable WHERE mycol = CASE WHEN param1 is null THEN mycol ELSE @param1 END AND mycol2 = CASE WHEN @param2 is null THEN mycol2 ELSE @param2 END
May 5, 2006 at 2:54 am
Hi All
so What i thought is i can use isnull(@paramvalue,columnname) in the where clause.
The above all will work ,when and only when the Allow Null value is enabled in the Report property.so when i do this i see a null checked initially in the preview.It is showing some odd look here where user has to uncheck that one first and has to go selection which is of two steps. for one step process
Any ideas pls
Thank you
Raj Deep.A
May 5, 2006 at 7:07 am
1) My opinion, the point of an SP is to protect the underlying table from direct access. Using dynamic SQL will require you to give select permssions on the table so it is not usually suggested against a table. As well yo open yourself to injection attacks that way.
2) I usually use stored procedure groups, or a set of procs as I am trying to determin which really does have the best performance long term as I have discovered that the proc cache may actually be being recompiled anyway in a proc group but I haven't full confimed yet. I have a primary proc which makes the decision of the secondary run.
Proc Group Method
create proc myproc;1
@unit nvarchar(10)=NULL,
@invdate datetime=NULL,
@invno nvarchar(10)=NULL
as
If @unit is not null
Begin
EXEC dbo.myproc;2 @unit
End
Else
Begin
If @invdate is not null
Begin
EXEC dbo.myproc;3 @invdate
End
Else
Begin
EXEC dbo.myproc;4 @invno
End
End
GO
create proc myproc;2
@unit nvarchar(10)=NULL
as
select * from mytable
where
@unit = unit
GO
create proc myproc;3
@invdate datetime=NULL
as
select * from mytable
where
@invdate = invoice_date
GO
create proc myproc;4
@invno nvarchar(10)=NULL
as
select * from mytable
where
@invno = invoice_no
go
Parent/Child Proc Method
create proc myproc
@unit nvarchar(10)=NULL,
@invdate datetime=NULL,
@invno nvarchar(10)=NULL
as
If @unit is not null
Begin
EXEC dbo.myproc_2 @unit
End
Else
Begin
If @invdate is not null
Begin
EXEC dbo.myproc_3 @invdate
End
Else
Begin
EXEC dbo.myproc_4 @invno
End
End
GO
create proc myproc_2
@unit nvarchar(10)=NULL
as
select * from mytable
where
@unit = unit
GO
create proc myproc_3
@invdate datetime=NULL
as
select * from mytable
where
@invdate = invoice_date
GO
create proc myproc_4
@invno nvarchar(10)=NULL
as
select * from mytable
where
@invno = invoice_no
go
May 5, 2006 at 8:53 am
Raj -
I don't know if I explained "my" method very well, but you don't have to allow nulls, you just have to default the parameters to "All" and default the dates to a long, long time ago and sometime in the way future (not that the user has to see the date defaults).
SSC -
What if the user selects more than one of the parameters? That's a lot of stored procedures...
Good luck.
-Megan
May 5, 2006 at 9:44 am
Yes it would be. All depends on how much perfomance hit you are willing to accept. I always evaluate that fact in a decision. But based on the comment that onl one will ever be set, for the sake of this conversation my options are based on that fact and are simply offerin another possibility. As for if the condition of more than one option is choosen you can still do in the 4 but have to weigh performance conditions such as indexs and such into the mix. But by seperating them this way you are less likely to get recompiles (especially in the second still trying to verify what I saw recently on the first versus the information I have had in the past) and more likely to get reusably execution plans.
Even with that I will still tweak a query and set of procs until I can get an optimal situation long term. As for your item it is also a valid option as many of the others are. But in suggesting with those I would include WITH RECOMPILE in th Proc if a pretty evenly choosen and if any or all have indexes. If not and will always perform a table can then it would be fine without. The reason is if you know you are likely to get a Proc Cache Miss you are better off to use the WITH RECOMPILE option for performance.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply