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
82 views

issue with dynamic query and complex logic problem

I am working with dynamic query SQL WITH WINFORM(C#) DROP TABLE IF EXISTS #TB,##tmp SELECT EVENT_ID, CASE WHEN ALL_FAMILY = 1 THEN 'ALL_FAMILY' WHEN FAMILY = 1 THEN 'FAMILY' ...
rahul's user avatar
  • 1
4 votes
1 answer
255 views

Catch Block Inside of a Trigger Does Not Get Hit When a SP With Dynamic SQL Errors Out - Causing the Larger Transaction to Fail

I am not a fan of triggers and dynamic sql, however, what I am working with requires both. CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE AS BEGIN ...
Ross Bush's user avatar
  • 505
3 votes
1 answer
72 views

Creating VIEW using EXECUTE ignores USING clause parameter?

I am trying to create a UDF that creates a VIEW dynamically using the USING clause feature. The problem is, EXECUTE will not accept the passed parameter into the CREATE VIEW command. CREATE OR replace ...
Zaki's user avatar
  • 33
0 votes
1 answer
58 views

Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH

I would like to create transaction which will : DISABLE change tracking IF exists. TRUNCATE partition for table. SWITCH PARTITION from different table to main table. ENABLE change tracking IF was ...
adam.g's user avatar
  • 335
0 votes
1 answer
37 views

Accessing NEW.* keys dynamically in trigger in PostgreSQL

How you can access the NEW.* RECORD keys dynamically? For example here I'm trying to trim spaces: CREATE OR REPLACE FUNCTION trim_spaces() RETURNS TRIGGER AS $$ DECLARE _a HSTORE = hstore(NEW); _k ...
raspi's user avatar
  • 131
0 votes
1 answer
35 views

using variable in dynamic query

set serveroutput on DECLARE statement VARCHAR2(4000); BALANCING_ID INTEGER; VAULT_TILL_ID INTEGER; balancingid INTEGER := 16437; BEGIN statement := 'SELECT EOD_BALANCING_ID, VAULT_TILL_ID ...
guradio's user avatar
  • 97
2 votes
0 answers
426 views

How to return a result of dynamic query where returned number of columns are also dynamic?

We have function that generate dynamic query which uses crosstab function resulting in result set with dynamic number of columns. We tried using RETURN QUERY with RETURNS SETOF RECORD for this, but ...
user158's user avatar
  • 121
2 votes
2 answers
133 views

Is there a function that adds together all numeric columns in a row?

Using Postgres 15, how to get a sum of columns in each row from a table ? -86750 | 1728 | 1500 | 61.69 | 415 | 130.18 | 303.99 | 288.57 | 93.44 | 78.06 | 0 | 561....
francois P's user avatar
0 votes
2 answers
349 views

Get the maximum column sizes for each NUMERIC columns in all tables in postgresql

I'm trying to calculate the maximum length of NUMERIC columns in a postgres db. There are a number of tables in the db, and most of those tables contain a number of numeric columns. I'm importing a ...
masroore's user avatar
  • 111
3 votes
0 answers
74 views

How to create a view that returns all child records in a single row without aggregation?

I am an electrical engineer developing a parts library database and explorer tool for internal use in my organization. If anyone is familiar with OrCAD schematic capture tools, I am essentially ...
jde0503's user avatar
  • 31
2 votes
2 answers
1k views

PostgreSQL: Insert dynamic columns and values from jsonb argument

This question relates to Update dynamic column names using keys from jsonb argument and my code is based on the answer provided there by https://dba.stackexchange.com/users/3684/erwin-brandstetter I'm ...
Russell Turner's user avatar
0 votes
1 answer
139 views

How to return DDL success in a plpgsql function?

I have hundreds tables in a Postgresql-11 DB, need to be droped. In order to simplified my work, I coded a function as following: CREATE OR REPLACE FUNCTION dropTable( IN tab_name information_schema....
Leon's user avatar
  • 337
-1 votes
2 answers
61 views

Dynamically Pruning a Table [closed]

I have a table which is undergoing regular schema changes. It has around 80 columns currently. Some columns are read only. To refresh our dev environment, we copy the prod DB, prune it and anonymize ...
Echilon's user avatar
  • 167
0 votes
1 answer
261 views

Run SSIS Package but the package path will be decided runtime

I have to import 16 Excel files into 16 tables through agent. The agent will process the data further. I have created a SSIS package for each . I don't want to create 16 jobs to do same steps. My idea ...
Lakshmi R's user avatar
2 votes
1 answer
2k views

PostgreSQL: How to use INOUT parameter of a Stored Procedure called in dynamic SQL?

I have a stored procedure with an INOUT parameter, which value is modified inside the SP. I need to call that SP, providing a variable as that parameter, and then use the modified variable. But the ...
Oleksii Cherkas's user avatar
0 votes
1 answer
381 views

MS SQL Server: Dynamical create procedure / function / view / trigger in other database (sp_executesql)

Problem: you want / need to use dynamic SQL to create procedure / function / view / trigger in other databases than the current you can't specify the database name in the statement, CREATE VIEW ...
Thomas Franz's user avatar
0 votes
0 answers
32 views

Renaming the return fields of a SQL query with string variables?

Suppose I have a table as follows: symbol date price volume GE 2017.01.03 31.82 2,300 GE 2017.01.03 31.69 3,500 GE 2017.01.04 31.92 3,700 GE 2017.01.04 31.8 2,100 GE 2017.01.04 ...
ajldfa's user avatar
  • 109
5 votes
2 answers
1k views

sp_executesql Not Working with Parameters

I am running into a strange problem with dynamic sql that I believe is some sort of formatting issue, and I've been tearing my hair out the last few hours over this. I would really appreciate any ...
Austin's user avatar
  • 178
0 votes
1 answer
211 views

Alter a function in Postgres 12 to allow for dates to be default or populated [duplicate]

Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days. CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
rdbmsNoob's user avatar
  • 439
0 votes
2 answers
2k views

Default values for function parameters

Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days. CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
rdbmsNoob's user avatar
  • 439
0 votes
0 answers
1k 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 ...
Edge's user avatar
  • 101
1 vote
1 answer
131 views

Filter a SELECT query with predicates passed as single hstore value

For an API I am building I am supposed to write a function with a single hstore argument containing colname => value pairs so that queries may be filtered with a WHERE clause including an ...
eslukas's user avatar
  • 111
1 vote
1 answer
1k 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) ...
aldo kern's user avatar
1 vote
2 answers
656 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 ...
Marcello Miorelli's user avatar
1 vote
1 answer
2k 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....
Sevas's user avatar
  • 11
1 vote
0 answers
843 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....
jan_hilversmith's user avatar
0 votes
1 answer
2k 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 ...
Topper81's user avatar
0 votes
1 answer
923 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 ...
Martin Riddar's user avatar
0 votes
1 answer
25 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$, $...
Reshmi Nair's user avatar
0 votes
0 answers
362 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 ...
Jamshed Salik's user avatar
0 votes
0 answers
2k 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 ...
Alex F's user avatar
  • 1
0 votes
1 answer
274 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 ...
Chessbrain's user avatar
  • 1,183
8 votes
3 answers
3k 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, @...
T. Webster's user avatar
4 votes
1 answer
417 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,'>='); ...
yaugenka's user avatar
  • 445
