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
1 vote
1 answer
57 views

Use variable as column name

I'm trying to create trigger in PostgreSQL 14: create or replace function add_stats_to_player() returns trigger as $add_stats_to_player$ declare equipment_id int := new....
user avatar
  • 11
0 votes
0 answers
41 views

Is it possible to create a synonym dynamically?

I want to create a synonym for a lot of databases. Here's the synonym script: create synonym blog.Posts for [ClientA].contents.Posts The point is that the ClientA should be dynamic for each database. ...
user avatar
  • 31
1 vote
0 answers
60 views

How to compare a subset of two tables using dynamically generated column names in PostgreSQL?

I have two tables, one a test table and one a production table, both with +200 columns and a couple thousand lines of code to create the table. I periodically make changes and am trying to automate QA....
user avatar
0 votes
1 answer
91 views

Dynamic CREATE TABLE AS with indexes

I'm in the process of migrating our main db from SQL Server to PostgreSQL (while learning it in the process). One of the things I need to move are a bunch of stored procedures, that generate tables ...
user avatar
0 votes
1 answer
34 views

Allow create table (dynamic name) and insert but nothing else

I'm trying to find a way to allow an application to create tables and insert data into them on a SQL Server 2019 while protecting from injection attacks in case the app credentials would leak. My ...
user avatar
0 votes
1 answer
17 views

Using tablenames from a config table for joins

SELECT $model_master_id$, $model_market_segment$, $country$, $ctry_cd$, $score_run_id$, $period_id$, $prediction_from_dt$, $prediction_to_dt$, $buy_acq$, $...
user avatar
0 votes
0 answers
74 views

Create a stored procedure that dynamically union tables having extension .csv

