Dynamic SQL

[Aaron Bertrand - MVP]

[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.com@nomore.dale.fye> wrote in message
news:eXNisx0PDHA.3192@TK2MSFTNGP10.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]

Thanks, Aaron. Great reference.

--
HTH

Dale Fye


"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:ebUkl10PDHA.560@TK2MSFTNGP10.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.com@nomore.dale.fye> wrote in message
news:eXNisx0PDHA.3192@TK2MSFTNGP10.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
>
>
>

[Mark Battersby]

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

[John Sidney-Woollett]

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]

[Shridhar Daithankar]

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

[Troels Arvin]

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]majordomo@postgresql.org[/email])