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.
Questions tagged [dynamic-sql]
2681 questions
0
votes
2 answers
SQL Server, Dynamically join using the column name and value from same table with a different table
I have two tables as below that I am looking to join dynamically using the ColVal in TMaster with ColName in TMaster which is the name of the column to match with in TTable1.
TMaster:
TTable1:
So, final result would be the data from TTable1 that…
TSR
- 714
- 2
- 7
- 24
0
votes
0 answers
Struggling to link dynamic SQL statements together without error
DECLARE @overview TABLE(
category VARCHAR(50)
)
INSERT INTO @overview
SELECT DISTINCT category
FROM table_3
DECLARE @temp_table TABLE(
category VARCHAR(100),
increment IDENTITY(1,1)
)
INSERT INTO @temp_table
SELECT DISTINCT…
user14968098
0
votes
1 answer
Postgresql conditionally include distinct in query
Postgresql conditionally include distinct in query
Is there way to modify a query such as:
select distinct col1, col2
from our_schema.our_table
where (id = '1001')
The goal is to easily activate/deactivate the distinct…
JosephDoggie
- 1,514
- 4
- 27
- 57
0
votes
1 answer
Insert to table then Conversion failed when converting the varchar value to data type int
I am trying to INSERT a row when the row does not exist. I have 2 tables, and I check 1 by 1, if the record does not exist in the second table, then do INSERT.
DECLARE @Counter int;
DECLARE @jumlah int;
DECLARE @namamerchant varchar(200);
DECLARE…
Romli Eko
- 45
- 1
- 9
0
votes
0 answers
OPENDATASOURCE doeasn't work in @DynamicSQL
Few days ago I asked on StackOverflow how to query multiple server remotely, a user replied promptly with the right answer and it works perfectly:
DECLARE @ServerName varchar(50), @DynamicSQL NVARCHAR(MAX)
DECLARE @myTableVariable TABLE (id INT,…
Francesco Mantovani
- 10,216
- 13
- 73
- 113
0
votes
1 answer
Is a cursor or second stored procedure required for a set of dynamic SQL statements with dynamic table names
I want to iterate over a temp table that contains rows of table names I retrieved from sysobjects with:
select s.name from sysobjects s where s.type='U'
My temp table might have:
#tab_name
myTable1
myTable2
myTable3
...
With those table names I…
johnny
- 19,272
- 52
- 157
- 259
0
votes
2 answers
Create dynamic list of tables based on list with wildcards
So I have a list of tables that I want (with wildcards)
CREATE TABLE [config].[datalist](
[id] [int] IDENTITY(1,1) NOT NULL,
[order] [int] NULL,
[dbname] sysname NULL,
[schemaname] sysname NULL,
[tablename] sysname NULL
)…
Henrov
- 1,610
- 1
- 24
- 52
0
votes
1 answer
How to use wild card/like operator in dynamic query
I am trying to create a dynamic query as below in SQL Server. But keep getting error as:
Msg 105, Level 15, State 1, Line 9
Unclosed quotation mark after the character string '%[^a-Z0-9]%'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near…
Rohit
- 1
- 2
0
votes
0 answers
Dynamic display of columns and rows SQL
iF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL
DROP TABLE #Temp;
SELECT distinct ',' + QUOTENAME(a.Name)
into #Temp
from tableB b
inner join tableC c on c.Id = b.Id
inner join tableA a on a.Id = c.Id
--select *…
Neca24
- 49
- 1
- 7
0
votes
1 answer
Insufficient INHERIT PRIVILEGES for a stored procedure ORACLE
I am having a stored procedure in user META. This SP truncates a table in user STAGE.
CREATE PROCEDURE META.sp_truncate_tablex
AUTHID CURRENT_USER
as
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE STAGE.Tablex';
END;
When I run the SP I get RA-06598:…
Amir
- 399
- 1
- 7
- 25
0
votes
1 answer
Dynamically create columns in select SQL Oracle
I want to show the investigator's name and title in one row.
How can I make the title column as title1, title2, title 3, or so on dynamically in SQL Oracle?
The number of titles can vary so if there is an investigator with 4 titles, then there…
may
- 23
- 1
- 9
0
votes
0 answers
Access Subform SourceObject Set to Query updated with SQL - Column Order and Width?
I have an Access form with different functions users can do. They are pulling lists of data, all from 1 table. Based on different filters or operations they want to do, I dynamically construct the SQL, use that to update the query defs of a query,…
missscripty
- 529
- 2
- 11
- 30
0
votes
1 answer
problem in executing dynamic query using pivot with syntax error Incorrect syntax near ' + '
I want to Create a query with dynamic query and in this query i use concat some cells , when use concat like 'N'subject:' + ' ' + ContentProductionTitle' i get syntax error
error : Incorrect syntax near ' + '.
DECLARE @cols AS NVARCHAR(MAX),
…
arezu panahinia
- 1
- 3
0
votes
2 answers
Syntax for parameterized PostgreSQL function using dynamic SQL
This code:
ALTER TABLE myschema.mytable add column geom geometry (point,4326);
CREATE INDEX mytable_idx on myschema.mytable using GIST(geom);
UPDATE myschema.mytable set geom = st_setsrid(st_point(mytable.long, mytable.lat), 4326);
This works fine…
Encomium
- 257
- 4
- 14
0
votes
1 answer
Adding DB2 dynamic SQL code within an existing where clause
I have a query which runs for orders placed in the last hour. Depending on the client, certain conditions are applied to the WHERE clause of the query. The SQL WHERE Clause is stored in a separate table for each client.
The query would look like…
Cypherwolf
- 25
- 7