Free SQL Server 2000 Examples????

Here you can get answer to all your sql server 200o problems. Passs your SQL Server exams?


How to create a Table in SQL Server 2000

We are going to work with a example Table called Country.


CREATE TABLE [dbo].[Countries] ( [ZoneID] [int] NULL , [Currency] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CountryName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CountryCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO

How to Add Date in the Country Table

Just copy a paste the following code, put it in notepad, and the go to import export data in SQL Server 2000 choose the table Country and the txt file. Do not forget to put ";" as separator for each row. You will have all the countries in your SQL SErver database in only minutes.

"AD","Andorra""AE","United Arab Emirates""AF","Afghanistan""AG","Antigua and Barbuda""AI","Anguilla""AL","Albania""AM","Armenia""AN","Netherlands Antilles""AO","Angola""AQ","Antarctica""AR","Argentina""AS","American Samoa""AT","Austria""AU","Australia""AW","Aruba""AZ","Azerbaijan""BA","Bosnia and Herzegovina""BB","Barbados""BD","Bangladesh""BE","Belgium""BF","Burkina Faso""BG","Bulgaria""BH","Bahrain""BI","Burundi""BJ","Benin""BM","Bermuda""BN",
"Brunei Darussalam""BO","Bolivia""BR","Brazil""BS","Bahamas""BT","Bhutan""BV","Bouvet Island""BW","Botswana""BY","Belarus""BZ","Belize""CA","Canada""CC","Cocos""CF",
"Central African Republic""CG","Congo""CH","Switzerland""CI","Ivory Coast""CK","Cook Islands""CL","Chile""CM","Cameroon""CN","China""CO","Colombia""CR","Costa Rica""CU","Cuba""CV","Cape Verde""CX","Christmas Island""CY","Cyprus""CZ","Czech Republic""DE","Germany""DJ","Djibouti""DK","Denmark""DM","Dominica""DO","Dominican Republic""DZ","Algeria""EC","Ecuador""EE","Estonia""EG","Egypt""EH","Western Sahara""ER","Eritrea""ES","Spain""ET","Ethiopia""FI","Finland""FJ","Fiji""FK","Falkland Islands""FM","Micronesia""FO","Faroe Islands""FR","France""GA","Gabon""GD","Grenada""GE","Georgia""GF","French Guiana""GH","Ghana""GI","Gibraltar""GL","Greenland""GM","Gambia""GN","Guinea""GP",
"Guadeloupe""GQ","Equatorial Guinea""GR","Greece""GS","S. Georgia and S. Sandwich Islands""GT","Guatemala""GU","Guam""GW","Guinea-Bissau""GY","Guyana""HK","Hong Kong""HM","Heard and McDonald Islands""HN","Honduras""HR","Croatia""HT","Haiti""HU","Hungary""ID","Indonesia""IE",
"Ireland""IL","Israel""IN","India""IO","British Indian Ocean Territory""IQ","Iraq""IR","Iran""IS","Iceland""IT","Italy""JM","Jamaica""JO","Jordan""JP",
"Japan""KE","Kenya""KG","Kyrgyzstan""KH","Cambodia""KI","Kiribati""KM","Comoros""KN",
"Saint Kitts and Nevis""KP","North Korea""KR","South Korea""KW","Kuwait""KY","Cayman Islands""KZ","Kazakhstan""LA","Laos""LB","Lebanon""LC","Saint Lucia""LI","Liechtenstein""LK","Sri Lanka""LR","Liberia""LS","Lesotho""LT","Lithuania""LU","Luxembourg""LV",
"Latvia""LY","Libya""MA","Morocco""MC","Monaco""MD","Moldova""MG","Madagascar""MH",
"Marshall Islands"
"MK","Macedonia""ML","Mali""MM","Myanmar""MN","Mongolia""MO","Macau""MP",
"Northern Mariana Islands""MQ","Martinique""MR","Mauritania""MS","Montserrat""MT","Malta""MU",
"Mauritius""MV","Maldives""MW","Malawi""MX","Mexico""MY","Malaysia""MZ","Mozambique""NA","Namibia""NC",
"New Caledonia""NE","Niger""NF","Norfolk Island""NG","Nigeria""NI","Nicaragua""NL","Netherlands""NO","Norway""NP","Nepal""NR","Nauru""NU",
"Niue""NZ","New Zealand""OM","Oman""PA","Panama""PE","Peru""PF","French Polynesia""PG","Papua New Guinea""PH","Philippines""PK","Pakistan""PL","Poland""PM","St. Pierre and Miquelon""PN","Pitcairn""PR","Puerto Rico""PT","Portugal""PW","Palau""PY","Paraguay""QA","Qatar""RE","Reunion""RO",
"Romania""RU","Russian Federation""RW","Rwanda""SA","Saudi Arabia""SB","Solomon Islands""SC","Seychelles""SD","Sudan""SE","Sweden""SG","Singapore""SH","St. Helena""SI","Slovenia""SJ","Svalbard and Jan Mayen Islands""SK","Slovakia""SL","Sierra Leone""SM","San Marino""SN","Senegal""SO","Somalia""SR","Suriname""ST","Sao Tome and Principe""SU","Soviet Union""SV","El Salvador""SY","Syria""SZ","Swaziland""TC","Turks and Caicos Islands""TD","Chad""TF","French Southern Territories""TG","Togo""TH","Thailand""TJ","Tajikistan""TK","Tokelau""TM","Turkmenistan"
"TN","Tunisia""TO","Tonga""TP","East Timor""TR","Turkey""TT","Trinidad and Tobago""TV","Tuvalu""TW","Taiwan""TZ","Tanzania""UA","Ukraine""UG","Uganda""UK",
"United Kingdom""UM","US Minor Outlying Islands""US","United States""UY","Uruguay""UZ","Uzbekistan""VC","Saint Vincent and The Grenadines""VE","Venezuela""VG","British Virgin Islands""VI","US Virgin Islands""VN","Viet Nam""VU","Vanuatu""WF","Wallis and Futuna Islands""WS","Samoa""YE","Yemen""YT","Mayotte""YU","Yugoslavia""ZA","South Africa""ZM","Zambia""ZR","Zaire""ZW","Zimbabwe"



How to Generate a Stored Procedure in SQL Server 2000

Create a Stored Procedure in SQL Server 2000 is really easy, this section will show you how to create a stored procedure for adding, selecting, updating and deleting a country.


SQL Server 2000 - Stored Procedure to Add a Country

CREATE PROCEDURE [AddCountries]( @ZoneID int = NULL, @Currency char(10) = NULL, @CountryName nvarchar(50) = NULL, @CountryCode nvarchar(10))ASBEGIN
SET NOCOUNT ON DECLARE @Err int
INSERT INTO [Countries] ( [ZoneID], [Currency], [CountryName], [CountryCode] ) VALUES ( @ZoneID, @Currency, @CountryName, @CountryCode )
SET @Err = @@Error
RETURN @ErrEND
GO


SQL Server 2000 - Stored Procedure to Update a Country

CREATE PROCEDURE [UpdateCountries]( @ZoneID int = NULL, @Currency char(10) = NULL, @CountryName nvarchar(50) = NULL, @CountryCode nvarchar(10))ASBEGIN
SET NOCOUNT ON DECLARE @Err int
UPDATE [Countries] SET [ZoneID] = @ZoneID, [Currency] = @Currency, [CountryName] = @CountryName WHERE [CountryCode] = @CountryCode
SET @Err = @@Error
RETURN @ErrEND
GO

SQL Server 2000 - Stored Procedure to Delete a Country

CREATE PROCEDURE [DeleteCountries]( @CountryCode nvarchar(10))ASBEGIN
SET NOCOUNT ON DECLARE @Err int
DELETE FROM [Countries] WHERE [CountryCode] = @CountryCode SET @Err = @@Error
RETURN @ErrEND
GO

SQL Server 2000 - Stored Procedure to Select a Country
CREATE PROCEDURE [GetCountries]( @CountryCode nvarchar(10))ASBEGIN SET NOCOUNT ON DECLARE @Err int
SELECT [ZoneID], [Currency], [CountryName], [CountryCode] FROM [Countries] WHERE ([CountryCode] = @CountryCode)
SET @Err = @@Error
RETURN @ErrEND
GO


SQL Server 2000 - Stored Procedure to Select all Countries
CREATE PROCEDURE [GetAllCountries]ASBEGIN
SET NOCOUNT ON DECLARE @Err int
SELECT [ZoneID], [Currency], [CountryName], [CountryCode] FROM [Countries]
SET @Err = @@Error
RETURN @ErrEND
GO