Query implementation help.

  • I have about 50 fields (client attributes) that needs to be shown on the report. Client may or may not want to see all the 50 fields.

    Through the web (i am thinking), they will select some fields and query needs to be run on those fields only...Thos 50 fields may be coming from

    several tables. Also, they need to be able to rename the fields as they wish.

    Also another client may want the field to be named differently; I hope i am making sense here. What would be the best way to go around this?

  • I think the only possible solution would be dynamic SQL. Get all your info (including required fields, alternate fieldnames, tables, etc.) from the client and build the query with this information as a string.

    Because you don't know the requested number of fields and tables on forhand, you have to use code to create a comma separated column list (including the alternate field names) plus a table joining string and probably also create a dynamic where-clause. You'll need good error handling to make sure you pass a valid SQL statement to the database.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • And be VERY VERY careful if you use the dynamic sql approach to protect yourself from sql injection. NEVER run a dynamic string that has user entered content as part of that string. You will need to use sp_executesql and pass it parameters.

    It sounds like you basically creating a dynamic query builder. It will be extremely tedious and difficult to get it right.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • If you decide to try using dynamic SQL, give this a read first...

    The Curse and Blessings of Dynamic SQL

  • Dynamic report builders are tricky and nasty. If this doesn't have to be real time data, check into SSAS cubes and Powerpivot for your power users.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Doesn't have to be real time data. Would cube work if there is no facts? I mean just the dimensions. Doesn't sound like they need any facts on this attribute list.

  • SQL_Surfer (5/21/2013)


    Doesn't have to be real time data. Would cube work if there is no facts? I mean just the dimensions. Doesn't sound like they need any facts on this attribute list.

    Well... ish. You usually have to have SOMETHING that's a fact table somewhere if you're reporting on stuff. It may not be a clean DW build, but that's not always that important. Somewhere, you've got fact tables. It might be simply an invoicing table, but it's there.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply