Stack Overflow
Stack Overflow

Questions tagged [dynamic-sql]

Dynamic SQL is a technique using SQL (Structured Query Language) whose main difference from traditional SQL is that Dynamic SQL allows to build SQL statements dynamically at runtime, which eases the automatic generation and execution of program statements.

2681 questions
51
votes
9 answers

Dynamic SQL results into temp table in SQL Stored procedure

The code is as follows: ALTER PROCEDURE dbo.pdpd_DynamicCall @SQLString varchar(4096) = null AS Begin create TABLE #T1 ( column_1 varchar(10) , column_2 varchar(100) ) insert into #T1 execute ('execute ' + @SQLString ) …
Dhanapal
  • 14,239
  • 35
  • 115
  • 142
42
votes
4 answers

Refactor a PL/pgSQL function to return the output of various SELECT queries

I wrote a function that outputs a PostgreSQL SELECT query well formed in text form. Now I don't want to output a text anymore, but actually run the generated SELECT statement against the database and return the result - just like the query itself…
waldyr.ar
  • 14,424
  • 6
  • 33
  • 64
41
votes
5 answers

PostgreSQL convert columns to rows? Transpose?

I have a PostgreSQL function (or table) which gives me the following output: Sl.no username Designation salary etc.. 1 A XYZ 10000 ... 2 B RTS 50000 ... 3 C …
DonRaHulk
  • 575
  • 1
  • 6
  • 18
41
votes
5 answers

dynamic sql query in postgres

I was attempting to use Dynamic SQL to run some queries in postgres. Example: EXECUTE format('SELECT * from result_%s_table', quote_ident((select id from ids where condition = some_condition))) I have to query a table, which is of the form…
psteelk
  • 1,305
  • 3
  • 16
  • 24
40
votes
9 answers

What is dynamic SQL?

I just asked an SQL related question, and the first answer was: "This is a situation where dynamic SQL is the way to go." As I had never heard of dynamic SQL before, I immediately searched this site and the web for what it was. Wikipedia has no…
augustin
  • 14,373
  • 13
  • 66
  • 79
36
votes
2 answers

INSERT with dynamic table name in trigger function

I'm not sure how to achieve something like the following: CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$ DECLARE shadowname varchar := TG_TABLE_NAME || 'shadow'; BEGIN INSERT INTO shadowname…
sschober
  • 2,003
  • 3
  • 24
  • 38
35
votes
1 answer

PostgreSQL - dynamic value as table name

Possible Duplicate: Postgres Dynamic Query Function I wish to use the returned string from the query below as a table name for other query. SELECT 'backup_' || TO_CHAR(CURRENT_DATE,'yyyy-mm-dd') as you can see it returns a string. I wish to use…
Mr.
  • 9,429
  • 13
  • 58
  • 82
33
votes
10 answers

sql use statement with variable

I'm trying to switch the current database with a SQL statement. I have tried the following, but all attempts failed: USE @DatabaseName EXEC sp_sqlexec @Sql -- where @Sql = 'USE [' + @DatabaseName + ']' To add a little more detail. EDIT: I would…
Drejc
  • 14,196
  • 16
  • 71
  • 106
27
votes
5 answers

T-SQL Dynamic SQL and Temp Tables

It looks like #temptables created using dynamic SQL via the EXECUTE string method have a different scope and can't be referenced by "fixed" SQLs in the same stored procedure. However, I can reference a temp table created by a dynamic SQL statement…
Chad
  • 23,658
  • 51
  • 191
  • 321
25
votes
2 answers

Oracle: DBMS_UTILITY.EXEC_DDL_STATEMENT vs EXECUTE IMMEDIATE

Which are the differences between DBMS_UTILITY.EXEC_DDL_STATEMENT and EXECUTE IMMEDIATE?
Revious
  • 7,816
  • 31
  • 98
  • 147
25
votes
6 answers

Generate Delete Statement From Foreign Key Relationships in SQL 2008?

Is it possible via script/tool to generate a delete statement based on the tables fk relations. i.e. I have the table: DelMe(ID) and there are 30 tables with fk references to its ID that I need to delete first, is there some tool/script that I can…
Element
  • 3,981
  • 7
  • 42
  • 51
25
votes
7 answers

How to join to tables whose names are stored as values in another table?

Ι have some tables ( eg. [Table1], [Table2], [Table3] and so on ) with a [ID] as primary key and a RecTime as DATETIME on each. Αlso Ι have a table [Files] that hold files in a varbinary(max) column, and refers to the other tables having their Names…
armen
  • 1,253
  • 3
  • 23
  • 41
25
votes
3 answers

drop all tables sharing the same prefix in postgres

I would like to delete all tables sharing the same prefix ('supenh_agk') from the same database, using one sql command/query.
Roy
  • 723
  • 2
  • 8
  • 21
25
votes
4 answers

Dynamically generate columns for crosstab in PostgreSQL

I am trying to create crosstab queries in PostgreSQL such that it automatically generates the crosstab columns instead of hardcoding it. I have written a function that dynamically generates the column list that I need for my crosstab query. The idea…
invinc4u
  • 1,125
  • 3
  • 15
  • 26
23
votes
1 answer

Define table and column names as arguments in a plpgsql function?

It must be simple, but I'm making my first steps into Postgres functions and I can't find anything that works... I'd like to create a function that will modify a table and / or column and I can't find the right way of specifying my tables and…
fgcarto
  • 335
  • 1
  • 2
  • 8
1
2
3
…
99 100