Questions tagged [dynamic-sql]

Constructing a query at runtime with string concatenation operations and executing the query from that string.

Filter by
Sorted by
Tagged with
4
votes
2answers
63k views

Dynamic pivot: sum of sales per month

My table is like this: Sitecode Month Amount -------- ----- ------ XX Jan 1000 XX Jan 3000 XX Apr 3000 XX Apr 1000 What I want is, to show ...
18
votes
4answers
5k views

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values. CREATE TEMP TABLE foo AS SELECT x::text AS name, x::int ...
22
votes
4answers
81k views

Is there a way to generate table create script in TSQL?

Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a ...
5
votes
2answers
689 views

The database mail configuration saved into a temp table

I am currently dealing with a bunch of servers and they have different Database Mail Configurations. I can see the Database Mail configuration for each of those servers. I use the following script: ...
7
votes
3answers
14k views

Duplicate row with Primary Key in PostgreSQL

Assume I have a table as follows named people, where id is a Primary Key: +-----------+---------+---------+ | id | fname | lname | | (integer) | (text) | (text) | +===========+=========+=...
4
votes
2answers
25k views

How to execute this procedure in PL/SQL?

I have this table in the below format: Persnbr | Userfieldcd | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want ...
6
votes
2answers
11k views

Add function parameter to SQL query WHERE clause

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function: CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS $$ ...
6
votes
3answers
3k views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...
5
votes
2answers
21k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
2
votes
3answers
4k views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
4
votes
1answer
2k views

CREATE SEQUENCE using expressions with psql variables for parameters

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...
1
vote
1answer
553 views

For each field name in a list of fields, get the unique values

The Question: I have a view that is a list of fields in tables: FIELD_LIST_VW +-------------+------------+ | TABLE_NAME | FIELD_NAME | +-------------+------------+ | ENG.TABLE_1 | FIELD_1 | | ...
0
votes
1answer
1k views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to z)....
6
votes
2answers
10k views

How to drop all of my functions in PostgreSQL?

Right now I have to use query to get the command in text file. Then remove double quote from them. And finally, run that file in psql shell. How can drop all of my functions in PostgreSQL in a single ...
4
votes
2answers
2k views

How to quote qualified table name with format() function?

I have some problems in properly quoting a table name using the format function in the example below. CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text) RETURNS bool AS $...
2
votes
1answer
2k views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...
2
votes
1answer
2k views

Command to add column to all tables in a schema

Could you please give the command to add a column to all the tables if the table doesn't contain that column in a schema? e.g. The schema schema1 contains 50 tables in which 20 tables contain a ...
0
votes
2answers
5k views

How to create a table dynamically based on json data?

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...
8
votes
4answers
5k views

Dynamically convert hstore keys into columns for an unknown set of keys

I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns. Users can ...
7
votes
1answer
5k views

Trigger function taking column names as parameters to modify the row

I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet): CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) RETURNS trigger AS $...
7
votes
2answers
7k views

Using INSERT INTO…EXEC AT linked server into temp table fails with Msg 7391

I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is insert into ##eapb EXEC (@remotecmd) AT [...
5
votes
1answer
8k views

Create a view that dynamically UNIONs new tables

I have a database named DDOS_WAF_REPORT that has multiple tables with the name format dbo.DDOS_WAF_ACCOUNT_###### (a 5 or 6 digit ID number). All the tables have the exact same structure (both column ...
10
votes
2answers
2k views

Can sp_executesql be configured / used by default?

I am looking at an application that uses highly dynamic sql queries against SQL Server. Looking at the queries that are constructed in very weird and complicated ways, but that's a different story, I ...
0
votes
1answer
6k views

Beginner PL/SQL: Return row value from dynamic SQL function

