Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
395
questions
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....
- 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.
...
- 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....
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 ...
- 35
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 ...
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$,
$...
- 31
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 ...
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 ...
- 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 ...
- 934
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,
@...
- 299
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,'>=');
...
- 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 ...
- 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'
'...
- 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 "...
- 124
-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(@...
- 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 ...
- 117
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?
- 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 ...
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'),
...
...
- 147
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
...
- 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 ...
- 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,
...
- 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 ...
- 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 ...
- 113
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 = '...
- 11
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, ...
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 ...
- 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 ...
- 11
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 ...
- 349
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 ...
- 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 ...
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 ...
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 ...
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 ...
- 13
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, ...
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&...
- 195
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(...
- 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 ...
- 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. ...
- 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 ...
- 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 ...
- 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 ...
- 11
-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 &...
- 169
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
...
- 169
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 ' + @...
- 3
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 ...
- 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 ...
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 ...
- 167
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 ...
- 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 ...
- 21