Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    9

    Unanswered: How to make view name dynamic

    Hi All,

    I've searched everywhere but could not find right reference to my query so am creating new thread. Sorry if it is a duplicate.

    Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.

    Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic. I would appreciate if someone can walk me through this or direct me to some good references. Thanks for your help in advance.

    Select C1, C2, C3
    From v_@study_form

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try something like this:

    Code:
    declare @SQL nvarchar(max);
    set @SQL = N'Select C1, C2, C3 From v_' + @study + N'_form';
    execute sp_executesql @SQL;
    Hope this helps.

  3. #3
    Join Date
    May 2012
    Posts
    9

    Thank You

    Hi Imex,

    Thanks for quick response. Before trying your suggestion would like to confirm with you about the following.

    Basically, there is insert statement above select statement I mentioned which looks something like this

    Insert Into #A (C1,C2,C3,C4,C5)
    Select a.C1,
    a.C2,
    a.C3,
    b.C4,
    b.C4
    From v_@study_form a
    Join fffgg b

    As per your suggestion this is what statement should be like. Correct?

    declare @SQL nvarchar(max);
    set @SQL = N'Insert Into #A (C1,C2,C3,C4,C5)
    Select a.C1,
    a.C2,
    a.C3,
    b.C4,
    b.C4 From v_' + @study + N'_form'
    N'Join ffgg b';
    execute sp_executesql @SQL;

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    declare @SQL nvarchar(max);
    set @SQL = N'Insert Into #A (C1,C2,C3,C4,C5) ' +
               N'Select a.C1, a.C2, a.C3, b.C4, b.C4 ' +
               N'From v_' + @study + N'_form ' +
               N'Join ffgg b';
    execute sp_executesql @SQL;
    Hope this helps.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rookie, is it too late for you to normalize your data and avoid all the mess (and security risk) of dynamic SQL?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2012
    Posts
    9
    Hi Blindman,

    I'm relatively new to dynamic SQL. What exactly is the mess and risk with it?

    Hi Imex,

    Thanks again for prompt response.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Think about these...

    How would you write a query to show how many rows have C4 = somevalue across all views?

    When you get a few dozen of these views, and you are asked to retrieve all rows associated with one customer, how many queries do you need to write?

    And that is really just the tip of the iceberg.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The mess, you are already experiencing.
    The risk, primarily, is a form of hacking called "SQL Injection" where people actually submit SQL Statements as parameter values which then get blended into the existing statement and executed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •