The curse and blessings of dynamic SQL

14 Jul 17

alwyss w

sql dynamic injection

18 Jun 17

kevino

04 Oct 16

Hrvoje -

sql-server dynamic-sql article views partitioned-views

SELECT * FROM @tablename
when it comes to building a query plan, each table has its set of statistics and presumptions that are by no means interchangeable
It is much better to write ten or twenty stored procedures, even if they are similar to each other.
SELECT * should not be used in production code
pre-processor
SELECT * FROM sales + @yymm
name includes some partitioning component, typically year and sometimes also month
there is a suite of tables that actually do describe the same entity
Views and Partitioned Views
CREATE VIEW sales AS
year = '2006'
FROM dbo.sales2006
FROM dbo.sales2005
year = '2005'
Instead of composing the table name dynamically, you can now say
SELECT ... FROM sales WHERE year = '2006'
this view is not terribly efficient, as the query will access all tables in the view
partitioned view
A true partitioned view can be very efficient
for queries that include the partitioning column in the WHERE clause, SQL Server will only access the relevant table(s)
updatable
you can insert data into it, and the data will end up in the right table
Assume that as legacy of a poor design we have these three tables
year(OrderDate) = 1996
Orders96
year(OrderDate) = 1997
Orders97
Orders98
year(OrderDate) = 1998
and a CHECK constraint
These columns need a default
add Year column to each table
ALTER TABLE Orders96 ADD Year
DEFAULT '1996'
CHECK (Year = '1996')
This column must be the first column in the primary key
ALTER TABLE Orders96 ADD CONSTRAINT pk96 PRIMARY KEY (Year, OrderID)
Finally, you can create the view
CREATE VIEW Orders

37 more annotations...

25 Jul 16

Rafael Rodriguez

The Curse and Blessings of Dynamic SQL

SQL Server Dynamic

01 Jul 16

John Curfman

sql mssql dynamic

17 Feb 16

jletdiigo

t-sql sqlserver dynamic queries

11 Jan 16

ongcy80

10 Feb 15

Yi Wang

dynamic sql

20 Jan 15

Daniel Ionescu

exec sp_executesql sommarskog sql-server people

01 Jan 15

Christian Speas

sql sqlserver dynamic

02 Oct 14

ikapoz

sql sqlserver dynamic programming tutorial

29 Jul 14

Engelbert Tejeda

gls*"sql" "dynamic" "database context"

computers-windows computers-dba computers-lang

27 Mar 14

mattcolliver

sql dynamic tutorial guide

26 Feb 14

FirstN@me L@stN@me

per-224 tsql

16 Jan 14

sleeper_sk

sql dynamic

13 Nov 13

D C

sql sqlserver dynamic tsql otimo amazing

07 Nov 13

Felikso Mountains

"If you follow the various newsgroups on Microsoft SQL Server, you often see people asking why they can't do:

SELECT * FROM @tablename
SELECT @colname FROM tbl
SELECT * FROM tbl WHERE x IN (@list)

For all three examples you can expect someone to answer Use dynamic SQL and give a quick example on how to do it. Unfortunately, for all three examples above, dynamic SQL is a poor solution. On the other hand, there are situations where dynamic SQL is the best or only way to go.

In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages. To set the scene, I start with a very quick overview on application architecture for data access. I then proceed to describe the feature dynamic SQL as such, with a quick introduction followed by the gory syntax details. Next, I continue with a discussion on SQL injection, a security issue that it is essential to have good understanding of when you work with dynamic SQL. This is followed by a section where I discuss why we use stored procedures, and how that is affected by the use of dynamic SQL. I carry on with a section on good practices and tips for writing dynamic SQL. I conclude by reviewing a number of situations where you could use dynamic SQL and whether it is a good or bad idea to do it.

The article covers all versions of SQL Server from SQL 6.5 to SQL 2008, with emphasis on SQL 2000 and later versions. "

sql sqlserver dynamic database t-sql tsql mssql

29 Aug 13

Angela Trapp

t-sql dynamic sqlserver

13 Jun 13

Jonathan Powell

dynamic SQL SQLServer work

30 Apr 13

dwainekamm

