Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [dynamic-sql]

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

0
votes
2answers
59 views

Compile Dynamic Sql before Running

I have a table, and want to test if dynamic sql will compile. Example: create Table dbo.Product ( ProductId int, ProductName varchar(255) ) This will succeed: Column Name exists declare ...
1
vote
2answers
53 views

How can I add “in” keyword in this code to accept more than one value in gender and age parameters using SQL Server 2012

I want the parameters in Age and Gender to accept more than one value Create Procedure [Admission].[SelectPlayerData] @MembershipNumber varchar(20), @PlayerName varchar(70), @PlayerNameEnglish ...
1
vote
2answers
41 views

Dynamic SQL - How can you use it to run a SELECT against all schema's and Tables?

Scenario I have a database that contains all of our customer data, which is separated by Schemas. For example: Customer1 = Schema1, Customer2 = Schema2, Customer3 = Schema3, etc... Sometimes we ...
-1
votes
1answer
29 views

SQL Server _ Cursor in Dynamic SQL and UPDATE CASE WHEN THEN

I would like to extract the rows that meet the condition in the CASE WHEN condition and put them in the updated table. But the trick is to create a new row every time the condition is met in one of ...
0
votes
1answer
36 views

SQL Server - Cursor and Dynamic SQL

this query is working but not giving me the output I want. I am expecting to have 'FAILED' when one of the two conditions is met at least one time through the rows in the columns [TVF] or [RPPS]. But ...
-1
votes
0answers
51 views

Optimize multiple inserts

good day! Let me explain about my issue. One of my users has a sotred procedure to insert all the transactional information into a table for historic records. Nothing out of the ordinary, except for ...
2
votes
2answers
247 views

Create stored procedure that contains newlines with a oneliner sql statement

I want to put code in a single line that will create a stored procedure that contains newlines. Is that possible? Do I need to use sp_executesql? How do I escape newlines in a sql statement? How do I ...
1
vote
1answer
31 views

PostgreSQL - Return type and EXECUTE QUERY

