Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
359
questions
1
vote
2answers
47 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
32 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
23 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
44 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
37 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
39 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
27 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
33 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
84 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
49 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
91 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 ...
0
votes
2answers
38 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
40 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
54 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
28 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 ...
0
votes
2answers
126 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
42 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
51 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
35 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
112 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
37 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
914 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
0answers
7 views
Join a specific table depending on the provided values in a table
I have Maps (as in a game map) which each can have one or more drops, where those can vary between being currencies, items among other stuff that is separated in multiple, different tables because the ...
0
votes
1answer
60 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 ...
0
votes
0answers
20 views
Save multiple dynamic filter for aggregation in Postgresql
What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data?
Lets assume I have a table called t_filter which ...
4
votes
1answer
54 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 ...
-3
votes
2answers
207 views
Why does IO Statistics show a lot of LOB Logical Reads on the Worktable when none of the tables in my query use any LOB datatype fields?
I have a procedure that runs a query dynamically across a few hundred databases that all have the same exact schema and aggregates the results to a temp table.
The query only involves 3 tables (each ...
0
votes
1answer
31 views
Generate GRANT for all tables reference by a procedure
I have a couple of stored procedures in my DB in which each one select from a couple of tables. I need to construct a statement where I can give it a stored procedure and get the grant select ...
0
votes
1answer
31 views
Change database and execute update query (T- Transact)
Could anyone face with issue like me?
I need to update some rows in table, but I don't know exact name of database. I am able to figure out name with:
Select name
from sys.databases
where name ...
0
votes
1answer
52 views
Get availability group and listener of the current DB (dynamic SQL)
I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate ...
0
votes
0answers
37 views
Pass scale value for NUMERIC datatype dynamically
I want to pass scale value for NUMERIC datatype dynamically:
DO
$$
DECLARE
v_roundfact INTEGER;
BEGIN
v_roundfact := 3;
CREATE TEMP TABLE TempTable ON COMMIT DROP AS
...
-3
votes
1answer
81 views
Dynamic table names [closed]
I am trying to create dynamic table names for each month.
This is how I tried it:
declare @query varchar(max)
declare @i date
declare @tableid int
declare @tblname varchar(20)
...
1
vote
0answers
25 views
MySQL 5.7 - Calling procedure with dynamic name
I'm trying to write a MySQL (5.7) procedure that dynamically calls another procedure based on the one of its inputs.
The code I came up with so far is this (procedure_exists works for checking ...
-1
votes
1answer
1k views
Stored procedure in SQL Server that returns total count of columns in table and distinct count of values in each column [closed]
I need a stored procedure that takes any table name as a parameter and returns total count of rows and distinct count of values in each column of that particular table when executed.
Let's take the ...
0
votes
1answer
397 views
How to insert into temp table on Server A results from dynamic SQL open query stored proc call to remote server B via Remote Query showplan operator?
I have to call a stored procedure on server B from server A using open query via dynamic SQL, and insert the results into a temp table created on server A. I noticed in the execution plan, it's using ...
3
votes
0answers
39 views
Source Control Dynamics
What ways are there to do version control of an MS Dynamics database? I've found mentions of DACBAC, and obviously SSIS packages could just be dumped in a git repo.
I'm particularly keen on learning ...
0
votes
1answer
153 views
Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?
I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will ...
0
votes
1answer
35 views
SQL stored procedure to produce custom XML text
I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables.
With my current solution I have a stored procedure for each 30 tables where I do: ...
1
vote
1answer
36 views
Stored procedure to set value to a column must accept returned values from functions
I created a very simple table in Data Mart to store ETL diagnostic information:
CREATE TABLE [internal].[etl_status]
(
[property_name] VARCHAR(100) NOT NULL PRIMARY KEY,
[property_value] ...
-1
votes
2answers
133 views
Join to tables dynamically depending on data
have a logging table that contains references to other objects in other tables. I want to create a view to add some additional data to the logging table by looking back at the other objects.
For ...
0
votes
1answer
127 views
INSERT INTO… SELECT over a LinkedServer in smaller batches
I've written the following code to move data from one database table, to another (over a LinkedServer);
SET NOCOUNT ON
DECLARE @ApplicationURL varchar(50),
@LinkedServer nvarchar(50),
@...
0
votes
1answer
67 views
Where to put single quotes?
I am trying to execute the below statement which creates a database called service2019:
DECLARE @dbname VARCHAR(50);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
EXEC (
'CREATE ...
4
votes
1answer
428 views
If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?
If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp ...
1
vote
0answers
32 views
Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?
I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with ...
0
votes
1answer
368 views
Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?
If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored ...
2
votes
1answer
499 views
Variable to select database in SQL query
If I want use a variable in the FROM clause of a SQL query I need to use dynamic SQL. Which might be a pain if the query is long and complex.
There are few different ways of running a query from a ...
0
votes
0answers
13 views
Need to break up a large file into smaller files to send via sp_sendsqlmail
I have created a job to send a "report" to someone (actually two reports). My issue is that the query is returning some 150K+ rows of data (no lobs or xml or anything, so not large rows), but ...
2
votes
2answers
597 views
Postgresql return results of dynamic SQL
I'm using Postgresql, version 12.
My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that ...
2
votes
1answer
787 views
Update dynamic column names using keys from jsonb argument
I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, ...
0
votes
3answers
821 views
Create stored procedure with dynamic number of columns + column names
I am relatively new to postgresql (I have version 12), and am having some trouble creating my first stored procedure. The real trouble is that I need it to be dynamic in some way, and thus I am having ...