"CREATE PROCEDURE general_select1 @tblname sysname,
@key varchar(10) AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT col1, col2, col3 ' +
' FROM dbo.' + quotename(@tblname) +
' WHERE keycol = @key'
EXEC sp_executesql @sql, N'@key varchar(10)', @key
CREATE PROCEDURE general_select2 @tblname nvarchar(127),
@key varchar(10) AS
EXEC('SELECT col1, col2, col3
FROM ' + @tblname + '
WHERE keycol = ''' + @key + '''')"

13 Mar 13

Carlos Herrero

sql programacion trabajo

22 Feb 13

Malcolm Herring

From Internet Explorer Developer Info SQL Server

19 Feb 13

Darrell Denlinger

sql

14 Feb 13

Mike Bliv

sql sqlserver dynamic programming mssql t-sql database

16 Jan 13

developerdan

sql t-sql dynamic

29 Jun 12

Cesar Vega

18 Jun 12

aleksej k

sql dynamic

21 May 12

jkendzior

Build the entire SQL string with parameter values expanded.

27 Apr 12

Anu radha

SQLserver DynamicSQL

12 Apr 12

dellwood9212

sql sqlserver

03 Apr 12

Richard Whitney

10 Jan 12

Claudio Gonzalez Vera

sql server Dynamic Sql

13 Dec 11

andyp81

01 Nov 11

kolan29

"Partitioned Table"

SQL BestPractices статьи сохранить

05 Aug 11

Stefan Aigner

sql sqlserver dynamic programming mssql database t-sql Server

11 Jul 11

David Caughill

SQLServer SQLSecurity DynamicSQL

28 Jun 11

benjamintn

SqlServer programming

22 Jan 11

Ian Campbell

sql server guide dynamic-sql

01 Jan 11

robert matthew cook

29 Nov 10

Humprey Cogay

SQL Server T-SQL Dynamic Clauses

03 Nov 10

Edward Speer

sql

29 Oct 10

Craig Allen

sql reference

12 Oct 10

Johannes Nejsum

Windsurfer wifi antenna

sql

25 Aug 10

Adrian Hills

sql server

29 Apr 10

Rolle E

OUTPUT

tsql dynamic sql sqlserver database t-sql

07 Apr 10

alexanderk

The Curse and Blessings of Dynamic SQL

sqlserver t-sql

21 Feb 10

jcassain

t-sql tsql sql sqlserver

15 Dec 09

Just TheDBA

sql code

20 Aug 09

Ilia Ternovich

Dynamic SQL Database Microsoft SqlServer T-SQL Article Tutorial Help

29 Jul 09

Mark Watkins

In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages.

The article covers all versions of SQL Server from SQL 6.5 to SQL 2008, with emphasis on SQL 2000 and later versions.

sql dynamic reference mssql

24 Jul 09

stormwild

dynamic sql sqlserver programming t-sql database howto tips reference

15 Jul 09

Steven Moberg

T-SQL

06 Jul 09

Andres Cuervo Adame

sql database tutorial security mssql

15 Jun 09

sommarskole: in depth overview of dynamic SQL
/ TOC:
Accessing Data from an Application
sp_executesql
EXEC()
SQL Injection – a Serious Security Issue
Dynamic SQL and Stored Procedures
Good Coding Practices and Tips for Dynamic SQL

t-sql reference code

04 May 09

Richard Kanavati

sql mssql sqlserver

20 Apr 09

Sterling Heibeck

sql

19 Feb 09

jmarxu1fm

This work is one of the most complete works on Dynamic SQL. Exhaustive.

tsql DynamicSQL

12 Dec 08

Riccardo Pietrucci

complete guide to the use of dynamic SQL in stored procedures and to a minor extent from client languages

tutorial tsql windows sqlserver tips dynamic sql

02 Dec 08

Endy Kalu

CREATE PROCEDURE search_orders @custid nchar(5) = NULL, @shipname nvarchar(40) = NULL AS DECLARE @sql nvarchar(4000) SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' + ' FROM dbo.Orders WHERE 1 = 1 ' IF @custid IS NOT NULL SELECT @sql = @sql + ' AND CustomerID LIKE @custid ' IF @shipname IS NOT NULL SELECT @sql = @sql + ' AND ShipName LIKE @shipname ' EXEC sp_executesql @sql, N'@custid nchar(5), @shipname nvarchar(40)', @custid, @shipname

29 Oct 08

Bob Carr

sql security

06 Aug 08

Lee Anne Pedersen

SQL database dynamic programming tips reference SQL Server

27 Jul 08

Rodney Greenfield

sql dynamic for:thesifter for:mia783

21 Jul 08

Mr. DiGi

sql programming database mssql 2000 tips

. Do you see that I've prefixed all tables in the query with dbo? There is a very important reason for this. On SQL 2000, this is an absolute must for effecient use of the query-plan cache. If you leave out dbo from a single table, each user will get his own copy of the plan in the cache. This is because on SQL 2000, each user has a default schema which is equal to the username. So when user1 runs a query that goes "SELECT ... FROM Orders", SQL Server must first check if there is a table user1.Orders, before it looks for dbo.Orders. Since user1.Orders could appear on the scene at any time, SQL Server needs to have a separate plan for each user.
SELECT * FROM sales + @yymm

20 Jul 08

Sherif Abou Seda

sql database learning programming sqlserver

09 Jul 08

Vanessa Hurst

dynamic SQL dynamicSQL sqlserver t-sql work

17 Jun 08

anniepritt

sqlserver tsql cursor

17 Apr 08

Joel Coppin

sql sqlserver database db mssql

08 Apr 08

Dong Zhang

sqlserver

01 Apr 08

T M

sql database

27 Mar 08

Peter A Schott

programming sql

18 Mar 08

29 Jan 08

ronald fuller

sql from_delicious Data BI

In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages.
In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages.