February 9, 2017 at 9:58 am
Hi,
example
in report dataset query defined as
select * from sysobjects where name like = @Name or type =@Type
if user does not pass @name or @Type can we in query designer modify it similar something like
select * from sysobjects where
if (@name is not null)
begin
name like = @Name or
end
if(@type is not null)
begin
type =@Type
end
if @name is null and @type is null
begin
select * from sysbojects
end
I don't want to create stored procedure, build dynamic sql add logic based on paramaters of sp
Thank you
February 9, 2017 at 10:03 am
You can do it like that:
select * from sysobjects where (name like = @Name OR @Name is null) or (type =@Type OR @Type is null)
Notice that queries like that tend to have poor query plan and slow execution. It is better to use the logic that in your question you wrote that you don't want to use.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2017 at 12:04 pm
Thank a lot .
Provided solution is working fine
February 9, 2017 at 12:12 pm
ebooklub - Thursday, February 9, 2017 12:04 PMThank a lot .
Provided solution is working fine
Think so? Please read the following article on "Catch-All Queries" and see how future performance will tank and what you can do about it. The article should be required reading for all Developers.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply