Generated SQL Stored Procedures

The following are sample stored prcoedures generated for the Customer table in the Northwind database:

INSERT stored procedure:

CREATE PROCEDURE dbo.usp_addCustomers(@pCustomerid AS NCHAR(5), @pCompanyname AS NVARCHAR(40), @pContactname AS NVARCHAR(30), @pContacttitle AS NVARCHAR(30), @pAddress AS NVARCHAR(60), @pCity AS NVARCHAR(15), @pRegion AS NVARCHAR(15), @pPostalcode AS NVARCHAR(10), @pCountry AS NVARCHAR(15), @pPhone AS NVARCHAR(24), @pFax AS NVARCHAR(24))
AS

	SET NOCOUNT ON

	INSERT INTO dbo.Customers ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])
	     VALUES (@pCustomerid, @pCompanyname, @pContactname, @pContacttitle, @pAddress, @pCity, @pRegion, @pPostalcode, @pCountry, @pPhone, @pFax)

	RETURN @@Error

GO		
			

UPDATE stored procedure:

CREATE PROCEDURE dbo.usp_updateCustomers(@pCustomerid AS NCHAR(5), @pCompanyname AS NVARCHAR(40), @pContactname AS NVARCHAR(30), @pContacttitle AS NVARCHAR(30), @pAddress AS NVARCHAR(60), @pCity AS NVARCHAR(15), @pRegion AS NVARCHAR(15), @pPostalcode AS NVARCHAR(10), @pCountry AS NVARCHAR(15), @pPhone AS NVARCHAR(24), @pFax AS NVARCHAR(24))
AS

	SET NOCOUNT ON

	UPDATE dbo.Customers
	     SET [CompanyName] = @pCompanyname, 
	            [ContactName] = @pContactname, 
	            [ContactTitle] = @pContacttitle, 
	            [Address] = @pAddress, 
	            [City] = @pCity, 
	            [Region] = @pRegion, 
	            [PostalCode] = @pPostalcode, 
	            [Country] = @pCountry, 
	            [Phone] = @pPhone, 
	            [Fax] = @pFax
	   WHERE [CustomerID] = @pCustomerid

	RETURN @@Error

GO		
		

DELETE stored procedure:

CREATE PROCEDURE dbo.usp_deleteCustomers(@pCustomerid AS NCHAR(5))
AS

	SET NOCOUNT ON

	DELETE FROM dbo.Customers
	      WHERE [CustomerID] = @pCustomerid

	RETURN @@Error

GO		
		

SELECT stored procedures:

CREATE PROCEDURE dbo.usp_getCustomers(@pCustomerid AS NCHAR(5))
AS

	SET NOCOUNT ON

	SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]
	  FROM dbo.Customers
	ORDER BY [CustomerID] ASC
	 WHERE [CustomerID] = @pCustomerid

	RETURN @@Error

GO

CREATE PROCEDURE dbo.usp_getCustomerss
AS

	SET NOCOUNT ON

	SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]
	  FROM dbo.Customers
	ORDER BY [CustomerID] ASC

	RETURN @@Error

GO	
		

SELECT...FOR XML stored procedures:

CREATE PROCEDURE dbo.usp_getCustomersXML(@pCustomerid AS NCHAR(5))
AS

	SET NOCOUNT ON

	SELECT 1 AS Tag, NULL AS Parent, '' AS [Customerss!1!], 
	       [CustomerID] AS [Customers!1!customerid], 
	       [CompanyName] AS [Customers!1!companyname], 
	       [ContactName] AS [Customers!1!contactname], 
	       [ContactTitle] AS [Customers!1!contacttitle], 
	       [Address] AS [Customers!1!address], 
	       [City] AS [Customers!1!city], 
	       [Region] AS [Customers!1!region], 
	       [PostalCode] AS [Customers!1!postalcode], 
	       [Country] AS [Customers!1!country], 
	       [Phone] AS [Customers!1!phone], 
	       [Fax] AS [Customers!1!fax]
	  FROM dbo.Customers
	 WHERE [CustomerID] = @pCustomerid
	FOR XML EXPLICIT

	RETURN @@Error

GO

CREATE PROCEDURE dbo.usp_getCustomerssXML
AS

	SET NOCOUNT ON

	SELECT 1 AS Tag, NULL AS Parent, '' AS [Customerss!1!], 
	       NULL AS [Customers!2!customerid], 
	       NULL AS [Customers!2!companyname], 
	       NULL AS [Customers!2!contactname], 
	       NULL AS [Customers!2!contacttitle], 
	       NULL AS [Customers!2!address], 
	       NULL AS [Customers!2!city], 
	       NULL AS [Customers!2!region], 
	       NULL AS [Customers!2!postalcode], 
	       NULL AS [Customers!2!country], 
	       NULL AS [Customers!2!phone], 
	       NULL AS [Customers!2!fax]

	UNION ALL

	SELECT 2 AS Tag, 1 AS Parent, '' AS [Customerss!1!], 
	       [CustomerID] AS [Customers!2!customerid], 
	       [CompanyName] AS [Customers!2!companyname], 
	       [ContactName] AS [Customers!2!contactname], 
	       [ContactTitle] AS [Customers!2!contacttitle], 
	       [Address] AS [Customers!2!address], 
	       [City] AS [Customers!2!city], 
	       [Region] AS [Customers!2!region], 
	       [PostalCode] AS [Customers!2!postalcode], 
	       [Country] AS [Customers!2!country], 
	       [Phone] AS [Customers!2!phone], 
	       [Fax] AS [Customers!2!fax]
	  FROM dbo.Customers

	ORDER BY [Customers!2!customerid] ASC
	FOR XML EXPLICIT

	RETURN @@Error

GO		
		

Last Updated: 29 September 2004.