dynamic sql

  • I have to write a stored procedure which serves as a universal stored procedure for all the tables in the database...

    From the application side he will pass me the table name then in return

    i have to pass all the columns for that table.

    for example if he passes the table name as ABC

    then i have to pass all the column values to application side related to table ABC...so dynamically i have to generate the select code with the table name inside the procedure

  • There's no point in using a sp for that... just do it adhoc.

    What's the real business need behind this request?

  • Best tools for that would be any of the ORM tools, like Linq or nHibernate. Don't do it in a proc, you'll just end up with injection vectors all over the place.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • There are like 50 look up tables in our database...

    then in all the look up tables he has like Active or Inactive values...

    so based onthe acitve/inactive values he has to set the colors onthe application side...

    so he was asking me to create a stored procedure where he will pass the table name then i have to return all the column values related to that table.

  • Sorry to hit the same nail over and over again. But a SINGLE sp is not the correct tool for this.

    ORM or linq seems much better.

    Or you can go the route of generating the 50 procs once and then calling the correct one. That one could make some sort of sense but not 1 proc for all tables.

  • How about some dynamic sql to dynamically create the stored procedure for doing this properly instead?

    SELECT 'CREATE PROCEDURE dbo.[spGet_' + ss.NAME + '_' + st.NAME + '] AS SELECT ' + STUFF((SELECT ',' + sc.name

    FROM sys.columns sc

    WHERE sc.object_id = st.OBJECT_ID

    ORDER BY sc.column_id

    FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'') +

    ' FROM ' + QUOTENAME(ss.NAME) + '.' + QUOTENAME(st.NAME) + ';'

    FROM sys.tables st

    JOIN sys.schemas ss

    ON st.schema_id = ss.schema_id;

    Then just have him dynamically generate the procedure name (should be easy since he knows the table name already).

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Wayne, even that's a bad idea.

    He'll end up having to query metadata for the returned dataset, unless all the tables have identical structures (in which case, why 50 tables, why not 1 table with a "category" column or some such?).

    Linq or any of the other ORM products will that part of the work for him.

    As per Leroy Jenkin's post ... I mean Joe Celko's post, there are some issues here beyond that, but it has to start being fixed somewhere.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hi GSquared,

    This is meant to be run one time to create the procs, that are then used to get the data. Of course, they need some modifications to add a where clause, but other than that, what is wrong with this?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • CELKO (5/31/2011)


    Do you also believe in the Magical, Universal Elixir that will cure all diseases and restore youth?

    Hell YES!!! It's called "Beer"! 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lucky9 (5/31/2011)


    I have to write a stored procedure which serves as a universal stored procedure for all the tables in the database...

    From the application side he will pass me the table name then in return

    i have to pass all the columns for that table.

    for example if he passes the table name as ABC

    then i have to pass all the column values to application side related to table ABC...so dynamically i have to generate the select code with the table name inside the procedure

    I agree with the others. Without knowing a whole lot more about why this is being done, this is probably a really bad thing to do. That, notwithstanding, the following will easily do the trick. Change it to your liking and change it to a stored proc.

    USE AdventureWorks

    DECLARE @pSomeSchemaName SYSNAME,

    @pSomeTableName SYSNAME;

    SELECT @pSomeSchemaName = 'Production',

    @pSomeTableName = 'ProductPhoto'

    SELECT Ordinal_Position, Column_Name

    FROM Information_Schema.Columns

    WHERE Table_Schema = @pSomeSchemaName

    AND Table_Name = @pSomeTableName

    ORDER BY Ordinal_Position

    Ah... my bad. I didn't see the word "values" in the requirements.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • WayneS (5/31/2011)


    Hi GSquared,

    This is meant to be run one time to create the procs, that are then used to get the data. Of course, they need some modifications to add a where clause, but other than that, what is wrong with this?

    Because if the proc is picked dynamically, the columns returned for each dataset will be different, and that means the front end code has to be MASSIVELY more complex to deal with querying the metadata of the proc before running the proc.

    It's better than a single dynamic query for the same thing, but still has huge hurdles the dev doesn't realize he's creating for himself. Ignorance is bliss, till the thing you don't know is which side of the road to drive on, and whether there's an 18-wheeler ahead of you going the opposite way. That's pretty much where the original request is at. (Not the person who wrote the post, the dev who asked for the proc in the first place.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Okay, I see where you're coming from. Yes, it would be a bit of front-end code pain to deal with that.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Yeah, I would tell the developer, "No. Do it right."

  • I understood the requirement differently.

    If the routine would return, say, name and datatype for all columns, then that would be enough to pass

    to generic table editors, simple display pages, and spreadsheet output. That seems pretty reasonable.

    So I would write something like the following.

    (User can omit fields that are not of interest to requestor.)

    create proc [dbo].[dbi_tbl_getCols2]( @db varchar(31) = 'yourDB', @tbl varchar(31) = 'yourTable')

    as

    begin

    set nocount on

    declare @srvr varchar(15)

    set @srvr = @@servername

    declare @d1 varchar(31)

    set @d1 = case when @db='' then '' else @db + '.' end

    declare @x1 varchar(1023)

    declare @x2 varchar(1023)

    declare @xcmd varchar(2048)

    set @x1 =

    'select t.name tblName, c.column_name colName

    , c.ordinal_position seq, c.data_type

    , isnull(c.character_maximum_length,0) maxLen

    , isNull(c.numeric_precision,0) num_prec

    , isNull(c.numeric_scale,0) num_scale, ac.is_identity, ac.is_computed

    '

    set @x2 =

    'from '+@d1+'sys.tables t

    inner join '+@d1+'INFORMATION_SCHEMA.COLUMNS c on c.table_name = t.name

    inner join '+@d1+'sys.all_columns ac

    on ac.name = c.column_name and t.object_id = ac.object_id

    where t.name = ''' + @tbl + ''' order by seq '

    set @xcmd = @x1 + @x2

    exec (@xcmd)

    end

  • Thomas Considine (6/2/2011)


    I understood the requirement differently.

    If the routine would return, say, name and datatype for all columns, then that would be enough to pass

    to generic table editors, simple display pages, and spreadsheet output. That seems pretty reasonable.

    So I would write something like the following.

    (User can omit fields that are not of interest to requestor.)

    create proc [dbo].[dbi_tbl_getCols2]( @db varchar(31) = 'yourDB', @tbl varchar(31) = 'yourTable')

    as

    begin

    set nocount on

    declare @srvr varchar(15)

    set @srvr = @@servername

    declare @d1 varchar(31)

    set @d1 = case when @db='' then '' else @db + '.' end

    declare @x1 varchar(1023)

    declare @x2 varchar(1023)

    declare @xcmd varchar(2048)

    set @x1 =

    'select t.name tblName, c.column_name colName

    , c.ordinal_position seq, c.data_type

    , isnull(c.character_maximum_length,0) maxLen

    , isNull(c.numeric_precision,0) num_prec

    , isNull(c.numeric_scale,0) num_scale, ac.is_identity, ac.is_computed

    '

    set @x2 =

    'from '+@d1+'sys.tables t

    inner join '+@d1+'INFORMATION_SCHEMA.COLUMNS c on c.table_name = t.name

    inner join '+@d1+'sys.all_columns ac

    on ac.name = c.column_name and t.object_id = ac.object_id

    where t.name = ''' + @tbl + ''' order by seq '

    set @xcmd = @x1 + @x2

    exec (@xcmd)

    end

    Going from this:

    then i have to pass all the column values to application side related to table ABC...so dynamically i have to generate the select code with the table name inside the procedure

    It mentions "column values", and "dynamically generate the select code".

    That means, to me, actually run the select and return the "column values" in the dataset.

    That particular situation is something I've seen more devs ask for than I care to remember. "How come we can't have one proc that selects everything for us?" is a pretty common request. It's always one of those things that seems brilliant until you actually try to do it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 15 total)

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