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
1 answer
16 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 ...
2 votes
1 answer
27 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 ...
0 votes
1 answer
28 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 ...
0 votes
0 answers
23 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 ...
  • 109
0 votes
0 answers
23 views

Convenient way to select 100+ columns (but not ALL the table columns) in SQL query at a time?

I have a table with more than 100 columns and I want to query all but the first and second columns. How do I do it quickly without enumerating 100 columns? Just for illustrative purposes, suppose I ...
  • 101
5 votes
2 answers
455 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
54 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, ...
  • 389
0 votes
2 answers
151 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, ...
  • 389
0 votes
0 answers
269 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
67 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
0 votes
1 answer
389 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
300 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
900 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
462 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
975 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
231 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
23 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
223 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
1k 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
153 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,109
8 votes
2 answers
2k 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, @...
4 votes
1 answer
199 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,'>='); ...
  • 445
0 votes
1 answer
588 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,765
1 vote
2 answers
1k 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' '...
  • 121
1 vote
1 answer
494 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
205 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
124 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
613 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
857 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
1k 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
1k 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
3k 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, ...
  • 27
2 votes
2 answers
601 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
536 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
331 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
515 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
798 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
1k 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
80 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
3k 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
24 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
183 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
456 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
749 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
327 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
46 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
271 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
638 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

1
2 3 4 5
9