Questions tagged [dynamic-sql]
Constructing a query at runtime with string concatenation operations and executing the query from that string.
432 questions
0
votes
1
answer
72
views
sp_executesql with ISNUMERIC(@ColumnName) not working with column name passed as parameter
Question: Why example 1 does not work while example 2 works:
SQL Table to check for non-numeric values in varchar column:
CREATE TABLE #t(Mycol varchar(15))
INSERT #t VALUES ('123.86'),('4a5.67'),('45....
- 525
1
vote
1
answer
229
views
How can I execute Openrowset in the named instance I am currently in?
I am running this openrowset query about jobs on my current server and I see that the result set is not correct.
I was expecting a bunch of jobs but I only get one.
when I run the following piece of ...
- 17.3k
4
votes
2
answers
337
views
Dynamic NVARCHAR(MAX) Being Truncated When 'inlining' a NVARCHAR(MAX) Parameter
The @SQL variable in the statement below is always truncated when I "inline" a variable. If I send the variable in and print it does not truncate to 4000, however, I can't bind the sent in ...
- 683
0
votes
1
answer
174
views
Need to modify Stored Procedure to create table in another database
We have a SQL database that is a secondary database or should I say a secondary replica in an AlwaysOn Availabiltity Group. As a result, the database is always a read-only database and therefore I can'...
- 123
0
votes
1
answer
188
views
Why can I not capture dynamic SQL called with EXEC using the sqlserver.sql_batch_completed Extended Event?
It is my understanding that dynamic SQL is part of a batch. Yet, when I listed for sqlserver.sql_batch_completed as follows
CREATE EVENT SESSION [CaptureBatch] ON SERVER
ADD EVENT sqlserver....
- 1,360
4
votes
1
answer
188
views
Are non-used CTEs never materialized in PostgreSQL 16?
I want to return my post rows in one of three sort orders, controlled by a sorting_param function parameter. The sorting is complex, doesn't lend itself to a simple ORDER BY CASE .... So I'm thinking ...
- 143
0
votes
1
answer
114
views
T-SQL how to dynamically Pivot a tree
I've got a simple parent-child tree relationship that was built recursively
Parent
Child
A
B
B
E
B
C
E
NULL
C
NULL
A
F
F
NULL
this table comes from a recursive CTE:
with tree(parent, child) as (...)
...
- 117
1
vote
1
answer
72
views
Rolling sum with partial value for last day
I can't figure out how to write a query that returns rolling sum by item based on rules in another table.
Below is a table that list in chronological order the stock value of an item on specific days.
...
- 13
0
votes
1
answer
113
views
How can I find all violations of a proposed foreign key on all columns with a specific name?
Let's say I have multiple tables with a column foobar. And I wish to make to make all these columns reference foobar.id as a FOREIGN KEY. The problem is I know there are violations in this schema from ...
- 65.8k
0
votes
3
answers
133
views
Dynamic column selection in a subquery
I have two tables, one containing polls and the other poll votes, like this (simplified):
CREATE TABLE IF NOT EXISTS polls (
id SERIAL PRIMARY KEY,
token VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS ...
6
votes
1
answer
1k
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 ...
- 171
2
votes
1
answer
275
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 (...
- 131
0
votes
2
answers
77
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 ...
- 101
0
votes
1
answer
26
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 ...
- 379
0
votes
1
answer
1k
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 ...
- 444
0
votes
1
answer
535
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 ...
- 5
0
votes
1
answer
150
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....
4
votes
1
answer
677
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
...
- 683
3
votes
1
answer
264
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 ...
- 33
0
votes
1
answer
224
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 ...
- 487
0
votes
1
answer
93
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 ...
- 131
0
votes
1
answer
58
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
...
- 95
2
votes
1
answer
1k
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 ...
- 121
2
votes
2
answers
293
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....
- 123
0
votes
2
answers
1k
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 ...
- 131
3
votes
0
answers
153
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 ...
- 31
2
votes
2
answers
3k
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 ...
0
votes
1
answer
330
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....
- 413
-1
votes
2
answers
100
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 ...
- 167
1
vote
4
answers
2k
views
T-SQL How to retrieve metadata from SQL proc containing temp tables
I've a T-SQL stored proc running in a Microsoft SQL Server 2019 environment. The proc contains temp tables, dynamic SQL and is 2000+ lines. The proc's output has been verified and tested for ...
- 11
0
votes
1
answer
401
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 ...
- 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 ...
- 131
0
votes
1
answer
915
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 ...
- 885
5
votes
2
answers
3k
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 ...
- 178
0
votes
1
answer
280
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, ...
- 459
0
votes
2
answers
5k
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, ...
- 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 ...
- 101
1
vote
1
answer
192
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 ...
- 111
1
vote
1
answer
2k
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)
...
- 11
1
vote
2
answers
1k
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 ...
- 17.3k
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....
- 11
1
vote
0
answers
1k
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
3k
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 ...
- 35
0
votes
1
answer
2k
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 ...
- 206
0
votes
1
answer
37
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$,
$...
- 31
0
votes
0
answers
455
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
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 ...
- 1
0
votes
1
answer
355
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,233
8
votes
3
answers
4k
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,
@...
- 319
4
votes
1
answer
521
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,'>=');
...
- 455