Questions tagged [dynamic-sql]

Constructing a query at runtime with string concatenation operations and executing the query from that string.

43 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
2answers
2k views

Update query in a trigger

I have a configuration table: Table: refdatecol id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
2
votes
2answers
266 views

Postgresql return results of dynamic SQL

I'm using Postgresql, version 12. My goal is to create a function, or stored procedure, which generates a dynamic select which is returned by the function or stored procedure. The difficulty is that ...
2
votes
0answers
65 views

RAISE NOTICE doesn't work as expected while using within Dynamic SQL block

I have a small piece of code to get all sequences and the current and maximum value of all sequences in a database. I have tried with recursive and some other alternatives through plain sqls, however ...
2
votes
1answer
765 views

How to send parameters to BCP inside dynamic sql using sp_executesql

We are trying to run a BCP-query dynamically to put some data to files. However, when using the following code, it does not seem that sp_executesql understands that @RId inside the BCP query is a ...
2
votes
0answers
202 views

Plan Guide for the LINQ-SQL Query

I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...
2
votes
0answers
298 views

security concerns over enabling data access for the current server

Included in the code of the creation of some stored procedures, I have the enabling of data access as you can see below. BEGIN TRY PRINT @@SERVERNAME PRINT DB_NAME() PRINT 'enable the ...
2
votes
0answers
418 views

plan_generation_num keeps incrementing, why?

I have a query that is executed using sp_executesql code is as follows DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3); SET @ppCityID = CAST(@cityID AS NVARCHAR(3)); exec sp_executesql N'...
1
vote
0answers
19 views

MySQL 5.7 - Calling procedure with dynamic name

I'm trying to write a MySQL (5.7) procedure that dynamically calls another procedure based on the one of its inputs. The code I came up with so far is this (procedure_exists works for checking ...
1
vote
0answers
16 views

Source Control Dynamics

What ways are there to do version control of an MS Dynamics database? I've found mentions of DACBAC, and obviously SSIS packages could just be dumped in a git repo. I'm particularly keen on learning ...
1
vote
0answers
30 views

Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?

I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with ...
1
vote
0answers
489 views

How to run a dynamic select query with column names from another query?

I'm new to Postgres, so I'm sorry if this is overly simple. But I've searched for ages, and can't find a solution for my dynamic-sql problem. I have one table that contains the list of columns in a ...
1
vote
0answers
531 views

SQL Buffer Pool and Plan cache

Does SQL Buffer Pool size = Max server memory.? I have been trying to find out the reason why plan cache in my SQL server 2016 gets cleared every few hours. I have 128 GB Physical Memory. Earlier i ...
1
vote
1answer
872 views

How to specify with a parameter a field to extract from a RECORD variable

Here's a table: CREATE TABLE t_heights (id INT, height REAL); INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06); Reading through the table rows with a FOR...LOOP and a RECORD type ...
1
vote
0answers
598 views

MySQL dynamic number of columns

I'm looking for a way to dynamically build up the number of columns in a query. Imagine a school assessment database. I desire a way to get, for each question the percentage of students who got a ...
1
vote
0answers
76 views

listing the permissions for a database object - and take into consideration it can be a synonym

I have a script that gives me all the permissions assigned to a database object: ------------------------------------------------- -- permissions to database object -- marcelo miorelli v. 20170401 ---...
1
vote
0answers
620 views

BULK INSERT ERRORFILE Not Created

We have stored procedure on SQL Server v12.0.5203.0. Within that procedure we have a dynamic SQL statement that performs BULK INSERT. We are trying to capture misformatted rows and continue processing ...
1
vote
0answers
47 views

Dynamic content update problem

I have three tables: forms which contains basic information about a form form_parts which contains information about a certain input field and has a FK to forms (each form parts must have one ...
1
vote
0answers
56 views

Are there any pitfalls to be aware of when using dynamic type-coercion in SQL Server?

I want to insert into a database with a query that looks something like the following: INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>); As I ...
1
vote
0answers
36 views

Dynamically determine the starting time and ending time for a. . . Breakfast of Champions!

Dynamically determine the starting time and ending time for a between statement based on the results of a previous query's table records when ordered appropriately for TOP and BOTTOM records ...
1
vote
0answers
533 views

Select where table and column name are from another table

I know I need dynamic sql, but haven't gotten this to work: I want to loop through a table and for each row, return the value of the field that is located by two columns for that row. Master ...
1
vote
0answers
1k views

Partly rotated table with pivot/dynamic SQL without aggregation

Using MS SQL 2008, I have created a table that I want to display in horizontal direction instead of vertical direction. Because I do not now how many query results I will get returned I think I have ...
0
votes
0answers
5 views

Join a specific table depending on the provided values in a table

