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
0 votes
0 answers
19 views

Generate dynamic number of columns from CTE

A hospital database (SQL Server 2016) has a table of visits to the hospital. If patients transfer to a different specialty, a new visit is made (but from a research perspective it's considered one ...
  • 101
0 votes
1 answer
105 views

exec limited to 4000 characters?

trying to execute this script gives only the first select as a result declare @sp nvarchar(max) select @sp = concat(N'select 1 ', replicate('-', 5000), char(13) + char(10), N'select 2') exec (@sp) ...
1 vote
2 answers
102 views

stored procedure runs fine in all sessions but one

There are procedures that run fine manually but not in a job, or fails when run from an application, or not work in SSIS SQL task Mine works in all sessions but one. this is the code I am running - it ...
1 vote
1 answer
300 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
1 vote
0 answers
280 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
490 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 ...
0 votes
1 answer
61 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
19 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$, $...
0 votes
0 answers
141 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
700 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
80 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 ...
  • 1,074
8 votes
2 answers
1k 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, @...
3 votes
1 answer
115 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,'>='); ...
  • 405
0 votes
1 answer
217 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,755
0 votes
2 answers
840 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
282 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 "...
-1 votes
1 answer
132 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
79 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 ...
2 votes
1 answer
427 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?
  • 75
0 votes
1 answer
601 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 ...
1 vote
1 answer
783 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
1 answer
777 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
54 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
2k 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
2 votes
2 answers
118 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 ...
  • 133
1 vote
1 answer
291 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 ...
2 votes
1 answer
219 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
2 answers
200 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
604 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
696 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
0 answers
64 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
1 answer
2k 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
20 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
117 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
331 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
556 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
1 answer
131 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
42 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
1 answer
2k 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
170 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 ...
  • 127
1 vote
2 answers
401 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
1k 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 ...
  • 26.6k
1 vote
2 answers
40 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 ...
  • 25
0 votes
2 answers
886 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
1 answer
614 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
1 answer
62 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
1 answer
34 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
1 answer
546 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 ...
  • 67.6k
0 votes
1 answer
1k 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
475 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
2 3 4 5
8