I have a database named Fire that has 41 tables with the name format dbo.[#######.csv]. All the tables have the different number of column name like table 1 column A,B and table 2 column A,B,C and ...
user avatar
0 votes
0 answers
66 views

Select a dynamic list of columns in Postgres

Just wanted to come back to this question as proposed solution (using a temp table) Are there other ways to select a dynamic list of columns? didn't exactly work for me. My scenario (using example ...
user avatar
  • 1
0 votes
1 answer
52 views

Immutable dynamic sql functions

Scenario #1 Basically, lets say we have a function that's using dynamic SQL but, also know for a fact that it will always return the same output for the same input and declare it immutable will it ...
user avatar
7 votes
2 answers
892 views

How does use of sp_executesql with parameters protect against SQL injection?

The following is a dynamic filtering solution that uses sp_executesql IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders; GO CREATE PROC dbo.GetOrders @orderid AS INT = NULL, @...
user avatar
3 votes
1 answer
48 views

Evaluate comparison operator from table column

Is it possible to expand a field value into a comparison operator? Something like this: create table math ( value1 int, value2 int, operator text ); insert into math values(1,2,'>='); ...
user avatar
  • 395
0 votes
1 answer
38 views

How return dynamic SQL value in procedure

I have a procedure that takes in parameters, and makes a dynamic statement based on those. The statement always returns a numeric value. How do I return that value, or pass it to the variable? For ...
user avatar
  • 1,735
0 votes
2 answers
292 views

MySQL: Is There a way to dynamically generate multiple date range based on its date

The data I got is id date cnt 1 '2021-11-26' 1 1 '2021-11-25' 1 1 '2021-11-24' 1 1 '2021-11-23' 1 The result I want is id from_date to_date cnt 1 '2021-11-23' '2021-11-24' 2 1 '2021-11-23' '...
user avatar
  • 111
1 vote
1 answer
105 views

How to parametrize DDL in a plpgsql procedure?

How do I parametrize DDL statements in a plpgsql procedure? Namely, I want to use the org_label param as the role and schema name but the code below takes it literally and I end up with the role "...
user avatar
-1 votes
1 answer
66 views

Change dynamic SQL to Static SQL with order by [closed]

We have a SP that has a Dynamic SQL and one of the parameters it gets is order by. when we use the dynamic we can do something like this if @order > 0 BEGIN SET @ORDERBYSTR = 'ORDER BY ' + cast(@...
user avatar
  • 119
0 votes
0 answers
49 views

Using a variable that may be null in a where statement

I select a value into a mysql variable. Eg... select @userId := userId from myTable where id = ?; The value of @userId can be null. Now I want to update the rows in ...
user avatar
2 votes
1 answer
288 views

My function returned a string. How to execute it?

My function fun() returned a long crosstab table generation string (varchar) that I can view with select fun(). How to execute it not inside a function but from a console?
user avatar
  • 65
0 votes
1 answer
384 views

oracle stored procedure errors: ORA-00904 invalid identifier, dynamic SQL

I am trying to find all tables that contain column called arg_column_name, look up value arg_column_value in that column of those tables, and return names of the tables and counts of found rows to the ...
user avatar
0 votes
1 answer
375 views

In SQL Server, is there a way to pivot on an unknown number columns WITHOUT using dynamic SQL?

Essentially if we have this: create table #temp ([key] nchar, [value] nvarchar(2)); insert #temp ([key], [value]) values ('a', '1'), ('b', '2'), ... ...
user avatar
0 votes
1 answer
383 views

oracle pl/sql block throwing ORA-00922: missing or invalid option

I have the following segment of oracle pl/sql block DECLARE rec all_tab_columns%ROWTYPE; v_tableName VARCHAR2(100); v_columnName VARCHAR2(100); v_schemaName VARCHAR2(100); BEGIN ...
user avatar
  • 13
1 vote
0 answers
53 views

What is the best way to have actual execution plan and IO stats as values to insert?

We have idea to create procedure which will test performance of query and insert specific information associated with executed query to table with test performance history ([dbo].[TestHistory]. We ...
user avatar
  • 45
0 votes
1 answer
1k views

How to insert a row from JSON in Postgres

I have 2 tables recording company market data. A table recording stock market symbols CREATE TABLE appl.symbols ( id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, symbol VARCHAR(10) NOT NULL, ...
user avatar
  • 125
1 vote
2 answers
32 views

Viewing the statement in EXECUTE with USING

Let's say with the below function, I need to debug what is executed inside the EXECUTE statement. (a long, dynamic statement with dynamic variables provided in USING in reality) CREATE OR REPLACE ...
user avatar
  • 123
1 vote
1 answer
127 views

MariaDB: Create dynamic PrepareStatement inside a Procedure

I am trying to make a stored procedure which returns me the records of a table that match filters that can be applied in layers. The procedure receives certain variables as parameters and I want to ...
user avatar
1 vote
1 answer
141 views

SQL Dynamic Query based on user input in PowerBI

I am pulling data from inSQL using the SQL Query below: SET QUOTED_IDENTIFIER OFF SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime, [X],[Y], [Z], [A] FROM WideHistory WHERE wwRetrievalMode = '...
user avatar
0 votes
2 answers
77 views

Turn SQL tables into Views - with Dynamic SQL

As the name would suggest, I need to turn a TON of sql tables into views, using dynamic SQL. Motive-wise, it's just a silly workaround to preserve a Legacy program. So far I'm creating a temptable, ...
user avatar
0 votes
1 answer
341 views

Sql: Count Null and Non-Null values for set of tables and fields dynamically

I would like to create dynamic sql query where for the set of tables and fields from information schema I will count null and non-null values of these fields. I have idea to use it something like this ...
user avatar
  • 11
1 vote
1 answer
255 views

Using parameters in stored procedure with plpgsql

I am trying to return the count of overlapping polygons. The problem is that it complains about my "$1" signs: Error is "SQL state: 42601", syntax error at "$1". Why ...
user avatar
0 votes
0 answers
52 views

Dynamic SQL and AO AG - SQL Server

I have 3 servers with an SQL Server instance (S1\PROD, S1\PROD, S3\PROD) in my cluster and I have a couple of DBs hosted on these with AG AO. I have a lot of DBs and they have 2 replicas (1 primary ...
user avatar
0 votes
1 answer
794 views

How can I dynamically create views in Postgres 10 using variables?

I know how to do this in MySQL and MSSQL but I'm struggling to do the same in Postgres 10.12. I have about 200 views to create and the only difference in each one of them is the schema location and ...
user avatar
  • 249
0 votes
0 answers
15 views

Call two function within main function PostgreSQL

i have two functions one with the english language and the other with the french language. I would like to create a third function which will be the main function that will return the first function ...
user avatar
0 votes
1 answer
60 views

Get another function to return timestamp in PostgreSQL [duplicate]

Can I have another function to return the last timestamp. I want to compare with this query to use the best query. My query: CREATE OR REPLACE FUNCTION f_last_update1(nomTable text , OUT updated ...
user avatar
0 votes
1 answer
236 views

Get latest timestamp from column in given table dynamically

I want to get last timestmap from a table with the column updated in PostgreSQL. And I want a function to do that. So I can pass the table name as parameter and the function returns the latest ...
user avatar
1 vote
1 answer
345 views

Does it make sense to use OPTION(RECOMPILE) in dynamic SQL?

I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries? There are a few points to keep in mind: There will be hundreds (maybe even thousands) of ...
user avatar
0 votes
1 answer
44 views

Problem calling Postgres function

I wrote this function: create or replace function field_summaries(gap interval , _tbl anyelement, _col text) returns table(hour text, average numeric, minimum numeric, ...
user avatar
1 vote
1 answer
40 views

Is it possible to "bind" sp_executesql to the calling procedure, as recorded in the Query Store?

Statements executed with sp_executesql appear to be generally "unbound" from the procedure in which they appear. By "bind", I simply mean "associated to the calling object&...
user avatar
0 votes
1 answer
973 views

Include single quote string in redshift dynamic SQL

I have function named Schema.func_date(date,varchar) I wanna do like this(I am using below statement inside Store proc) Execute 'Select *, ID as Main_Id, ' || 'schema.func_date(quote_literal(...
user avatar
  • 103
0 votes
0 answers
73 views

How to run query to convert undefined JSON fields into a row dynamically

Related to this answer I found a solution above to get undefined JSON files into rows. However, I need to run query twice so I modify the query to run it once. I tried to run the query returned from ...
user avatar
  • 117
1 vote
2 answers
185 views

Stop SQL job if it runs past a certain time

I have a SQL job which executes a stored procedure. The proc loops through a number of databases, then executes a series of dynamic SQL queries to archive/delete a bunch of tables in each database. ...
user avatar
  • 111
0 votes
1 answer
771 views

Is it possible to create Dynamic SQL statements purely in SQLite? [closed]

A lot of modern RDBMS have a feature called Dynamic SQL (MS SQL Server, for example) which allows you to build your SQL statements in strings and then execute that string of SQL. Does such a feature ...
user avatar
  • 24k
1 vote
2 answers
30 views

Inject aggregation function inside a procedure

Is it possible to inject the name of a function (AVG, MAX...) as a procedure parameter? CREATE PROCEDURE test(func TEXT) LANGUAGE PLPGSQL AS $$ BEGIN DROP TABLE IF EXISTS foo; CREATE TEMPORARY TABLE ...
user avatar
  • 15
0 votes
2 answers
636 views

Passing database name to input parameter from a stored procedure

I have some issue with this application. I know that there are a lot of topics about passing a Database name as input but still I couldn't figure out what is the issue my code. Hopefuly, you guys can ...
user avatar
-1 votes
1 answer
414 views

Write a dynamic query of nested subqueries [duplicate]

Let's take an everyday query with a list of nested queries so I can show you the problem, see how we'd have to add a subquery when a new Certification comes out: select DISTINCT e.display_name as &...
user avatar
1 vote
1 answer
58 views

A Query with a Dynamic Case clause or how to avoid Case When

Let's take an everyday query with a CASE clause so I can show you the problem, see how we'd have to change the query when a new Certification comes out: select count(*) as "certCount", CASE ...
user avatar
0 votes
1 answer
33 views

Dynamic execution query produces error when called from the procedure, but works when executed manually

create procedure createTable(@tableName varchar(25), @columnName varchar(25), @columnType varchar(25)) as begin declare @sqlQuery as varchar(MAX) set @sqlQuery = 'create table ' + @...
user avatar
1 vote
1 answer
375 views

Equivalent of varchar(max) in ASE

What is the equivalent of SQL Server's varchar(max) data type in ASE? By ASE I mean what used to be known as Sybase ASE, which is now owned by SAP. I'm attempting to execute a piece of dynamically ...
user avatar
  • 67k
0 votes
1 answer
952 views

Passing a variable in OPENJSON WITH statement in SQL Server

Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason ...
user avatar
1 vote
1 answer
369 views

SQLServer Query Store not saving failed queries run by SP_ExecuteSQL

We are on SQL Server 2016 Query Store is activated on the database in question. We have had some timeout problems reported on a nightly batchjob, and we have located the query in question. The query ...
user avatar
1 vote
1 answer
2k views

Passing parameters for dynamic SQL in a function

I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In ...
user avatar
  • 59
2 votes
2 answers
2k views

Count missing values in each column of the table in PostgreSQL

Question: Which column of fortune500 (a table) has the most missing values? To find out, I have checked each column individually mentioned below, which is very tedious. SELECT SUM(CASE WHEN ...
user avatar

1
2 3 4 5
8