I have a PL/SQL function that successfully compiles: 01 WITH FUNCTION find_CV_errors( 02 l_table_name IN VARCHAR 03 ,l_field_name IN VARCHAR 04 ,l_domain_name IN VARCHAR 05 ...
8
votes
3answers
42k views

Postgres plpgsql - Using a variable inside of a dynamic create statement

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as: ... DECLARE tblVar varchar := "myTable"; BEGIN EXECUTE 'CREATE TABLE $1 ( foo ...
8
votes
2answers
15k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
7
votes
1answer
11k views

Are there other ways to select a dynamic list of columns?

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that. 1. Using refcursors CREATE OR REPLACE FUNCTION selecttestwithcolumnlist( ticker ...
6
votes
1answer
24k views

How to use function parameters in dynamic SQL with EXECUTE?

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
6
votes
2answers
5k views

triggers - using the inserted / deleted tables in Dynamic SQL

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables. Declare @NewID varchar(50) = Replace(...
5
votes
4answers
7k views

Does it make sense to have CASE .. END in an ORDER BY?

Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice? It is common to use parameters ...
3
votes
1answer
2k views

Making dynamic queries for linked server in SQL Server

I want to create a dynamic query in SQL Server which will run on linked server. I am trying to do it as follows. USE [MYDB] GO DECLARE @company AS nvarchar(50); DECLARE @id nvarchar(MAX); DECLARE @...
3
votes
2answers
687 views

Bug in PL/pgSQL function creation

I don't know if this question better suits here or in SO ... This is a script that I'd like to launch (the code of the function was copied from a question on SO): \c mydb create or replace function ...
2
votes
1answer
2k views

Comprehensive troubleshooting of DatabaseMail

I am having a problem sending email from one of my servers. I want to use DatabaseMail and/or msdb.dbo.sp_send_dbmail I have other production servers that use all the same databaseMail settings ...
1
vote
1answer
2k views

Bind variable vs. string-concatenated variable

I've made an anonymous block that has dynamic SQL: 01 DECLARE 02 l_table_name VARCHAR2(100); 03 l_field_name VARCHAR2(100); 04 l_domain_name VARCHAR2(100); 05 06 BEGIN ...
0
votes
2answers
135 views

Parameterized Dynamic TSQL - Dynamic Parameters

We have a situation where effectively the same report is requested by different customers but they: Don't want all the columns Want the columns in a different order than we naturally have them Want ...
12
votes
2answers
13k views

How to Dynamically change the database using TSQL

I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL: EXEC sys.sp_executesql N'USE db1 ' ; It executes successfully however the database ...
10
votes
1answer
32k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
10
votes
2answers
81k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...
7
votes
2answers
1k views

Why does this cursor produce results in the incorrect order?

I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes. The line ORDER BY 1,2,3 DESC; in the SQL ...
5
votes
1answer
2k views

Passing ROWTYPE parameter to EXECUTE

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the ...
5
votes
1answer
5k views

Assignment of a column with dynamic column name

I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following: CREATE OR REPLACE FUNCTION ...
4
votes
3answers
216 views

SELECT rows based on indefinite number of filters

How do I create a function which takes indefinite parameters? And then finds all game_ids in a table where each parameter matches a different row (but same game_id)? Example Table: create table tags ( ...
4
votes
2answers
1k views

Find changed columns for near-identical rows across tables

I run PostgreSQL 9.3.5 on Windows 7, 64-bit. My data arrives quarterly, in multiple tables (table1, ..., tableN) that are linked, intra-period, by cross-table constraints based on key identifiers. ...
3
votes
1answer
163 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
3
votes
1answer
502 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
3
votes
1answer
1k views

Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

I have this table that is created every month. ex. model201707 ('model' = fixed; '201707' = year & month based on the current date): | COLUMN_NAME | TYPE | |---------------|-------------|...
2
votes
1answer
14k views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
2
votes
0answers
207 views

Plan Guide for the LINQ-SQL Query

I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...
2
votes
1answer
2k views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
2
votes
0answers
70 views

RAISE NOTICE doesn't work as expected while using within Dynamic SQL block

I have a small piece of code to get all sequences and the current and maximum value of all sequences in a database. I have tried with recursive and some other alternatives through plain sqls, however ...