Dynamically generate where condition

If we have a pre-defined SQL statement without "where conditions", how do I dynamically insert the where conditions into it (by user input or whatever)? Is there any free tool available for carrying out this kind of work?

Regards,

Ricky.

Ricky,

If the Where condition must be added to a Sql statement say within a Stored Procedure,

then you could use dynamic SQL statements.

declare @.sql varchar(255),

Select @.sql ="Predefined SQL statement" + "The where condition which can be input to the SP"

Exec @.sql

Sandoty

|||

Be cautious when you use the dynamic sql queries. It may cause two preaches,

1. SQL Injection

2. Permission issues

SQL Injection,

Your code is allowing the end user to inject the SQL command on your own code. So they can vulnerable your database be cautious here. Try to eliminate the comments (-- or /**/) as your input. You can control it on the UI itself. There are lots more practices are there. Google it for SQL Injection. You will get better idea.

Permission Issues,

Dynamic sql will expect the required permission on the source tables, if current user doesn’t have the permission on the table level (but on the SP), the dynamic SQL won’t work.

You can achieve the filter conditions from the SPs or prepared query itself. If the intention is reducing the code & time to achieve this task Pls take care the above issues.

|||

One more point about dynamic queries is the performance issues.

Each time you want to run them the sql command has to be recompiled in order to get an execution plan.

This may be positive in some situations. But most of the time the best way is to set your application to run with specific queries.|||

You can find a lot of information about this theme in the following articles.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

AMB

Dynamically changing position of Textbox

Hi,

In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.

Thanks in Advance,

Leks

What does the surrounding content in the layout look like? Do you have a "clear" region in which you could place a very large textbox, representing the all the page area in which the textbox could potentially be placed?

If so, you might be able to do this with padding. IOW, you would place this very large textbox in the layout and then you would pad the top and left to "push" the content of the text box into the customer's preferred position, at runtime.

This is possible because you can express the padding values as expressions. Think of the textbox's true top and left as an offset from 0,0, which you will subtract from the customer's input values (which will be parameters) to get the padded top and left at runtime.

HTH,

>L<

|||

Hi Lisa,

Thanks for your quick reply. But unfortunately my layout is not clear. It contains other data too that I need to position according to the input for them. For each item in the report we have to enter the position. I know this process is a bit tedious. but there is no other ways. They actualy need the report data such that it can be printed in many forms. Please reply as soon as possible.

Thank you dear,

Leks

|||

Hi Leks,

If you have to enter the position of each item in the report, then I think your best option is to edit the RDL on the fly, put your coordinates right in the report definition.

Are you comfortable with doing this? It may be quite straightward. Some things that would enter into it and might change what you have to do would include:

* -- What type of data region are you using (list/table) to contain the dynamic items?

* -- Are there additional items in the report that are *not* dynamically positioned, for example is this an order header you are trying to position so that it fits in a window envelope with a table of order lines that is relatively static?

>L<

|||

Hi Lisa,

Here all the data should change position dynamically. So Your solution shoud work. But I didnt get it. How we can change the rdl on the fly?

Thanking you,

Leks

|||

You can write a whole RDL on the fly if you want to. Look for an example called something like "generate RDL dynamically" on http://gotreportviewer.com/ and there is another example there that will show you how to LoadReportDefinition for a local report from a stream. If the report is a serverreport, you could use the SOAP API to upload the adjusted report definition instead.

In your case, I don't think that it will be necessary to write the whole RDL from scratch (although you will still have to load or upload your revised version). You can have a "template" RDL that you adjust, for each run or each customer, or as needed.

There are two ways to do an RDL template revision, the way I would do it and the way most developers would prefer to do it <g>. I prefer XSLT; in this case I might have a parameter in the XSLT for each positionable item in the report, or pass the entire set of adjustments in the form of a lookup document. If you prefer to load up the RDL in the DOM and select the textboxes as nodes to adjust each of them, this is pretty easy too.

Do you need more help than this, or is this what you're looking for?

>L<

|||

Hi Lisa,

Yes. This was what I was looking for. Now I know that using the XSLT we can position the textbox or any control dynamically.

Thank you so much Lisa,

Leks

|||

Great -- if you get stuck, holler. I am very willing to go the extra mile for other folks who like XSLT <s>.

>L<

Dynamically changing position of Textbox

Hi,

In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.

Thanks in Advance,

Leks

What does the surrounding content in the layout look like? Do you have a "clear" region in which you could place a very large textbox, representing the all the page area in which the textbox could potentially be placed?

If so, you might be able to do this with padding. IOW, you would place this very large textbox in the layout and then you would pad the top and left to "push" the content of the text box into the customer's preferred position, at runtime.

This is possible because you can express the padding values as expressions. Think of the textbox's true top and left as an offset from 0,0, which you will subtract from the customer's input values (which will be parameters) to get the padded top and left at runtime.

HTH,

>L<

|||

Hi Lisa,

Thanks for your quick reply. But unfortunately my layout is not clear. It contains other data too that I need to position according to the input for them. For each item in the report we have to enter the position. I know this process is a bit tedious. but there is no other ways. They actualy need the report data such that it can be printed in many forms. Please reply as soon as possible.

Thank you dear,

Leks

|||

Hi Leks,

If you have to enter the position of each item in the report, then I think your best option is to edit the RDL on the fly, put your coordinates right in the report definition.

Are you comfortable with doing this? It may be quite straightward. Some things that would enter into it and might change what you have to do would include:

* -- What type of data region are you using (list/table) to contain the dynamic items?

* -- Are there additional items in the report that are *not* dynamically positioned, for example is this an order header you are trying to position so that it fits in a window envelope with a table of order lines that is relatively static?

>L<

|||

Hi Lisa,

Here all the data should change position dynamically. So Your solution shoud work. But I didnt get it. How we can change the rdl on the fly?

Thanking you,

Leks

|||

You can write a whole RDL on the fly if you want to. Look for an example called something like "generate RDL dynamically" on http://gotreportviewer.com/ and there is another example there that will show you how to LoadReportDefinition for a local report from a stream. If the report is a serverreport, you could use the SOAP API to upload the adjusted report definition instead.

In your case, I don't think that it will be necessary to write the whole RDL from scratch (although you will still have to load or upload your revised version). You can have a "template" RDL that you adjust, for each run or each customer, or as needed.

There are two ways to do an RDL template revision, the way I would do it and the way most developers would prefer to do it <g>. I prefer XSLT; in this case I might have a parameter in the XSLT for each positionable item in the report, or pass the entire set of adjustments in the form of a lookup document. If you prefer to load up the RDL in the DOM and select the textboxes as nodes to adjust each of them, this is pretty easy too.

Do you need more help than this, or is this what you're looking for?

>L<

|||

Hi Lisa,

Yes. This was what I was looking for. Now I know that using the XSLT we can position the textbox or any control dynamically.

Thank you so much Lisa,

Leks

|||

Great -- if you get stuck, holler. I am very willing to go the extra mile for other folks who like XSLT <s>.

>L<