My function returns a setof composite type, however the RETURN EXECUTE seems to return some other unknown type: create type core.search_result as ( user_id uuid, country core.country, ethnicity ...
0
votes
1answer
44 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 ...
2
votes
2answers
294 views

Pass data from a table row into a create login statement using dynamicSQL [closed]

I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more. We've done NO work concerning cursors/...
1
vote
2answers
87 views

Syntax error in SQL Server while making Dynamic SQL

Scenario: I have a table with 100M+ rows of data so I have created an index with something as: CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt ON dbo.base_voter (name_voter,...
-1
votes
0answers
49 views

Error in writing Dynamic stored procedure

I'm trying to write a Dynamic query to pass file name and table name to the procedure dynamically. what is wrong with this query? ALTER Procedure [dbo].[OpenExcel] @FileName nvarchar(2000), @...
2
votes
1answer
162 views

Dynamically define a RETURN table (column type, name) for subsequent loop

I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table. https://postgis.net/docs/ST_Intersection.html https://postgis.net/docs/ST_Intersects.html ...
0
votes
1answer
28 views

How I develop Union Report Views for Multiple Single Tenant Databases?

We have a database for each customer, over 20+ databases. All database schemas are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How ...
-2
votes
1answer
70 views

What is the Optimized Way to Dynamic SQL Select all Columns from Table?

I just generated this script. It will select all columns from a table dynamically. Is there an alternative or more clean, efficient way of conducting this? Additionally, I want to place line breaks ...
1
vote
2answers
63 views

How to get max field value length for each TEXT field in all tables in database

I am trying to get information about the data in my database. I have several columns in several tables that or type 'TEXT'. I want to perform a query that will create a result that looks like: |...
0
votes
2answers
45 views

how to run once a bunch of generated scripts with exception handling?

Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists. So I created a script that calls a procedure that will save the contents of this table ...
1
vote
1answer
43 views

Create DDL Trigger Dynamically

I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works ...
1
vote
0answers
111 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 ...
0
votes
0answers
98 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 ...
2
votes
0answers
81 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
2answers
79 views

Can you use dynamic sql in a select statement?

I have the following code: select [schema].[name] [schema], [table].[name] [table], [column].[name] [column], [type].[name] [type], [column].max_length [...
0
votes
0answers
80 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
2answers
140 views

Dynamically choosing column for WHERE clause

I've got a table that holds columns named "Day1" through to "Day31". Is there a way I can dynamically choose the correct column to put in a WHERE clause? I can get the current day, that's no ...
3
votes
1answer
223 views

Execution time high for Query in sp_executesql

I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below. I run the application and capture the query using profiler....
0
votes
1answer
94 views

How to get newly inserted row ids when inserting with execute?

I have a function which returns newly inserted row count: BEGIN EXECUTE ( SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1', _tbl, ...
-1
votes
2answers
55 views

Dynamic SQL isn't updating table

So I need to basically get the count of every table in a database daily and insert those counts into a separate database for analysis and reporting purposes. The table the counts need to be inserted ...
0
votes
2answers
2k views

How to create a table dynamically based on json data?

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...
4
votes
3answers
178 views

SELECT rows based on indefinite number of filters

How do I create a function which takes indefinite parameters? And then finds all game_ids in a table where each parameter matches a different row (but same game_id)? Example Table: create table ...
0
votes
0answers
24 views

linked server issue while synchronising logins between servers using T-SQL

I have been working on a solution to synchronise logins (using T-SQL) between 2 servers, or between AlwaysOn nodes, inspired by sqlsoldier. It requires a linked Server. this is a partial view of the ...
1
vote
1answer
314 views

Transpose Rows to column

Can anyone Please help me to transpose below result +------+------------+-------+ | SVR | dates | count | +------+------------+-------+ | SRV1 | 03/25/2018 | 1 | | SRV1 | 04/06/2018 | 59 ...
2
votes
1answer
416 views

Stored procedure with case statement for 'AND' based on parameter value

I have a table in my db that stored both successful and failed log-in attempts. I am creating a stored procedure that allow us to delete records which are older than X days old. So far so good, but I ...
1
vote
2answers
384 views

script all triggers in a database

I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications. Every time I need to reinitialise those database I need to script ...
0
votes
1answer
287 views

SQL SERVER dynamic Query with Like Statement for null values

I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only "...
2
votes
2answers
233 views

Safely quoting type names to protect against SQL-injection

How would I quote the type name to protect against SQL Injection. For example, take this SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int'); ^ ...
4
votes
2answers
55 views

Add column for subsequent values for a record

I have a table structured as follows: CREATE TABLE t ( HHID varchar2(1), INDIV_ID int, PRIM varchar2(1) ); insert into t values ('A', 10, 'Y'); insert into t values ('A',...
0
votes
1answer
75 views

sp_executesql changes non standard characters to?

Am trying to use sp_executesql to run commands but I've come across an issue where in non-english characters (aka Chinese & Japanese) are changed to ??? when ran. sql server 2008R2 & 2012 ...
0
votes
1answer
199 views

Set columns dinamically in UTL_FILE

I've been stuck with this problem, so I'm looking for help now. I'm trying to get a .cvs file from a query with PL/SQL. I'm using a cursor and UTL_FILE to achieve that. The problem is that I have to ...
0
votes
1answer
268 views

SQL Server 2017 Management Data Warehouse: collection_set_1 not working due to database trigger

The problem I have got an issue setting up Management Data Warehouse on SQL Server 2017 CU 5. The job "collection_set_1_noncached_collect_and_upload" keeps failing. It is related to the "Disk Usage" ...
3
votes
1answer
85 views

Any way to make stored procedure code dynamically reference other databases, without dynamic sql?

I've a situation where I have two databases: ABCData ABCUsers One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded: INSERT ...
3
votes
2answers
72 views

how to find the database context of the last sql statement?

I would like to find out from the code below, what was the name of the database where the last command was run. In this case I am looking for my_other_database. his there any way of finding this? Is ...
4
votes
2answers
558 views

How to quote qualified table name with format() function?

I have some problems in properly quoting a table name using the format function in the example below. CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text) RETURNS bool AS $...
1
vote
1answer
29 views

Build dynamic text search on table columns

With no success, I am trying to build a dynamic search text on a table with two columns (Id & Number, both columns are unique). The Number column should be grouped and start a new section of ...
2
votes
1answer
78 views

Generating SQL with SQL [closed]

I'm trying to generate SQL statements using SQL to create SQL code that will be highlighted and run by an admin to process the statement. My issue is with the code I have created. It's not efficient ...
2
votes
2answers
456 views

Dynamic SQL Query with Paging, Performance and Optimization

I have an interesting problem in front of me. There is a database with ~1 million user accounts with an anticipated growth of 1-2 million per year. The database is strongly TPT but this particular ...
1
vote
1answer
346 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
258 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
2answers
475 views

SQL Server Extend dynamic SQL query with where part fetched from Stored Procedure

I have a stored procedure that creates a dynamic SQL Query. I use sp_executesql, to have it SQL injection safe. I have a second stored procedure that generates a where logic. For example if I run the ...
1
vote
1answer
146 views

Loop INSERT EXEC AT through linked servers

Relating to Inserting EXEC AT linked server into temp table I am now in the midst of dynamically insert each result of dynamic sql to the temp table for each registered linked server. Below is my ...
1
vote
1answer
3k 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 [...