I have Maps (as in a game map) which each can have one or more drops, where those can vary between being currencies, items among other stuff that is separated in multiple, different tables because the ...
0
votes
0answers
14 views

Save multiple dynamic filter for aggregation in Postgresql

What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data? Lets assume I have a table called t_filter which ...
0
votes
1answer
30 views

Change database and execute update query (T- Transact)

Could anyone face with issue like me? I need to update some rows in table, but I don't know exact name of database. I am able to figure out name with: Select name from sys.databases where name ...
0
votes
0answers
25 views

Pass scale value for NUMERIC datatype dynamically

I want to pass scale value for NUMERIC datatype dynamically: DO $$ DECLARE v_roundfact INTEGER; BEGIN v_roundfact := 3; CREATE TEMP TABLE TempTable ON COMMIT DROP AS ...
0
votes
1answer
27 views

SQL stored procedure to produce custom XML text

I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables. With my current solution I have a stored procedure for each 30 tables where I do: ...
0
votes
2answers
58 views

Join to tables dynamically depending on data

have a logging table that contains references to other objects in other tables. I want to create a view to add some additional data to the logging table by looking back at the other objects. For ...
0
votes
0answers
16 views

postgres 9.6 - use data from xml to call procedure

I work with a system based on Postgres 9.6 (updated from 9.1 or something similar). One of latest changes requires an ability to remotely call procedure on database with some arguments. Due to various ...
0
votes
1answer
64 views

INSERT INTO… SELECT over a LinkedServer in smaller batches

I've written the following code to move data from one database table, to another (over a LinkedServer); SET NOCOUNT ON DECLARE @ApplicationURL varchar(50), @LinkedServer nvarchar(50), @...
0
votes
0answers
13 views

Need to break up a large file into smaller files to send via sp_sendsqlmail

I have created a job to send a "report" to someone (actually two reports). My issue is that the query is returning some 150K+ rows of data (no lobs or xml or anything, so not large rows), but ...
0
votes
0answers
20 views

PostgreSQL multi-pivot with keys as column names rather than categories?

I am trying to use crosstab() to generate a tricky pivot which is not really a true pivot. I am looking to get the key names as column names rather than the category. E.g. create table pivot (id ...
0
votes
1answer
34 views

How to select maximum date_created value from all tables matching certain pattern?

Given a MySQL DB with a hundreds of tables with the ones of interest being named ap_form_* where * = a random value. How do I loop through that list of tables to display the MAX(date_created) for each ...
0
votes
1answer
772 views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
0
votes
1answer
98 views

postgres dyanmic and unlimited where conditions

How would one properly write a FUNCTION in postgres to return a TABLE based on a query where the query WHERE clause can have both an unknown number of filter conditions, and the conditions themselves ...
0
votes
0answers
331 views

SSIS: Using Event Viewer to Trace Errors of A Package

Overview A package was developed due to a planned database server migration, and other teams with a dependency on legacy cross-database queries. There is a policy of not having Linked Servers, so an ...
0
votes
1answer
262 views

Comparing multiple tables on two databases using dynamic PL/SQL

The issue I'm having is I have a block of dynamic SQL that I can't get the results from. Here's the SQL: CREATE OR REPLACE PROCEDURE GGCHECKER IS cursor c1 is SELECT TABLE_NAME, ...
0
votes
2answers
3k views

Fill in a table name using a variable from another query

I have a general query I need to run for many tables SELECT sum(honoraria_amount) FROM table_1 The table IDs I have a list, from another query. For example, I want to run this for: table_1, table_39,...
0
votes
0answers
2k views

How to create Dynamic table in stored procedure?

We want call a Stored procedure which returns 4 tables in my Stored procedure.To create dynamic table in my sp I am using OPENROWSET. While executing my new sp i am getting Column name or number of ...
0
votes
1answer
216 views

Get the deleted value using while looping through the columns

I wrote a trigger to do auditing. To capture all the changes, I loop through the list of columns. The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get ...
0
votes
0answers
377 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
0
votes
0answers
1k views

Run a specific SQL Server job step from a stored procedure

I have a job with 5 steps that are scheduled to run after each other. I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter. Only ...
0
votes
0answers
376 views

Execute SQL at different server (with dynamic server name)

I'm using a settings table to configure on which servers a specific kind of data is stored, and I would like to do this as dynamically as possible without having to add the server as a linked server ...
0
votes
1answer
52 views

Executing an oracle variable string as I do in SQL Server

In SQL Server I can do something like: DECLARE @VAR VARCHAR(100); SELECT @VAR = SELECT * FROM SYS.DATABASES EXECUTE(@VAR) How can I execute a string that I've created inside a variable in Oracle? ...