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
22
votes
4answers
77k 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 ...
19
votes
3answers
2k views

Alternatives to concatenating strings or going procedural to prevent SQL query code repetition?

Disclaimer: Please bear with me as someone who only uses databases a tiny fraction of his work time. (Most of the time I do C++ programming in my job, but every odd month I need to search/fix/add ...
17
votes
2answers
3k views

Why does SQL Injection not happen on this query inside a stored procedure?

I made the following stored procedure: ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100)) AS SELECT ActorDOB, ActorName FROM tblActor WHERE ActorName LIKE '%' ...
16
votes
2answers
14k views

Create View in specified database with dynamic sql?

I am writting a dynamic sql to drop and create view in different database. So I wrote: set @CreateViewStatement = ' USE ['+ @DB +']; CREATE VIEW [dbo]...
15
votes
4answers
4k views

How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values. CREATE TEMP TABLE foo AS SELECT x::text AS name, x::int ...
15
votes
1answer
1k views

User-shared queries: Dynamic SQL vs. SQLCMD

I have to refactor and document a number of foo.sql queries which will be shared by a team of DB tech support (for customer configurations and things like that). There are types of tickets that come ...
10
votes
2answers
2k views

Can sp_executesql be configured / used by default?

I am looking at an application that uses highly dynamic sql queries against SQL Server. Looking at the queries that are constructed in very weird and complicated ways, but that's a different story, I ...
10
votes
4answers
10k 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 ...
10
votes
2answers
7k views

How to Dynamically change the database using TSQL

I'm having trouble with trying to dynamically change the context of SSMS to the database specified in dynamic SQL: EXEC sys.sp_executesql N'USE db1 ' ; It executes successfully however the database ...
10
votes
1answer
31k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
9
votes
2answers
72k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...
9
votes
1answer
11k views

sp_execute expects parameter '@handle' of type 'int'

I'm trying to check in a stored procedure if a table exists in my destination database. If it does not then I will create the table useing the information_schema tables from the source database. ...
9
votes
1answer
5k views

sp_prepexec (sp_execute) vs. sp_executeSQL

The meat of the question: are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of ...
8
votes
4answers
11k views

Switching between databases with dynamic SQL

I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database. ...
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 ...
8
votes
2answers
9k 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 (...
8
votes
2answers
11k views

Select permission inside stored procedure?

I have granted a user execute permission for a stored procedure that uses dynamic SQL. But when he tries to execute it, he gets the error: The SELECT permission was denied on the object '[table ...
8
votes
1answer
5k views

Print Parameters in Dynamic SQL

I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement. EG: Declare @SQL nvarchar(max), @Params ...
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 ...
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) | +===========+=========+=...
7
votes
1answer
10k 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`...
7
votes
2answers
13k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
6
votes
1answer
18k views

How to use function parameters in dynamic SQL with EXECUTE?

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
6
votes
4answers
4k views

Copy indexes from one table to another

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
6
votes
3answers
4k 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 ...
6
votes
2answers
10k views

Query across multiple schemas, unknown until execution time

I have multiple schemas that I need to query across. The schemas are not known in advance, for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
6
votes
3answers
3k views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is not ...
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 ...
6
votes
2answers
37k 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 ...
6
votes
3answers
10k 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 ...
6
votes
1answer
3k views

Performance issue with sp_executesql and VARCHAR parameter

Table Segments has an index by DEPARTMENT (VARCHAR(10)) and BDPID(VARCHAR(10)). Execution time of the first query is 34 seconds SELECT TOP 10 c.BDPID, seg.FINAL_SEGMENT FROM Customers c LEFT JOIN ...
6
votes
1answer
2k views

sp_ExecuteSQL, performance and table variables

Is anyone able to explain some behaviour I'm seeing with SQL Profiler? Taking the following two batches: exec sp_executesql N' declare @t table ( x int ) insert into @t (x) ...
6
votes
1answer
10k views

Are there other ways to select a dynamic list of columns?

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that. 1. Using refcursors CREATE OR REPLACE FUNCTION selecttestwithcolumnlist( ticker ...
6
votes
1answer
4k 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(...
5
votes
3answers
13k 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 ...
5
votes
2answers
66k views

How to include a datetime parameter within a stored procedure along with string query?

My stored procedure is as follows, -- Add the parameters for the stored procedure here @FromDate datetime, @ToDate datetime --Select query DECLARE @query nvarchar(max) ...
5
votes
2answers
7k views

How to drop all of my functions in PostgreSQL?

Right now I have to use query to get the command in text file. Then remove double quote from them. And finally, run that file in psql shell. How can drop all of my functions in PostgreSQL in a single ...
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 ...
5
votes
1answer
4k views

Trigger function taking column names as parameters to modify the row

I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet): CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) RETURNS trigger AS $...
5
votes
1answer
376 views

Is it good practice to create tables dynamically?

Let's say that I'm making an IRC bot, and in addition to all the other stuff that should be stored in the DB, I want to log all the messages in each channel in which the bot is present. I already ...
5
votes
2answers
542 views

The database mail configuration saved into a temp table

I am currently dealing with a bunch of servers and they have different Database Mail Configurations. I can see the Database Mail configuration for each of those servers. I use the following script: ...
5
votes
4answers
5k views

Does it make sense to have CASE .. END in an ORDER BY?

Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice? It is common to use parameters ...
5
votes
1answer
9k views

The metadata could not be determined because statement invokes an extended stored procedure

in sql server 2012 I used to have a look at the jobs by getting the output of SP_HELP_JOB -- https://www.sqlservercentral.com/Forums/Topic259078-8-1.aspx -- getting data from sp_help_job into a temp ...
5
votes
2answers
9k views

Add function parameter to SQL query WHERE clause

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function: CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS $$ ...
5
votes
1answer
5k views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
5
votes
1answer
2k views

Passing ROWTYPE parameter to EXECUTE

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the ...
5
votes
2answers
13k views

Check whether empty strings are present in character-type columns

I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to ...
5
votes
1answer
3k views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
5
votes
1answer
671 views

Dynamic casting from text within plpgsql

Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query ...
4
votes
2answers
659 views

Does adherence to the DRY principle justify dynamic SQL?

Suppose I have two queries that will be frequently run against my database: SELECT UserID, UserName, UserGender FROM Users WHERE UserID = @User SELECT UserID, UserName, UserGender FROM ...