0 votes
1 answer
1k 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 ...
Kahn's user avatar
  • 1,813
1 vote
2 answers
2k 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' '...
Eric Lee's user avatar
  • 121
1 vote
1 answer
1k 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 "...
Jakub Holý's user avatar
-1 votes
1 answer
431 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(@...
Y.G.J's user avatar
  • 119
0 votes
0 answers
340 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 ...
user1031947's user avatar
2 votes
1 answer
987 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?
Blender's user avatar
  • 75
0 votes
1 answer
2k 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 ...
Igor Zhilin's user avatar
1 vote
1 answer
3k 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'), ... ...
Panzercrisis's user avatar
0 votes
1 answer
2k 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 ...
Rahul's user avatar
  • 13
1 vote
0 answers
57 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 ...
grochu's user avatar
  • 45
0 votes
1 answer
5k 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, ...
rossco's user avatar
  • 27
2 votes
2 answers
3k 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 ...
bluk's user avatar
  • 133
1 vote
2 answers
1k 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 ...
Eduardo Jiménez's user avatar
2 votes
1 answer
665 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 = '...
Felix Silva's user avatar
0 votes
2 answers
1k 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, ...
Sean Gillespie's user avatar
0 votes
1 answer
1k 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 ...
sebekkg's user avatar
  • 11

1
2 3 4 5
9