Dynamically edit DataSet

Hi,

I have a web application, where the user specifies the criteria for filtering data (with like, In, between... operators and data retrieved from the db). So, the selection criteria query (SQL) is generated. This is somthing similar to Query Wizard. It builds the SQL for WHERE clause only. This needs to be attached with the query (the developer specified during report-design, which is under dataset->commandtext in RDL file).

To clarify, do not want to use parameters. The idea is, the report design need not be modified each time any filters to be added or removed. The user can simply, add/remove the fields and corresponding sql statements for each field from the db (e.g. QueryWizard table).

The functionality to attach the where clause (from web page) to query (specified in reprot-design) is ready. I need to know, is there anyway I can retrieve the query from the RDL programmatically and set it back before the report is rendered (generated).

I heard something about QueryDefinition.Query, which returns the query, but not sure.

Anybody, any suggestion, will be a great help !!

Have you tried Report Builder? What you want sounds very close to what Report Builder is doing (RS 2005)|||

I have already explored the option of using the Report Builder. I do not want the end-user to design the report. The end-user should be able to specify the criteria (easy-steps thru Query Wizard) and view the report.

However, in case of Report Builder also, if I want to add new fields for selection criteria, the design of the report requires modification, which is what I dont want to do.

Is there any option? Cannt I edit the dataset programmatically ?

|||

Well, DataSet.Query.CommandText can be an expression.
You can try something like this: ="select * from mytable " + ReportParameters!Filter.Value

dynamically creating a select statement

I have a stored procedure in my database which will be used to search for records matching given criteria, such as within a date range or containing a keyword. The procedure stub is looking like this at the moment:

ALTER PROCEDURE [dbo].[search]
@.file_id int,
@.title_includes varchar(50),
@.notes_includes varchar(50),
@.updated_after datetime,
@.updated_before datetime,
@.deleted_after datetime,
@.deleted_before datetime,
@.size_bigger_than int,
@.size_smaller_than int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END

Ideally I would like criteria to only be used in the select statement if the value passed in is not null, but as far as i know it is not possible to place and if-then condition in the middle of a select statement to check if the parameter is null? Does anyone know of an efficient way to achieve this functionality? Any help will be greatly appreciated, thank you.

There's two approaches that I can think of that might help you.

Firstly, it's entirely valid to include criteria such as

WHERE (file_id = @.file_id or @.file_id is null)
AND (title_includes = @.title_includes or @.title_includes is null)
AND ...

in the stored procedure.

However, if you mean that you only want to see the file_id column in the results if the passed parameter @.file_id is not null, then you need to approach it differently, as follows:

declare @.select nvarchar(max)
declare @.where nvarchar(max)
declare @.selectsep nvarchar(5)
declare @.wheresep nvarchar(5)

set @.select = N''
set @.selectsep = N''
set @.where = N''
set @.wheresep = N''

if (@.file_id is not null)
begin
set @.select = @.select + @.selectsep + N'file_id'
set @.where = @.where + @.wheresep + N'file_id = @.file_id'
set @.selectsep = N','
set @.wheresep = N' and '
end

if (@.title_includes is not null)
begin
set @.select = @.select + @.selectsep + N'title_includes'
set @.where = @.where + @.wheresep + N'title_includes = @.title_includes'
set @.selectsep = N','
set @.wheresep = N' and '
end

... and so on for the other parameters ...

set @.select = N'SELECT <list of fields you always want to include>, ' + @.select + N'FROM <from clause>'
if (len(@.where) > 0)
begin
set @.select = @.select + N' WHERE ' + @.where
end

exec dbo.sp_executesql @.select
, N' @.file_id int, @.title_includes varchar(50), @.notes_includes varchar(50), @.updated_after datetime, @.updated_before datetime, @.deleted_after datetime, @.deleted_before datetime, @.size_bigger_than int, @.size_smaller_than int'
, @.file_id = @.file_id
, @.title_includes = @.title_includes
, @.notes_includes = @.notes_includes
, @.updated_after = @.updated_after
, @.updated_before = @.updated_before
, @.deleted_after = @.deleted_after
, @.deleted_before = @.deleted_before
, @.size_bigger_than = @.size_bigger_than
, @.size_smaller_than = @.size_smaller_than

Naturally, as you build up the @.select and @.where variables, you can include the usual range of operators (like, <, >, etc).

Let me know if you need any further assistance with this.

Iain

|||

