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
-1
votes
1answer
34 views

Dynamic sql query with top count sqlserver [closed]

My Requirement I need to set\get a variable's value using the dynamic query I need to write a dynamic sql query to select top (@topCount) The @topCount will need to be a calculated value using the ...
2
votes
1answer
830 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 ...
6
votes
2answers
5k views

triggers - using the inserted / deleted tables in Dynamic SQL

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables. Declare @NewID varchar(50) = Replace(...
0
votes
1answer
790 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
35 views

Dynamic SQL breaks statement

I am attempting to write a query that pivots a certain column that may have any number of distinct values in it. I am able to get this to work with static values, but when I try to parametrize the ...
13
votes
4answers
12k views

Why you want to avoid Dynamic SQL in stored procedure?

I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
0
votes
2answers
61 views

how to resolve ORA-04042: procedure, function, package, or package body does not exist and ORA-00942: table or view does not exist

I am trying to grant EXECUTE and READ privilege on two tables to a user ktest1. grant EXECUTE on SYS.KIR_DOKUMENT to ktest2; grant READ on SYS.KIR_DOKUMENT to ktest2; when i give grant execute i get ...
0
votes
0answers
6 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
1answer
56 views

Limiting the occurence of a value in PostgreSQL

Using PostgreSQL 11.7 and I have to model some real-world constraints. Effectively, I'm modeling a graph and an edge, of course, has two nodes. Each node can have zero, one, or two edges. Thus, I need ...
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 ...
2
votes
2answers
304 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 ...
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,...
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 ...
1
vote
1answer
895 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 ...
8
votes
1answer
12k views

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared I have a Stored Procedure which contains a Prepared Statement and a view DROP PROCEDURE IF EXISTS `sampleProc`...
5
votes
3answers
14k views

what is causing arithmetic overflow in the query below?

when I run the following query on MY_Database select * from sys.sysfiles I get the following results: But when I run a dynamic query that gets the percent of free space I get: Msg 8115, Level ...
0
votes
3answers
567 views

Dynamic query execution error

I'm trying to execute the following dynamic query code: declare @sql nvarchar(max) set @sql = 'DECLARE @DatePartitionFunction nvarchar(max) = N''CREATE PARTITION FUNCTION DatePartitionFunction (...
1
vote
1answer
63 views

Problem locating rows with dynamic SQL

I'm new at SQL in general, so I'm have a few problems with some of my stored procedures I'm designing. My procedure takes in 3 parameters. A location (or table name basically, a varchar), a room ...
2
votes
2answers
3k views

Execute Dynamic SQL Generated From Select

A business process in SQL can generate a variable to each operation 1-N number of calls to a stored procedure whose purpose is to insert data related to the main process. How does one execute ...
7
votes
2answers
1k views

Why does this cursor produce results in the incorrect order?

I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes. The line ORDER BY 1,2,3 DESC; in the SQL ...
1
vote
2answers
973 views

How can I pass a list of databases to sp_MSforeachdb?

I have the following script that when run gives me a comma delimited list of database names. SET NOCOUNT ON declare @db_list NVARCHAR(MAX) DECLARE @command varchar(1000) ...
8
votes
2answers
5k views

Generate script to automate renaming of default constraints

Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D I would prefer to ...
0
votes
1answer
46 views

Special letter behaviour problem?

I asked question in very blurry manner because I cannot explain in short sentence what is my problem about. In general I have a set of procedures which transforms text and XML. Inside the procedures ...
7
votes
3answers
13k views

EXEC vs SP_EXECUTESQL Performance

Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC. I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference ...
4
votes
2answers
2k views

Safe alternative to exec(sql)

I've found some exec(sql) statements buried in the code. They're there for good reason, because these statements couldn't be written directly, however they are an obvious attack vector. Is there a ...
4
votes
1answer
51 views

sp_executesql: what can be parameterized

Is there a definitive list published showing what can and cannot be parameterized in a call to sp_executesql. For example, predicates can, table names cannot. Number of rows in TOP can: exec ...
-3
votes
2answers
67 views

Why does IO Statistics show a lot of LOB Logical Reads on the Worktable when none of the tables in my query use any LOB datatype fields?

I have a procedure that runs a query dynamically across a few hundred databases that all have the same exact schema and aggregates the results to a temp table. The query only involves 3 tables (each ...
0
votes
1answer
27 views

Generate GRANT for all tables reference by a procedure

I have a couple of stored procedures in my DB in which each one select from a couple of tables. I need to construct a statement where I can give it a stored procedure and get the grant select ...
0
votes
1answer
31 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 ...
8
votes
4answers
5k views

Dynamically convert hstore keys into columns for an unknown set of keys

I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns. Users can ...
0
votes
1answer
22 views

Get availability group and listener of the current DB (dynamic SQL)

I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate ...
0
votes
1answer
217 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
26 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
102 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 ...
8
votes
3answers
42k views

Postgres plpgsql - Using a variable inside of a dynamic create statement

Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as: ... DECLARE tblVar varchar := "myTable"; BEGIN EXECUTE 'CREATE TABLE $1 ( foo ...
-3
votes
1answer
79 views

Dynamic table names [closed]

I am trying to create dynamic table names for each month. This is how I tried it: declare @query varchar(max) declare @i date declare @tableid int declare @tblname varchar(20) ...
0
votes
1answer
265 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, ...
1
vote
0answers
21 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
votes
1answer
263 views

Stored procedure in SQL Server that returns total count of columns in table and distinct count of values in each column [closed]

I need a stored procedure that takes any table name as a parameter and returns total count of rows and distinct count of values in each column of that particular table when executed. Let's take the ...
0
votes
1answer
58 views

How to insert into temp table on Server A results from dynamic SQL open query stored proc call to remote server B via Remote Query showplan operator?

I have to call a stored procedure on server B from server A using open query via dynamic SQL, and insert the results into a temp table created on server A. I noticed in the execution plan, it's using ...
3
votes
4answers
1k views

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

Weird question?...maybe, but I have a need. :) I have a stored procedure that I want to use universally in any database. The stored procedure generates some dynamic SQL and then executes that SQL in ...
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 ...
7
votes
2answers
7k views

Using INSERT INTO…EXEC AT linked server into temp table fails with Msg 7391

I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is insert into ##eapb EXEC (@remotecmd) AT [...
-2
votes
2answers
160 views

Wrong Output from Query

I am unable to get data from bnrcno column while using the below query. select 'insert into table values('||id||','||bnrcno||','||chargetype||','||domno||','||coa_id||','||liability_gl||','||...
0
votes
1answer
78 views

Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?

I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will ...
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: ...
1
vote
1answer
36 views

Stored procedure to set value to a column must accept returned values from functions

I created a very simple table in Data Mart to store ETL diagnostic information: CREATE TABLE [internal].[etl_status] ( [property_name] VARCHAR(100) NOT NULL PRIMARY KEY, [property_value] ...
0
votes
2answers
65 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
65 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), @...

1
2 3 4 5
7