Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
382
questions
0
votes
0answers
42 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 ...
-5
votes
0answers
44 views
How to write dynamic query with pagination in SQL [closed]
Query -
SET NOCOUNT ON;
WITH cte AS
(
SELECT *, COUNT(*) OVER() as TotalCount FROM tblOwnerDetails
where PhoneNo1 is null and PhoneNo2 is null
ORDER BY OwnerId
OFFSET @...
2
votes
1answer
267 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?
0
votes
1answer
42 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
1answer
76 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'),
...
...
0
votes
1answer
68 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
...
1
vote
0answers
52 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 ...
0
votes
1answer
166 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,
...
1
vote
2answers
26 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 ...
1
vote
1answer
37 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 ...
1
vote
1answer
45 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 = '...
0
votes
2answers
70 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
1answer
87 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 ...
1
vote
1answer
57 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 ...
0
votes
0answers
39 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 ...
0
votes
1answer
57 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 ...
0
votes
0answers
82 views
Convert a numeric number to a letter PostgreSQL
i have this function : https://dbfiddle.uk/?rdbms=postgres_12&fiddle=22a67dedd54291d7ffb527e2f9d482ca
this function retrieve only integer part, i want to retrieve also decimal part.
i tried : ...
0
votes
0answers
13 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
1answer
29 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
1answer
91 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
1answer
113 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 ...
0
votes
1answer
12 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
1answer
36 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&...
0
votes
1answer
345 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(...
0
votes
0answers
39 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 ...
1
vote
2answers
63 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. ...
0
votes
1answer
362 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 ...
1
vote
2answers
29 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 ...
0
votes
2answers
271 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 ...
-1
votes
1answer
231 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 &...
1
vote
1answer
45 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
...
0
votes
1answer
32 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 ' + @...
1
vote
1answer
159 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 ...
0
votes
1answer
632 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
1answer
212 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 ...
1
vote
1answer
477 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 ...
2
votes
2answers
952 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 ...
0
votes
2answers
42 views
How to mention declared integer in where condition using dynamic sql?
I tried with 1,2 and 3 quotes and with/without conversion, but I am getting
Error: Conversion failed when converting the varchar value
Can someone please help me?
with conversion
left ...
0
votes
1answer
131 views
How to create dynamic columns using prepared statements in MySQL?
I have 4 tables related to each other.
Table location:
CREATE TABLE `location` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
...
1
vote
2answers
124 views
How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL
I have three tables with these structures:
1) z_test_a(c_num number , <other columns>)
2) z_test_b(c_num number , Amount number , <other columns>)
3) z_test_b(c_num number , Amount ...
-1
votes
1answer
245 views
Single query to replace a value in multiple tables with same column
I am using Postgres SQL. I am having multiple tables say table1, table2, table3, table4, ... with a common column say name in each of the tables. I want to replace a the value 'Jhon' with 'Jimmy' in ...
1
vote
2answers
336 views
How to use 'Bind Variables' in a 'Dynamic Query ' when the exact number of variables are not known
I have a procedure in which I'm using Dynamic SQL(variable actual_query) and I'm generating this query based on the input parameter i_tables which is a concatenation of the name of some tables. It has ...
0
votes
1answer
61 views
How to display the exact query in the output section of a stored procedure when using 'Dynamic SQL' and 'Bind variables' in Oracle
I have a Procedure as you can see below. In this procedure I have a dynamic querywhich is qry variable and I'm using Bind variable in order to eliminate the risk of injection and improve performance:
...
0
votes
1answer
76 views
How to prevent a highly dynamic PL-SQL query from injection using bind variables
I have a procedure as you can see below:
create or replace procedure Injection_test(qry1 varchar2,
qry2 varchar2,
...
0
votes
1answer
72 views
How do I loop through every table and alter the PK column like so? (Postgres)
Using psql in Postgres 12, I need to alter every column in every table of my database that's:
int/bigint
not null
primary key
to:
identity column
auto-increment
with default value starting at ...
-1
votes
1answer
441 views
Dynamic sql query with top count sqlserver [closed]
My Requirement
I need to set\get a variable's value using the dynamic query
I need to write a dynamic sql query to select top (@topCount)
The @topCount will need to be a calculated value using the ...
0
votes
1answer
81 views
Dynamic SQL breaks statement
I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the ...
0
votes
2answers
2k views
how to resolve ORA-04042: procedure, function, package, or package body does not exist and ORA-00942: table or view does not exist
I am trying to grant EXECUTE and READ privilege on two tables to a user ktest1.
grant EXECUTE on SYS.KIR_DOKUMENT to ktest2;
grant READ on SYS.KIR_DOKUMENT to ktest2;
when i give grant execute i get ...
0
votes
1answer
76 views
Limiting the occurence of a value in PostgreSQL
Using PostgreSQL 11.7 and I have to model some real-world constraints. Effectively, I'm modeling a graph and an edge, of course, has two nodes. Each node can have zero, one, or two edges. Thus, I need ...
4
votes
1answer
93 views
sp_executesql: what can be parameterized
Is there a definitive list published showing what can and cannot be parameterized in a call to sp_executesql.
For example, predicates can, table names cannot. Number of rows in TOP can:
exec ...
