MS SQL Server

Can you delete data in a view? The answer is no.

But if the question is "Can you delete data from the underlying table through view?" The answer is then yes. SQL Server will allow you to delete data from the underlying table through a view. The tutorial exercise below is a good example:

DELETE FROM ggl_links_top WHERE id = 100001;

GO

SELECT * FROM ggl_links_top;

GO

36470 999966 dgqnvmy pyjqd toqcoupuxortasdtzvcae jonfb

12292 999953 qebmw v qqmywe q kza wskxqns jnb

6192 999943 p o qisvrakk hk od

SELECT TOP 1 * FROM ggl_links ORDER BY counts DESC;

GO

id  url        ...

------ ------------------------------------------- ...

36470 dgqnvmy pyjqd toqcoupuxortasdtzvcae jonfb ...

By default, column names in a view are provided by the underlying SELECT statement.

But sometimes, the underlying SELECT statement can not provide names for output columns that specified as expressions with functions and operations. In this case, you need to assign new names for the view's columns. The tutorial exercise below creates a view to merge several table columns into a single view column with a format called CSV (Comma Separated Values):

CREATE VIEW ggl_links_dump AS

SELECT CONVERT(VARCHAR(20),id)

+ ', ' + CONVERT(VARCHAR(20),counts)

+ ', ''' + url + ''''

FROM ggl_links WHERE counts > 1000

GO

Msg 4511, Level 16, State 1, Procedure ggl_links_dump,

Line 2

Create View or Function failed because no column name

was specified for column 1.

When you define a view, its columns are defined through a list of expressions in the underlying SELECT statement. Their data types will be determined implicitly by the expressions.

For example, if the column expression is a column name of a underlying table, the data type of the view column will be the same of the underlying table column.

If the column expression is a function, the data type of the view column will be the function return data type.

If the column expression is an operation, the data type of the view column will be the expression result data type.

The following tutorial exercise shows you some examples of view column data types:

DROP VIEW ggl_links_view;

GO

CREATE VIEW ggl_links_view (ID, DateString, CountUrl) AS

SELECT id, CONVERT(VARCHAR(16), created, 107),

CONVERT(VARCHAR(20),counts)+' - '+url

FROM ggl_links WHERE counts > 1000

GO

By default, views are not bound to the schema of the underlying tables. This means that SQL Server will allow you to change underlying table's schema any time. For example, you can drop the underlying table while keep the view. Of course, this will make the view not valid any more.

If you don't want anyone to change underlying table's schema once a view has been defined, you can create a binding view with the "WITH SCHEMABINDING" clause to bind the view to the schema of underlying tables. A binding view has the following features:

* Changing of underlying table's schema is not allowed as long as there exists one binding view.

* Indexes can be created only on binding views.

The tutorial exercise below shows you how to create a binding with "WITH SCHEMABINDING":

DROP VIEW ggl_links_view;

GO

CREATE VIEW ggl_links_view (ID, DateString, CountUrl)

WITH SCHEMABINDING

AS SELECT id, CONVERT(VARCHAR(16), created, 107),

CONVERT(VARCHAR(20),counts)+' - '+url

FROM ggl_links_copy WHERE counts > 1000;

GO

Msg 4512, Level 16, State 3, Procedure ggl_links_view,

Line 3

Cannot schema bind view 'ggl_links_view' because name

'ggl_links_copy' is invalid for schema binding.

Names must be in two-part format and an object

canno

If you want execute a stored procedure created previously, you can use the EXECUTE statement in the following formats:

EXEC procedure_name;

EXECUTE procedure_name;

The key word EXEC is actually optional. So you can execute a stored procedure by just entering the procedure name as the statement. See examples in the following tutorial exercise:

USE GlobalGuideLineDatabase;

GO

-- create a quick procedure

CREATE PROCEDURE date AS

PRINT CONVERT(VARCHAR(20),GETDATE(),107);

GO

-- execute with EXEC

EXEC date;

GO

May 19, 2007

-- execute with EXEC

date;

GO

May 19, 2007

-- using a reserved keyword as procedure name

CREATE PROCEDURE datetime AS PRINT GETDATE();

GO

datetime;

GO

May 19, 2007 11:35PM

Looks like SQL Server allows you to reserved keywords as stored procedure names

If you want to create a simple stored procedure with no input and output parameters, you can use the "CREATE PROCEDURE" command with a statement batch in a simple format as shown in below:

CREATE PROCEDURE procedure_name AS

statement_1;

statement_2;

...

statement_n;

GO

The following tutorial exercise shows you how to create a simple stored procedure:

USE GlobalGuideLineDatabase;

GO

CREATE PROCEDURE Hello AS

SELECT 'Welcome to:';

SELECT ' GlobalGuideLine.com';

GO

Command(s) completed successfully.

EXEC Hello;

GO

-----------

Welcome to;

(1 row(s) affected)

----------------

GlobalGuideLine.com

(1 row(s) affected)

If you need to search and sort data in a view with a large number of row, you may want to create an index on the view to speed up your search process.

The tutorial exercise below shows you how to create a unique clustered index on a view.

DROP VIEW ggl_links_view;

GO

CREATE VIEW ggl_links_view (ID, UrlReversed)

AS SELECT id, REVERSE(url)

FROM ggl_links_copy WHERE counts > 1000;

GO

CREATE UNIQUE CLUSTERED INDEX date_string

ON ggl_links_view (ID);

GO

Cannot create index on view 'ggl_links_view'

because the view is not schema bound.

ALTER VIEW ggl_links_view (ID, UrlReversed)

WITH SCHEMABINDING

AS SELECT id, REVERSE(url)

FROM dbo.ggl_links_copy WHERE counts > 1000;

GO

CREATE UNIQUE CLUSTERED INDEX date_string

ON ggl_links_view (ID);

GO

EXEC SP_HELP ggl_links_view;

GO

index_name index_description    index_keys

----------- ------------------------------------ ----------

date_string clustered, unique located on PRIMARY ID

If you have an existing procedure that you don't want to use it anymore, you should delete it from the SQL Server by using the "DROP PROCEDURE" statement as shown in the tutorial example below:

USE GlobalGuideLineDatabase;

GO

DROP PROCEDURE datetime;

GO

Command(s) completed successfully.

SELECT * FROM sys.procedures;

GO

Name  object_id schema_id type type_desc   

---------- ----------- ----------- ---- --------------------

Hello 1621580815 1   P SQL_STORED_PROCEDURE

date  1653580929 1   P SQL_STORED_PROCEDURE

(2 row(s) affected)

Stored procedure "datetime" is no longer in the database.

If you are creating a stored procedure with multiple statements, it's better to use "BEGIN ... END" to group all statements into a single statement block.

The tutorial exercise below shows you some good examples:

USE GlobalGuideLineDatabase;

GO

CREATE PROCEDURE Show AS BEGIN

SELECT name, type_desc FROM sys.tables;

SELECT name, type_desc FROM sys.views;

SELECT name, type_desc FROM sys.procedures;

END;

GO

Command(s) completed successfully.

EXEC Show;

GO

name type_desc

------------------- ---------------------

ggl_random USER_TABLE

ggl_links_indexed USER_TABLE

ggl_links USER_TABLE

ggl_links_copy USER_TABLE

name type_desc

------------------- ---------------------

ggl_links_top VIEW

ggl_links_dump VIEW

ggl_links_view VIEW

name type_desc

------------------- ---------------------

Hello SQL_STORED_PROCEDURE

date SQL_STORED_PROCEDURE

Show SQL_STORED_PROCEDURE

Where the end of the "CREATE PROCEDURE" statement structure? The answer is simple, the end of the statement batch.

Even if you are using a "BEGIN ... END" statement block, the stored procedure structure is not going to end at the end of the statement block. It will continue to the end of the statement batch, usually the GO command. The tutorial exercise gives you a good example:

USE GlobalGuideLineDatabase;

GO

DROP PROCEDURE ShowFaq;

DROP TABLE Faq;

GO

-- How this statement batch will be executed?

CREATE PROCEDURE ShowFaq AS BEGIN

PRINT 'Number of questions:';

SELECT COUNT(*) FROM Faq;

PRINT 'First 5 questions:'

SELECT TOP 5 * FROM Faq;

END;

CREATE TABLE Faq (Question VARCHAR(80));

GO

EXEC ShowFaq;

GO

Number of questions:

Msg 208, Level 16, State 1, Procedure ShowFaq, Line 3

Invalid object name 'Faq'.

What happened here was that the "CREATE TABLE" statement was not execueted. It was included as part of the stored procedure "ShowFaq". This is why you were getting the error "Invalid object name 'Faq'."

If you want to know how an existing stored procedure was created, you can use SQL Server Management Studio to automatically generate a "CREATE PROCEDURE" script The following tutorial shows you how to do this:

1. Run SQL Server Management Studio and connect to SQL server.

2. On the Object Explorer window, follow the object tree: Databases > GlobalGuideLineDatabase > Programmability > Stored Procedures > dbo.ShowFaq.

3. Click right mouse button on dbo.ShowFaq. The context menu shows up.

4. Select "Script Stored Procedure as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:

USE [GlobalGuideLineDatabase]

GO

/****** Object: StoredProcedure [dbo].[ShowFaq]

Script Date: 05/19/2007 21:31:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[ShowFaq] AS BEGIN

PRINT 'Number of questions:';

SELECT COUNT(*) FROM Faq;

PRINT 'First 5 questions:'

SELECT TOP 5 * FROM Faq;

END;

CREATE TABLE Faq (Question VARCHAR(80));

Very often, you need to create a stored procedure with one or more parameters. You only supply values to those parameters at the time of executing the stored procedure.

Stored procedures with parameters can be created with the following syntax:

CREATE PROCEDURE procedure_name

@parameter_1 datatype,

@parameter_2 datatype,

...

@parameter_n datatype

AS

statement_1;

statement_2;

...

statement_n;

GO

The following tutorial exercise shows you how to create a stored procedure with one parameter called @url:

USE GlobalGuideLineDatabase;

GO

DROP PROCEDURE Hello;

GO

CREATE PROCEDURE Hello

@url nvarchar(40)

AS

PRINT 'Welcome to ' + @url;

GO

EXEC Hello 'rendc.org';

GO

Welcome to rendc.org

If a stored procedure is created with parameters, you need pass values to those parameters when calling the stored procedure with one of two formats listed below:

-- Passing values only

EXEC procedure_name value_1, value_2, ... value_n;

-- Passing name-value pairs

EXEC procedure_name

@parameter_1 = value_1,

@parameter_2 = value_2,

...

@parameter_n = value_n;

The tutorial exercise below shows 2 ways to pass values to stored procedure parameters:

DROP PROCEDURE Hello;

GO

CREATE PROCEDURE Hello

@url nvarchar(40)

AS

PRINT 'Welcome to ' + @url;

GO

EXEC Hello 'rendc.org';

GO

Welcome to rendc.org

EXEC Hello @url='rendc.org';

GO

Welcome to rendc.org

If you add a parameter when creating a stored procedure, you can provide a default value so that the execution statement is not required to pass input value to this parameter.

To provide a default value to a parameter, you should use this format: "@parameter_name data_type = default_value". The tutorial exercise below shows you how provide default values to stored procedure parameters:

DROP PROCEDURE diff_in_days;

GO

CREATE PROCEDURE diff_in_days

@start_date DATETIME,

@end_date DATETIME = '19-May-2007'

AS BEGIN

PRINT CONVERT(VARCHAR(20),@end_date,107)

+ ' - '

+ CONVERT(VARCHAR(20),@start_date,107)

+ ' = '

+ STR(DATEDIFF(DAY, @start_date, @end_date));

END;

GO

-- Default value is used

EXEC diff_in_days

@start_date='01-Jan-2007';

GO

May 19, 2007 - Jan 01, 2007 = 138

-- Default value is not used

EXEC diff_in_days

@start_date='01-Jan-2007',

@end_date='11-May-2007';

GO

May 11, 2007 - Jan 01, 2007 = 130

-- Input value must be supplied for a parameter

-- without a default value

EXEC diff_in_days

@end_date='11-May-2007';

GO

Msg 201, Level 16, State 4, Procedure diff_in_days, Line 0

If you want get the definition of an existing stored procedure back from the SQL Server, you can use the system view called sys.sql_modules, which stores definitions of views and stored procedures.

The sys.sql_modules holds stored procedure definitions identifiable by the object id of each view. The tutorial exercise below shows you how to retrieve the definition of stored procedure, "ShowFaq" by joining sys.sql_modules and sys.procedures:

USE GlobalGuideLineDatabase;

GO

SELECT m.definition

FROM sys.sql_modules m, sys.procedures p

WHERE m.object_id = p.object_id

AND p.name = 'ShowFaq';

GO

definition

-----------------------------------------

CREATE PROCEDURE ShowFaq AS BEGIN

PRINT 'Number of questions:';

SELECT COUNT(*) FROM Faq;

PRINT 'First 5 questions:'

SELECT TOP 5 * FROM Faq;

END;

CREATE TABLE Faq (Question VARCHAR(80));

(1 row(s) affected)

When calling a stored procedure defined with parameters, you can pass values to those parameters in two ways:

* Passing only values in the same order as parameters defined in the stored procedure.

* Passing name-value pairs in any order.

The advantages of passing name-value pairs to stored procedure parameters are:

* Makes the calling statement more readable - You know which value is passed to which parameter.

* Makes it possible to pass values in an order different than how parameters are defined.

The tutorial exercise shows you some good examples of passing name-value pairs as parameters:

CREATE PROCEDURE diff_in_days

@start_date DATETIME,

@end_date DATETIME

AS BEGIN

PRINT CONVERT(VARCHAR(20),@end_date,107)

+ ' - '

+ CONVERT(VARCHAR(20),@start_date,107)

+ ' = '

+ STR(DATEDIFF(DAY, @start_date, @end_date));

END;

GO

EXEC diff_in_days

'01-Jan-2007',

'19-May-2007';

GO

May 19, 2007 - Jan 01, 2007 = 138

EXEC diff_in_days

@start_date='01-Jan-2007',

@end_date='19-May-2007';

GO

May 19, 2007 - Jan 01, 2007 = 138

-- Name-value pairs can be given in any order

EXEC diff_in_days

Can you pass expressions to stored procedure parameters? The answer is no.

When executing stored procedures, all input values must be entered as data literals, which can be specified within single quotes ('), or without them if they cause no confusion. The tutorial exercise below shows you how input values should be specified:

CREATE PROCEDURE area_of_circle @radius REAL

AS BEGIN

PRINT 'Radius = ' + STR(@radius,9,3);

PRINT 'Area = ' + STR(3.14*@radius*@radius,9,3);

END;

GO

-- Input value without quotes

EXEC area_of_circle 1.5;

GO

Radius = 1.500

Area = 7.065

-- Input value with quotes

EXEC area_of_circle '1.5';

GO

Radius = 1.500

Area = 7.065

-- Expressions are not allowed

EXEC area_of_circle 1.0+0.5;

GO

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '+'.

If you find a mistake in an existing stored procedure previously created, you can drop (delete) it and create it again correctly. But dropping a stored procedure may affect other database objects who are depending on this stored procedure.

So the best way to correct a mistake in an existing stored procedure is to use the "ALTER PROCEDURE" statement as shown in the following tutorial example:

USE GlobalGuideLineDatabase;

GO

-- Finding a mistake - the last line is wrong

SELECT m.definition

FROM sys.sql_modules m, sys.procedures p

WHERE m.object_id = p.object_id

AND p.name = 'ShowFaq';

GO

definition

-----------------------------------------

CREATE PROCEDURE ShowFaq AS BEGIN

PRINT 'Number of questions:';

SELECT COUNT(*) FROM Faq;

PRINT 'First 5 questions:'

SELECT TOP 5 * FROM Faq;

END;

CREATE TABLE Faq (Question VARCHAR(80));

(1 row(s) affected)

-- Modifying the stored procedure

ALTER PROCEDURE ShowFaq AS BEGIN

PRINT 'Number of questions:';

SELECT COUNT(*) FROM Faq;

PRINT 'First 5 questions:'

SELECT TOP 5 * FROM Faq;

END;

GO

Command(s) completed successfully.

If an output parameter is defined in a stored procedure, the execution statement must provide a variable to receive the output value in the format: "@variable_name OUTPUT" or "@parameter_name = @variable_name OUTPUT". The following tutorial exercise gives you a good example:

-- Using @variable format

DECLARE @message VARCHAR(40);

EXECUTE diff_in_days

'01-Jan-2007',

'11-May-2007',

@message OUTPUT;

PRINT @message;

GO

May 11, 2007 - Jan 01, 2007 = 130

-- Using @parameter = @variable format

DECLARE @message VARCHAR(40);

EXEC diff_in_days

@start_date='01-Jan-2007',

@end_date='11-May-2007',

@days = @message OUTPUT;

PRINT @message;

GO

May 11, 2007 - Jan 01, 2007 = 130