Skip to main content

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
5 votes
1 answer
173 views

How does sp_executesql interact with temporary tables?

I'm at a loss as to why some SQL leveraging sp_executesql is working the way it is when temporary tables are involved, along with INSERT and SELECT - INTO. I'm using SQL Server. Here is the code in ...
Wipqozn's user avatar
  • 161
2 votes
1 answer
152 views

Using a function as argument to GRANT fails with syntax error

I can't get GRANT to take current_database() as argument. These all fail with the same error: GRANT ALL PRIVILEGES ON DATABASE current_database() to justintestin; GRANT ALL PRIVILEGES ON DATABASE (...
Chema's user avatar
  • 131
0 votes
2 answers
39 views

Does Oracle database server stored runtime dynamic SQL?

I have a plsql below that executes dynamic sql. DECLARE sql_stmt VARCHAR2(200); BEGIN sql_stmt := 'SELECT employee_name FROM employees WHERE employee_id = :1'; EXECUTE IMMEDIATE sql_stmt ...
Idonknow's user avatar
  • 101
-1 votes
1 answer
94 views

Using CURRENT_USER in Dynamic SQL Adds a Dot to the End of the Username [closed]

This is for Azure SQL Server, I have some code that creates the current user as a user in an existing database: DECLARE @sql NVARCHAR(MAX); SET @sql = N'CREATE USER [' + CURRENT_USER + N'] FROM LOGIN [...
SmudgerDan's user avatar
0 votes
1 answer
12 views

How to format an array of identifiers?

I would like to write a function to define triggers. I would like to call it this way: CALL make_mutable('mytable', '{"mycolumn3", "mycolumn4"}'); I think I need a dynamic ...
ceving's user avatar
  • 339
0 votes
1 answer
302 views

Get absolute number and percentage of NULL values for each column of each table in a given schema

I'd like to extract some basic statistics about how tables are populated in a given PostgreSQL 16 schema, such as the absolute number and percentage of null values for each column in all tables in ...
s.k's user avatar
  • 372
0 votes
1 answer
169 views

OPENROWSET on sp_executesql with two dynamic parameters

Can someone give me a working example of using OPENROWSET on SQL Server with dynamic SQL (sp_executesql) executing a stored procedure with two parameters as variables, preferably date and integer? I ...
mk SQL's user avatar
  • 5
0 votes
1 answer
66 views

Update selected columns containing 0 with NULL

I want to update all columns named with suffix '[]' in the table geochem if they have zeros (0) with NULL. This query returns all relevant column names: select c.COLUMN_NAME from information_schema....
kitchenprinzessin's user avatar
4 votes
1 answer
331 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
  • 567
3 votes
1 answer
120 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
97 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
  • 355
0 votes
1 answer
53 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
37 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
674 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 ...
kaushalyap's user avatar
2 votes
2 answers
166 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
684 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
  • 121
3 votes
0 answers
97 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
2k 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
211 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
  • 357
-1 votes
2 answers
65 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
326 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
  • 119
2 votes
1 answer
3k 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
557 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
5 votes
2 answers
2k 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
232 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
  • 459
0 votes
2 answers
3k 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
  • 459
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
138 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
740 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
922 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
1k 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
384 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
289 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,223
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
465 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
  • 455
0 votes
1 answer
2k 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
468 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
414 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
1k 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
2 votes
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
3k 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

1
2 3 4 5
9