Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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
4
votes
3answers
201 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 ...
1
vote
1answer
1k 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 ...
3
votes
1answer
1k 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
2k 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 ...
4
votes
1answer
201 views

Execution plan caching of ITVF called with inline vs dynamic SQL

I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function [dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (...
0
votes
1answer
817 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 "...
4
votes
2answers
60 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',...
2
votes
1answer
1k views

Dynamic SQL UNPIVOT and putting results into existing table

I know that there are a few questions out there on Dynamic SQL and Pivoting, but I'm unable to figure out the issue that I came across since I'm not familiar with Dynamic SQL/Pivoting. So, I have the ...
2
votes
2answers
394 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'); ^ ...
0
votes
1answer
165 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
475 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
458 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" ...
1
vote
1answer
43 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
82 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
884 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 ...
2
votes
1answer
1k views

Snapshot_isolation_state_desc NOT turning off [closed]

I am trying to resolve the following issue and need your help. We wanted to turn on is_read_committed_snapshot_on and Snapshot_isolation_state_desc ON on our db! When I ran following command to ...
1
vote
0answers
448 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 ...
3
votes
2answers
1k views

Find all the SP's where a particular column is not updated

I want to find stored procedures where a particular column is not updated. As an example SP1: BEGIN UPDATE YourTable SET Foo = @Foo, Bar = @Bar WHERE Id = @Id ...
1
vote
1answer
415 views

Dynamic SQL in a pivot table

I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant ...
5
votes
2answers
17k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
1
vote
2answers
817 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 ...
22
votes
4answers
78k views

Is there a way to generate table create script in TSQL?

Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a ...
1
vote
1answer
57 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 ...
6
votes
1answer
8k views

Create view in a different database with dynamic SQL

As part of a larger project I'm trying to create a new view on each database in a SQL Server instance. I've created a stored procedure that among other things is looping through the databases in ...
0
votes
3answers
536 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 (...
0
votes
1answer
5k views

INSERT INTO statement with parameters for tableName, columns and values?

Is it possible to have an SP that looks something like this: DECLARE @tableName = null; DECLARE @cols = null; DECLARE @values = null; INSERT INTO @tableName @cols @values; The resultant query looks ...
7
votes
3answers
11k 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 ...
1
vote
1answer
6k 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 [...
1
vote
1answer
301 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
903 views

How to return rows from multiple tables selected from information_schema

I need to return rows from a set of tables found in information_schema. I can find the table names, and I have a function that compiles and executes, but it returns an empty set, so there must be ...
1
vote
1answer
7k views

metadata could not be determined because stored procedure contains dynamic SQL

I have a stored procedure that contains some dynamic SQL. I am trying to execute the stored procedure using OPENROWSET but am getting the error: Msg 11514, Level 16, State 1, Procedure ...
0
votes
3answers
2k views

Dynamic conversion of long data to wide with multiple columns

I'm trying to convert long data: ID|SchoolID|Section|RepScore|SportsScore|PartyScore 1 |20 |1 |23.2 |70.2 |42.3 2 |20 |6 |65.2 |75.8 |52.3 ...
0
votes
1answer
41 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 ...
1
vote
2answers
288 views

Execute dynamic query inside procedure

I'm trying to build a procedure as follows: CREATE PROCEDURE [test].[ZTEST1] @Messages NVARCHAR(4000) AS SET NOCOUNT ON; BEGIN Declare @SQL nvarchar(1000); SET @SQL = 'SELECT parsename(replace(...
7
votes
3answers
11k views

Duplicate row with Primary Key in PostgreSQL

Assume I have a table as follows named people, where id is a Primary Key: +-----------+---------+---------+ | id | fname | lname | | (integer) | (text) | (text) | +===========+=========+=...
1
vote
1answer
73 views

Function taking a string as table and column name

I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table: CREATE OR REPLACE FUNCTION survey_participated(_sn text) ...
0
votes
1answer
762 views

How to get list of all tables in SQL Server without specifying a db?

I have a function that allows me to check if a table name is valid, that looks like this: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tbl But this has to be executed in the ...
3
votes
1answer
1k views

Making dynamic queries for linked server in SQL Server

I want to create a dynamic query in SQL Server which will run on linked server. I am trying to do it as follows. USE [MYDB] GO DECLARE @company AS nvarchar(50); DECLARE @id nvarchar(MAX); DECLARE @...
3
votes
1answer
3k views

“ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

I have a plpgsql function to create child tables using table inheritance in PostgreSQL like this: CREATE TABLE parent_table ( value integer, end_time timestamp without time zone ); CREATE OR ...
0
votes
1answer
66 views

Create view using tables from differing databases on a server

I need to create a view that pulls data from multiple databases on a server. I've done this by hard coding the database names (db1.schema.table), but I need it to be more dynamic. This view needs to ...
0
votes
1answer
58 views

Declaratively select an entity requiring relations from all (dynamic number of) related entities

I am trying to construct a where clause that requires an X number of children to have a relation to a specific parent with a N-to-N relationship. Something similar to "Get all receipts where these ...
1
vote
2answers
10k views

Incorrect syntax near ')' [closed]

I'm trying to execute the following stored procedure: CREATE PROCEDURE dbo.Compress_taille(@nom_table VARCHAR(64)) AS PRINT @...
8
votes
2answers
10k views

PostgreSQL: Using schema and table names from other tables?

Consider the situation where I have all my schema names in one table and all my table names in another table. Is it possible to do something like in the following (pseudo-code)? SELECT value FROM (...
2
votes
2answers
95 views

Prevent T-SQL code that is not compatible to break the script

Here is an interesting challenge that I have not been able to crack... There is one nice T-SQL query that I Jonathan Kehayias created some time ago to find Implicit Conversions issues on the queries ...
0
votes
1answer
1k views

Use Dynamic SQL To Create View From Cursor

I want to iterate the table names that are listed in a table and create one view that has all of the info listed. I have the cursor set, I have the base of the view set, but how would I modify the ...
1
vote
0answers
72 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 ---...
2
votes
4answers
982 views

how can I identify ALL triggers that call a specific stored procedure?

I am having an issue that I need to move some stored procedures to a different database. Those SPs are run from the same and other databases as well. Specially they are run from inside triggers. In ...
2
votes
2answers
2k views

how to sucessfully revoke view any definition (or any other server permissions)?

I had to grant a server permissions to an active directory group, in order to solve a LIVE Bug: GRANT VIEW ANY DEFINITION TO [mycompany\webDevSeniors] GO after the permission was no longer needed I ...
4
votes
1answer
2k views

Pass Parameters to trigger function to execute dynamic SQL

I have this code which was solved by Erwin: Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function Now I want the trigger to call the trigger function insert_data_func with parameters (...
1
vote
0answers
488 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 ...