Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [dynamic-sql]

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

SQL has limited facilities for abstraction and it may be necessary to implement processing by constructing a SQL query at runtime. Normally the term is used when this is done within a stored procedure in the database itself.

While it gives effectively infinite flexibility, dynamic SQL has some drawbacks, including:

  • The query optimiser must re-calculate a query plan for the query; often systems can't cache query plans for dynamically generated SQL.

  • Constructing a query through string concatenation can lead to SQL injection vulnerabilities. Some systems allow queries of this sort to be executed with parameters but variadic parameters are a common application for dynamic SQL and this might not be possible.

432 questions
23
votes
4 answers

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 table name and returns a string that contains the…
Andrew Savinykh
  • 1,325
  • 5
  • 15
  • 28
21
votes
3 answers

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 context of SSMS doesn't change. I've tried a…
Mazhar
  • 823
  • 4
  • 11
  • 24
20
votes
4 answers

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 FROM generate_series(1,10) AS t(x); CREATE TEMP…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
20
votes
2 answers

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 '%' + @nameString + '%' AND ActorGender =…
Ravi
  • 677
  • 3
  • 10
  • 19
19
votes
3 answers

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 something in an Oracle database.) I have repeatedly…
Martin
  • 2,420
  • 4
  • 26
  • 35
19
votes
3 answers

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 in performance when using EXEC vs SP_EXECUTESQL.
Pரதீப்
  • 1,409
  • 5
  • 18
  • 37
18
votes
3 answers

Why would you want to avoid dynamic SQL in stored procedures?

I have heard it said you do not want to use Dynamic SQL. Can you give me some concrete or real-life examples? Personally, I code using it a few times in my database. I think it is OK because of its flexibility. My guess is about SQL injection or…
Richard Sayakanit
  • 947
  • 2
  • 8
  • 12
16
votes
2 answers

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].[MyTable] AS …
King Chan
  • 399
  • 2
  • 5
  • 11
15
votes
3 answers

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 | Daniel | Edwards | | 2 …
Joshua Burns
  • 625
  • 1
  • 7
  • 11
15
votes
1 answer

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 regularly where each customer have their own…
Phrancis
  • 1,320
  • 1
  • 7
  • 24
14
votes
2 answers

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 column names are stored in another table, that I can…
Hotchips
  • 295
  • 1
  • 2
  • 10
14
votes
3 answers

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 integer NOT NULL, bar varchar NOT…
Jmoney38
  • 1,175
  • 5
  • 13
  • 22
12
votes
1 answer

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. However when I use sp_execute to try and bring back…
Lumpy
  • 2,129
  • 9
  • 34
  • 43
12
votes
4 answers

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 step?
sarit
  • 295
  • 2
  • 3
  • 8
11
votes
2 answers

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 $func$ BEGIN NEW.target :=…
Ivan
  • 225
  • 2
  • 7
1
2 3
…
28 29