You can try this:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SearchCurrency]

@.currencycode nchar(5) = null,

@.currencyname nchar(25) = null

as

begin

declare @.stmt nvarchar(max)

set @.stmt = 'SELECT currencycode, currencyname

FROM currencies where 1=1 '

IF @.currencycode IS NOT NULL

set @.stmt = @.stmt + ' AND currencycode = '''+ @.currencycode + ''''

IF @.currencyname IS NOT NULL

set @.stmt = @.stmt + ' AND currencyname = ''' + @.currencyname +''''

exec(@.stmt)

end

|||

I highly recommend reading Erland's article on Dynamic SQL before going down this path. It may work well for you, but you should be properly informed about the pitfalls.

See:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Dynamic WHERE clause (SQL server 2005)

I'm in a situation where a user should be able to choose what data to
retrieve from a table. The criteria is not constant, sometime it is included
,
other times not. The problem I'm facing is how to create a dynamic WHERE
clause. I would prefere to avoid client side embedded SQL.
My first idea was to create a table valued functions that takes the criteria
and using dynamic SQL returns a table with the subset of data. The reason I
chose a TVF is so I can call it from multiple stored procedures.
The following function is accepted by SQL server, but when I run against it
I get the following error 'Only functions and extended stored procedures can
be executed from within a function.'
ALTER FUNCTION [dbo].[GetTagIds2]
(
@.TagMask NVARCHAR(50)
)
RETURNS @.ResultTable TABLE
(
TagId INT PRIMARY KEY NOT NULL,
Name NVARCHAR(100) NOT NULL
)
AS
BEGIN
DECLARE @.DynSql VARCHAR(1024);
-- this works fine
-- INSERT INTO @.ResultTable(TagId,Name)
-- SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag
-- WHERE IMS_Tag.Name LIKE(@.TagMask)
SET @.DynSql = @.DynSql + 'INSERT INTO @.ResultTable(TagId,Name)';
SET @.DynSql = @.DynSql + 'SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag';
SET @.DynSql = @.DynSql + 'WHERE IMS_Tag.Name LIKE(@.TagMask)';
EXEC sp_executesql @.DynSql;
RETURN
END
Any idea why this is not working?
If there are any better ways of doing this please let me know.You cannot use dynamic SQL in a function; you will need to use a different
approach.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/share_data.html
"Christopher Kimbell" <c_kimbell@.newsgroup.nospam> wrote in message
news:0BB5E543-76BC-4EC7-B038-DF0E9308D3DC@.microsoft.com...
> I'm in a situation where a user should be able to choose what data to
> retrieve from a table. The criteria is not constant, sometime it is
> included,
> other times not. The problem I'm facing is how to create a dynamic WHERE
> clause. I would prefere to avoid client side embedded SQL.
> My first idea was to create a table valued functions that takes the
> criteria
> and using dynamic SQL returns a table with the subset of data. The reason
> I
> chose a TVF is so I can call it from multiple stored procedures.
> The following function is accepted by SQL server, but when I run against
> it
> I get the following error 'Only functions and extended stored procedures
> can
> be executed from within a function.'
>
> ALTER FUNCTION [dbo].[GetTagIds2]
> (
> @.TagMask NVARCHAR(50)
> )
> RETURNS @.ResultTable TABLE
> (
> TagId INT PRIMARY KEY NOT NULL,
> Name NVARCHAR(100) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.DynSql VARCHAR(1024);
> -- this works fine
> -- INSERT INTO @.ResultTable(TagId,Name)
> -- SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag
> -- WHERE IMS_Tag.Name LIKE(@.TagMask)
> SET @.DynSql = @.DynSql + 'INSERT INTO @.ResultTable(TagId,Name)';
> SET @.DynSql = @.DynSql + 'SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag';
> SET @.DynSql = @.DynSql + 'WHERE IMS_Tag.Name LIKE(@.TagMask)';
> EXEC sp_executesql @.DynSql;
> RETURN
> END
> Any idea why this is not working?
> If there are any better ways of doing this please let me know.|||Christopher Kimbell wrote:
> I'm in a situation where a user should be able to choose what data to
> retrieve from a table. The criteria is not constant, sometime it is
> included, other times not. The problem I'm facing is how to create a
> dynamic WHERE clause. I would prefere to avoid client side embedded
> SQL.
See Erland's article on this topic here:
http://www.sommarskog.se/index.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks guys!
The information was very usefull.