Professional Web Applications Themes

Dynamic SQL - Microsoft SQL / MS SQL Server

[url]http://www.algonet.se/~sommar/dynamic_sql.html[/url] [url]http://www.algonet.se/~sommar/arrays-in-sql.html[/url] "Dale Fye" <spam.saic.comnomore.dale.fye> wrote in message news:eXNisx0PDHA.3192TK2MSFTNGP10.phx.gbl... > I'm an Access programmer, migrating to SQL SERVER 2K. > > I want to insert a bunch of records into a table using a stored > procedure. One of my coworkers already has some code to do this, but > he is running a client side cursor (ASP) to loop thru the list of > USERIDs we want to add, causing increased network traffic. I'd like > to do it with a single call to the server. If I can get this working > I will be passing two values ...

  1. #1

    Default Re: Dynamic SQL

    [url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]
    [url]http://www.algonet.se/~sommar/arrays-in-sql.html[/url]



    "Dale Fye" <spam.saic.comnomore.dale.fye> wrote in message
    news:eXNisx0PDHA.3192TK2MSFTNGP10.phx.gbl...
    > I'm an Access programmer, migrating to SQL SERVER 2K.
    >
    > I want to insert a bunch of records into a table using a stored
    > procedure. One of my coworkers already has some code to do this, but
    > he is running a client side cursor (ASP) to loop thru the list of
    > USERIDs we want to add, causing increased network traffic. I'd like
    > to do it with a single call to the server. If I can get this working
    > I will be passing two values to the SP, a ProfileID (int), and a
    > string which contains USERIDs (alphanumeric) values that might look
    > like ('aaaa', 'bbbb', 'ccccc'). What I want to do is something like:
    >
    > INSERT INTO myTable (ProfileID, USERID)
    > SELECT ProfileID, U.UserID
    > FROM Users U
    > WHERE U.UserID IN ( & Users & )
    >
    > I know this won't work, but would like to know the most efficient way
    > to accomplish my goal.
    >
    > Thanks in advance!
    > --
    > HTH
    >
    > Dale Fye
    >
    >
    >

    Aaron Bertrand - MVP Guest

  2. #2

    Default Re: Dynamic SQL

    Thanks, Aaron. Great reference.

    --
    HTH

    Dale Fye


    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:ebUkl10PDHA.560TK2MSFTNGP10.phx.gbl...
    [url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]
    [url]http://www.algonet.se/~sommar/arrays-in-sql.html[/url]



    "Dale Fye" <spam.saic.comnomore.dale.fye> wrote in message
    news:eXNisx0PDHA.3192TK2MSFTNGP10.phx.gbl...
    > I'm an Access programmer, migrating to SQL SERVER 2K.
    >
    > I want to insert a bunch of records into a table using a stored
    > procedure. One of my coworkers already has some code to do this,
    but
    > he is running a client side cursor (ASP) to loop thru the list of
    > USERIDs we want to add, causing increased network traffic. I'd like
    > to do it with a single call to the server. If I can get this
    working
    > I will be passing two values to the SP, a ProfileID (int), and a
    > string which contains USERIDs (alphanumeric) values that might look
    > like ('aaaa', 'bbbb', 'ccccc'). What I want to do is something
    like:
    >
    > INSERT INTO myTable (ProfileID, USERID)
    > SELECT ProfileID, U.UserID
    > FROM Users U
    > WHERE U.UserID IN ( & Users & )
    >
    > I know this won't work, but would like to know the most efficient
    way
    > to accomplish my goal.
    >
    > Thanks in advance!
    > --
    > HTH
    >
    > Dale Fye
    >
    >
    >


    Dale Fye Guest

  3. #3

    Default Dynamic SQL

    I need a white paper or statistics that address the
    performance differences of applications that use static
    SQL vs. applications that use dynamic SQL. I searched
    Microsoft.com several different ways but came up empty.
    Any ideas?
    Bill Guest

  4. #4

    Default Dynamic SQL

    Hi



    When looking at PostGres through the eyes of an Oracle Developer I was
    wondering if PostGres supports a feature called Dynamic SQL. Of course even
    better would be the ability to run PostGress/PL dynamically too.



    Dynamic SQL and Dynamic PL/SQL are useful when you don't know until runtime
    how your SQL or PL/SQL looks like.



    Any tips appreciated.



    /Mark




    Mark Battersby Guest

  5. #5

    Default Re: Dynamic SQL

    Yes, it does. And it's a lot easier than DBMS_SQL too!

    Look at the EXECUTE command in the pl/pgsql programming language.

    See [url]http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html[/url]

    John Sidney-Woollett

    Mark Battersby wrote:
    > Hi
    >
    >
    >
    > When looking at PostGres through the eyes of an Oracle Developer I was
    > wondering if PostGres supports a feature called Dynamic SQL. Of course even
    > better would be the ability to run PostGress/PL dynamically too.
    >
    >
    >
    > Dynamic SQL and Dynamic PL/SQL are useful when you don't know until runtime
    > how your SQL or PL/SQL looks like.
    >
    >
    >
    > Any tips appreciated.
    >
    >
    >
    > /Mark
    >
    >
    >
    >
    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faq[/url]

    John Sidney-Woollett Guest

  6. #6

    Default Re: Dynamic SQL

    Mark Battersby wrote:
    > When looking at PostGres through the eyes of an Oracle Developer I was
    > wondering if PostGres supports a feature called Dynamic SQL. Of course
    > even better would be the ability to run PostGress/PL dynamically too.
    >
    > Dynamic SQL and Dynamic PL/SQL are useful when you donít know until
    > runtime how your SQL or PL/SQL looks like.
    Does this help?

    [url]http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN[/url]
    [url]http://www.postgresql.org/docs/8.0/static/plpgsql-porting.html[/url]
    [url]http://www.postgresql.org/docs/8.0/static/ecpg-dynamic.html[/url]
    [url]http://www.postgresql.org/docs/8.0/static/libpq-exec.html[/url]

    Shridhar


    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Shridhar Daithankar Guest

  7. #7

    Default Re: Dynamic SQL

    On Mon, 31 Jan 2005 13:18:38 +0100, Mark Battersby wrote:
    > When looking at PostGres through the eyes of an Oracle Developer I was
    > wondering if PostGres supports a feature called Dynamic SQL. Of course even
    > better would be the ability to run PostGress/PL dynamically too.
    PostgreSQL does not conform to the "Basic dynamic SQL" feature of the SQL
    standard (feature ID B031). However, PostgreSQL supports some of the
    involved concepts, like prepared statements. See
    [url]http://www.postgresql.org/docs/8.0/static/ecpg.html[/url] and in particular
    [url]http://www.postgresql.org/docs/8.0/static/ecpg-dynamic.html[/url]

    --
    Greetings from Troels Arvin, Copenhagen, Denmark



    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Troels Arvin Guest

Similar Threads

  1. dynamic drop downlists in an editable dynamic datagrid.
    By mkhans@gmail.com in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 22nd, 09:01 PM
  2. ANN: Learn to go dynamic with WebAssist Dynamic Site 101
    By Joseph Lowery in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 28th, 04:20 PM
  3. dynamic menu system going to dynamic page
    By Moragreid in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 4th, 08:19 PM
  4. DYNAMIC Dropdown. How to stick on non dynamic choice?
    By Lesley G in forum Dreamweaver AppDev
    Replies: 3
    Last Post: February 23rd, 05:49 PM
  5. Dynamic text box within dynamic movie clip
    By WebDon webforumsuser@macromedia.com in forum Adobe Flash, Flex & Director
    Replies: 3
    Last Post: January 27th, 08:47 AM

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