Dynamic SQL is a technique using SQL (Structured Query Language) whose main difference from traditional SQL is that Dynamic SQL allows to build SQL statements dynamically at runtime, which eases the automatic generation and execution of program statements.
Questions tagged [dynamic-sql]
2681 questions
314
votes
21 answers
Why would someone use WHERE 1=1 AND in a SQL clause?
Why would someone use WHERE 1=1 AND in a SQL clause (Either SQL obtained through concatenated strings, either view definition)
I've seen somewhere that this would be used to protect against SQL Injection, but it seems very weird.
If…
Bogdan Maxim
- 5,866
- 3
- 23
- 34
216
votes
14 answers
Truncating all tables in a Postgres database
I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL?
At the moment I've managed to come up with a SQL statement that returns all the commands I need to execute:
SELECT 'TRUNCATE…
Sig
- 4,988
- 3
- 28
- 29
208
votes
12 answers
Create PostgreSQL ROLE (user) if it doesn't exist
How do I write an SQL script to create a ROLE in PostgreSQL 9.1, but without raising an error if it already exists?
The current script simply has:
CREATE ROLE my_user LOGIN PASSWORD 'my_password';
This fails if the user already exists. I'd like…
EMP
- 59,148
- 53
- 164
- 220
207
votes
11 answers
How to get sp_executesql result into a variable?
I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable.
I know I can use sp_executesql but can't find clear examples around about how to do this.
JohnIdol
- 48,899
- 61
- 158
- 242
182
votes
7 answers
SQL update fields of one table from fields of another one
I have two tables:
A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]
A will always be subset of B (meaning all columns of A are also in B).
I want to update a record with a specific ID in B with their data from A for all…
Nir
- 2,051
- 2
- 14
- 6
178
votes
18 answers
Drop all tables whose names begin with a certain string
How can I drop all tables whose names begin with a given string?
I think this can be done with some dynamic SQL and the INFORMATION_SCHEMA tables.
Blorgbeard
- 101,031
- 48
- 228
- 272
140
votes
6 answers
Getting result of dynamic SQL into a variable for sql-server
Executing dynamic SQL as follows in Stored Procedure:
DECLARE @sqlCommand nvarchar(1000)
DECLARE @city varchar(75)
SET @city = 'London'
SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city…
Peter Lindholm
- 2,420
- 5
- 23
- 27
112
votes
8 answers
Table name as a PostgreSQL function parameter
I want to pass a table name as a parameter in a Postgres function. I tried this code:
CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer
AS $$
BEGIN
IF EXISTS (select * from quote_ident($1) where…
John Doe
- 9,414
- 13
- 50
- 69
100
votes
4 answers
Declare Variable for a Query String
I was wondering if there was a way to do this in MS SQL Server 2005:
DECLARE @theDate varchar(60)
SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59'''
SELECT AdministratorCode,
SUM(Total) as theTotal,
…
StealthRT
- 10,108
- 40
- 183
- 342
96
votes
3 answers
Why do I get "Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'." when I try to use sp_executesql?
Why do I get this error
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
when I try to use sp_executesql?
Manoj Wadhwani
- 1,487
- 4
- 20
- 23
84
votes
16 answers
In SQL Server, how do I generate a CREATE TABLE statement for a given table?
I've spent a good amount of time coming up with solution to this problem, so in the spirit of this post, I'm posting it here, since I think it might be useful to others.
If anyone has a better script, or anything to add, please post it.
Edit: Yes…
Blorgbeard
- 101,031
- 48
- 228
- 272
76
votes
8 answers
How to use table variable in a dynamic sql statement?
In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that procedure. I am using Sql Server 2008.
This…
Ashar Syed
- 1,236
- 6
- 16
- 28
70
votes
10 answers
nvarchar(max) still being truncated
So I'm writing a stored procedure in MS SQL Server 2008. It's a really long query and I have to write it dynamically, so I create a variable called @Query and make it of type NVARCHAR(MAX). Now, I have been told that in modern versions of SQL…
Andrew
- 4,953
- 15
- 40
- 58
60
votes
7 answers
Using a cursor with dynamic SQL in a stored procedure
I have a dynamic SQL statement I've created in a stored procedure. I need to iterate over the results using a cursor. I'm having a hard time figuring out the right syntax. Here's what I'm doing.
SELECT @SQLStatement = 'SELECT userId FROM…
Micah
- 111,873
- 86
- 233
- 325
59
votes
7 answers
DROP FUNCTION without knowing the number/type of parameters?
I keep all my functions in a text file with 'CREATE OR REPLACE FUNCTION somefunction'.
So if I add or change some function I just feed the file to psql.
Now if I add or remove parameters to an existing function, it creates an overload with the same…
Steinthor.palsson
- 6,286
- 13
- 44
- 51