I am trying to learn how to create and use SQL Server stored procedures, and I am finding that every example out there, uses varchar(xx) as the parameters, and never an int, which is what would be used if for example, you wanted to search on an ID value to select a specific record, such as in the first code example.
CREATE PROCEDURE GetApplicationStartData
@CompanyID int(1)
AS
SET NOCOUNT ON;
SELECT *
FROM ApplicationStartData
WHERE CompanyID = @CompanyID
GO
Using this code, when it is executed, the following error occurs:
Msg 137, Level 15, State 2, Procedure GetApplicationStartData, Line 7
Must declare the scalar variable "@CompanyID"
Following is an example from the Microsoft website:
CREATE PROCEDURE HumanResources.uspGetEmployeesTest2
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName
AND EndDate IS NULL;
GO
Here is a snippet of the design of the ApplicationStartData table:
CREATE TABLE [dbo].[ApplicationStartData]
(
[CompanyID] [int] NOT NULL,
[PrimaryDomainName] [varchar](20) NULL,
[CompanyName] [varchar](25) NULL,
[CompanyAddress1] [varchar](25) NULL,
[CompanyAddress2] [varchar](25) NULL,
[CompanyCity] [varchar](20) NULL,
[CompanyState] [varchar](2) NULL,
[CompanyZIP] [varchar](10) NULL,
[CompanyPhone1] [varchar](15) NULL,
[CompanyPhone2] [varchar](15) NULL,
[CompanyPhone3] [varchar](15) NULL,
[CompanyFax] [varchar](15) NULL,
... and 40 more columns
How is this accomplished?