Professional Web Applications Themes

Variable table select in function - Microsoft SQL / MS SQL Server

I am trying to create a generic function that concatenates results of a query into a string. I am able to create the function but i cannot determine how to define a variable table in the select. Does anyone know what i need to do to use a variable table name? thanks Murray My code is as follows: CREATE FUNCTION udf_concatenated_code (tablename varchar(50),code varchar(50), where varchar(1000)) RETURNS varchar(50) AS BEGIN DECLARE strCon varchar(50) SET strCon = '' SELECT strCon = strCon + ',' + lith_codecode FROM (SELECT DISTINCTcode FROM tablename WHERE where ) s SET strCon = RIGHT(strCon,LEN(strCon) - 1) ...

  1. #1

    Default Variable table select in function

    I am trying to create a generic function that concatenates results of a
    query into a string. I am able to create the function but i cannot
    determine how to define a variable table in the select.

    Does anyone know what i need to do to use a variable table name?

    thanks

    Murray

    My code is as follows:
    CREATE FUNCTION udf_concatenated_code (tablename varchar(50),code
    varchar(50), where varchar(1000))
    RETURNS varchar(50)
    AS
    BEGIN
    DECLARE strCon varchar(50)
    SET strCon = ''

    SELECT strCon = strCon + ',' + lith_codecode
    FROM (SELECT DISTINCTcode FROM tablename WHERE where ) s

    SET strCon = RIGHT(strCon,LEN(strCon) - 1)


    END

    Murray Guest

  2. #2

    Default Re: Variable table select in function

    This is a bit tricky with EXECUTE because dynamic sql is a new batch so
    variables will not be avaiable to your function but inside dynamic code
    only. You need something else to cary your data from one scope to another
    like temorary table or use sp_executesql as you will see later in post. When
    you issue

    declare x int
    execute('set x = 1')

    you get and error because x cannot be used in another batch, on the other
    hand when you do

    execute('declare x int set x = 1')
    print x

    you also get an error. Slimpliest approach I can think of now is to use temp
    table like

    create table #x (vx int)
    declare x int
    execute('declare x int set x = 1 insert into #x values (x)')
    select x = vx from #x
    print x

    but keep in mind that this will slow you so this is not a good solution.
    Your solution is to use sp_executesql because of it's ability to use
    parameters (input and output). Evaluate following statement

    declare x int
    exec sp_executesql N'set px = 1', N'px int output', x output
    print x

    To add a table name you need to change query like

    declare query nvarchar(1024)
    set query = 'select .....' + tablename
    sp_executesql query....

    Regards
    KR

    "Murray Bryant" <murray.bryantharmonygolddotcomdotau> wrote in message
    news:phx.gbl...
     


    Kresimir Guest

  3. #3

    Default Re: Variable table select in function

    >> a generic function that concatenates results of a
    query into a string ...<<

    In a client server architcture, the display is done by the front end,
    not in the database side. SQL is a strongly typed language and they
    don't like generic functions. The idea is to know what you are doing
    *before* run time, instead of letting the users, present and any future
    ones make up functioanlity on the fly.
     [/ref]
    a variable table in the select. <<

    The short answer is use slow, proprietrary dynamic SQL to kludge a query
    together on the fly with your table name in the FROM clause.

    The right answer is never pass a table name as a parameter.

    1) This is dangerous because some user can insert pretty much whatever
    they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
    FROM Floob' in your statement string.

    2) It says that you have no idea what you are doing, so you are giving
    control of the application to any user, present or future. Remember the
    basics of Software Engineering? Modules need weak coupling and strong
    cohesion, etc.

    3) If you have tables with the same structure which represent the same
    kind of entities, then your schema is not orthogonal. Look up what
    Chris Date has to say about this design flaw.

    Go back to basics. What is a table? A model of a set of entities or
    relationships. EACH TABLE SHOULD BE A DIFFERENT KIND OF ENTITY.

    What a generic procedure that works equally on automobiles, octopi or
    Britney Spear's discology is saying that your applications a disaster of
    design because you have:

    1) failed to put all items of the same kind into one table. Chris date
    calls this orthogonal design, and I call it attribute splitting. Common
    example, 12 identical tables, one for each month, with the same
    information them instead of a single table with a temporal attribute.

    2) failed to tell the difference between data and meta-data. The SQL
    engine has routines for that stuff and applications do not work at that
    level, if you want to have any data integrity.

    Yes, you can write a program with dynamic SQL to kludge something like
    this. It will last about a year in production and then your data
    integrity is shot. Please stop writing code like this before you
    destroy your database.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: Variable table select in function

    Hi Murray,

    You can't use dynamic SQL inside a function so you might need to look at
    stored procedures instead.

    --
    Tony Rogerson
    SQL Server MVP
    http://www.sqlserverfaq.com?mbr=21
    (Create your own groups, Forum, FAQ's and a ton more)


    Tony Guest

  5. #5

    Default Re: Variable table select in function

    Oops, I didn't notice it's a UDF, sorry about that. I agrre with Tony, you
    should use stored procedure instead.

    Regards
    Kresimir Radosevic

    "Tony Rogerson" <eu.com> wrote in message
    news:%phx.gbl... 


    Kresimir Guest

Similar Threads

  1. Replies: 0
    Last Post: October 10th, 07:37 PM
  2. how to use a variable into a select?
    By Mich in forum ASP Database
    Replies: 11
    Last Post: November 8th, 03:15 PM
  3. Replies: 2
    Last Post: September 29th, 07:11 PM
  4. Is there any way to loop a temp table or table variable without cursor?
    By Lawrence in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: August 9th, 08:25 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139