This link has been bookmarked by 173 people . It was first bookmarked on 11 Aug 2006, by Joe Caropepe.
-
14 Jul 17
-
18 Jun 17
-
04 Oct 16
-
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
-
-
25 Jul 16
-
01 Jul 16
-
17 Feb 16
-
11 Jan 16
-
10 Feb 15
-
20 Jan 15
-
01 Jan 15
-
02 Oct 14
-
29 Jul 14
Engelbert Tejedagls*"sql" "dynamic" "database context"
-
27 Mar 14
-
26 Feb 14
-
16 Jan 14
-
13 Nov 13
-
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. " -
29 Aug 13
-
13 Jun 13
-
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
-
22 Feb 13
-
19 Feb 13
-
14 Feb 13
-
16 Jan 13
-
29 Jun 12
-
18 Jun 12
-
21 May 12
-
Build the entire SQL string with parameter values expanded.
-
-
27 Apr 12
-
12 Apr 12
-
03 Apr 12
-
10 Jan 12
-
13 Dec 11
-
01 Nov 11
-
05 Aug 11
-
11 Jul 11
-
28 Jun 11
-
22 Jan 11
-
01 Jan 11
-
29 Nov 10
-
03 Nov 10
-
29 Oct 10
-
12 Oct 10
-
25 Aug 10
-
29 Apr 10
-
07 Apr 10
-
21 Feb 10
-
15 Dec 09
-
20 Aug 09
-
29 Jul 09
Mark WatkinsIn 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. -
24 Jul 09
-
15 Jul 09
-
06 Jul 09
-
15 Jun 09
-
04 May 09
-
20 Apr 09
-
19 Feb 09
-
12 Dec 08
-
02 Dec 08
-
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
-
06 Aug 08
-
27 Jul 08
-
21 Jul 08
-
. 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
-
09 Jul 08
-
17 Jun 08
-
17 Apr 08
-
08 Apr 08
-
01 Apr 08
-
27 Mar 08
-
18 Mar 08
-
29 Jan 08
-
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.
-
-
26 Nov 07
-
11 Oct 07
-
07 Sep 07
-
14 Aug 07
-
30 May 07
-
16 May 07
-
20 Apr 07
-
Security
-
-
16 Jan 07
-
15 Dec 06
-
25 Nov 06
-
22 Nov 06
-
15 Nov 06
-
25 Oct 06
-
28 Aug 06
-
11 Aug 06
-
27 Jun 06
viniciusjlIn this article I will discuss the of use dynamic SQL in stored procedures and to a minor extent from client languages.
-
15 May 06
-
10 Mar 06
-
06 Aug 04
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.