Stack Overflow
Stack Overflow

Questions tagged [dynamic-sql]

Dynamic SQL is a technique using SQL (Structured Query Language) whose main difference from traditional SQL is that Dynamic SQL allows to build SQL statements dynamically at runtime, which eases the automatic generation and execution of program statements.

2681 questions
0
votes
0 answers

SQL Server: Materialized view based on stored proc with dynamic sql - how to

My client wants a pivot table, showing the performance of each month (column headers) per department (row headers). It has to be be possible to insert a 'as-of date' as a parameter, so the user (PHP) can pass that date and the pivot only shows…
Paul de Roos
  • 47
  • 5
0
votes
1 answer

How to define a complex return type without a table?

I have a stored procedure with dynamic sql that I need to return an OBJECT_RESULT type. I am trying to build the complex type within visual studio through EF. The problem, I believe, is coming from the fact that the table is one of the parameters of…
Morks
  • 284
  • 3
  • 15
0
votes
0 answers

How to dynamically or recursively compare columns within tables together in SQL

How to dynamically compare x number of tables and generate dynamic messages? I have the following schema: Table1: customerID, startDate, endDate Table2: customerID, startDate, endDate Table3: customerID, startDate, endDate And so on. Customers in…
Is_It_Broken
  • 1
0
votes
1 answer

Conversion failed when converting the varchar value ' AND ID =' to data type int

I was looking how I can parameterize table names and so I found dynamic sql queries. I finally got the proc saved, but when I execute it errors out with "Conversion failed when converting the varchar value ' AND ID =' to data type int." I have no…
Morks
  • 284
  • 3
  • 15
0
votes
1 answer

Dynamic SQL select multiple columns based on json field(s) without exposing SQL Injection risks

I'm creating a 'select colA, colB, colC, ...' stored proc, and I want the choice of columns to return to be based on JSON input. The most basic way to achieve that is DECLARE @jsonField nvarchar(max) = 'col1, col2, col3' --will get this from input…
The Lemon
  • 1,211
  • 15
  • 26
0
votes
1 answer

Stored procedure using Dynamic SQL to create new table in Oracle

The question is as follows: create a stored procedure (NEW_TABLE) with two strings as input parameters: String 1: Name of the table to be generated String 2: Name of columns and their datatypes. For example: 'ID Number, ProductName varchar2(50),…
Snoke
  • 75
  • 7
0
votes
2 answers

SQL prefix for multiple insert/update

I have prepared one script that updates/insert new values into few tables. Instead of using hardcoded values, I want to use prefix that will allow me to refer to this schema as variable. Example as per below. Instead of using SKC I want to use for…
matmen
  • 1
0
votes
1 answer

Dynamic SQL Rolling 12 Months Pivot

I am new to Dynamic SQL. I have the following table: CustName Date Hours First 01/01/2021 12 Second 01/01/2021 10 Second 05/02/2021 1 Second 10/11/2021 14 I am trying to make a rolling calendar Pivot for the last 12 months of…
Jim Dover
  • 593
  • 2
  • 12
  • 30
0
votes
1 answer

Using copy command within format in a stored procedure using Postgres plpgsql

I am trying to create a stored procedure in Postgres for generating CSV files containing the average values of some tags/variables between the last 15 minutes. But when I try to implement the 'where' condition for selecting the tag values in the…
Joyal Joy
  • 37
  • 8
0
votes
3 answers

Dynamically select columns in SQL

I have a question regarding the creation of a script to dynamically select columns for a select statement. Here is my scenario: I have some tables that have columns that are called "text1-n", "value1-n", "checkbox1-n" etc. I want to check whether…
scarabeaus
  • 13
  • 7
0
votes
3 answers

Oracle Create formula for dynamic SQL to prevent divide by zero

I have a table with formula column that have formula to fetch to dynamic SQL LIKE n5/n14+n3 My problem is when formula have divide, I should try convert the formula to prevent error divide by zero. My current idea is convert formula to CASE when it…
Pham X. Bach
  • 5,284
  • 4
  • 28
  • 42
0
votes
2 answers

SQL Server : using column values as table headers in a view

I currently have a table in SQL Server that looks something like this: Machine Serial Date Parameter Value Machine 1 12345 7/22/2021 Param 1 789 Machine 1 12345 7/22/2021 Param 2 456 Machine 1 67890 7/22/2021 Param 1 123 Machine…
Addison Waege
  • 49
  • 1
  • 3
  • 13
0
votes
1 answer

how to execute a query that is into a variable of a database in PostegreSQL

I have a question. so I have a query into "txt". txt is a column of one of my table(opt_xel_views) and i want to know how to execute the query that is in txt. Here is my code
Kheira
  • 7
  • 7
0
votes
3 answers

Does Parallel Hint within Dynamic SQL of Oracle run in parallel?

I have both DML and DDL as part of my procedure and enabled the parallel on both DML and DDL. I want to run them in Parallel mode using parallel hint but neither of them execute in parallel. Is this a limitation of using the Dynamic SQL? For…
Venkat
  • 107
  • 12
0
votes
1 answer

While executing oracle alter query getting error : ORA-01756: quoted string not properly terminated

I am trying to execute below query in my .sql file and getting error: quoted string not properly terminated execute immediate 'alter tablespace '||tname||' add datafile '''||file_name||lname||ext||''' size '||add_space||'m autoextend off maxsize…
Divya
  • 35
  • 7
1 2 3
…
99 100