Stored Proceedure

**Jim Doherty** · Nov 9 '07, 08:04 AM

Originally posted by jthep

Is there a way to create a stored procedure where more than one type of parameter can be entered? For example, a user can enter a int type, char(4) type, or nothing in the same stored proceedure.

Stored procedures are designed to take multiple parameters... or am I missing something

Jim :)

**jthep** · Nov 9 '07, 09:29 AM

Sorry, erase the first question and inserting a new one =D. I have a stored procedure below that displays data from both views and tables.

Code:

CREATE PROCEDURE personAddress
	@Person_Id int = NULL,
	@Address_Type varchar(35) = NULL
AS
	DECLARE @Employee_Id_Count as int
	DECLARE @Address_Type_Count as int

	SELECT @Employee_Id_Count = count(*)
	FROM PERSON P
	WHERE P.Person_Id = @Person_Id
	
	IF @Employee_Id_Count = 0
	BEGIN
		PRINT 'Error Msg 529141'
		RAISERROR(529141, 10, 1, @Person_Id)
		RETURN
	END
	SET NOCOUNT ON;

	SELECT @Address_Type_Count = count (*)
	FROM ADDRESS_TYPE AT
	WHERE Address_Type_Code = @Address_Type

	IF @Address_Type != NULL
		IF @Address_Type_Count = 0
		BEGIN 
			PRINT 'Error Msg 529142'
			RAISERROR(529142,10, 1, @Address_Type)
			RETURN
		END
	
		SELECT PA.[Employee Name] AS Name,
			PA.[Street Address] AS [Street Address],
			AT.Address_Type_Code AS [Type of Address Description]
		FROM Personnel_Address PA JOIN ADDRESS_TYPE AT
		ON PA.[Employee ID] = @Person_Id AND PA.[Address Type] = @Address_Type
	
	IF @Address_Type = NULL
		SELECT PA.[Employee Name] AS Name,
			PA.[Street Address] AS [Street Address],
			PA.[Address Type] AS [Type of Address Description]
		FROM Personnel_Address PA
		WHERE PA.[Employee ID] = @Person_Id

I don't know if I wrote it correctly but the user could either enter Person_Id, or (Person_Id, Address_Descrip tion)

If the user entered just the Person_Id param then the procedure checks if it is valid, and if not, it prints out an error. If the Id is valid, it displays all addresses the person of that Id has (it could be office, home, primary, etc).

If the user entered two params, it checks the second parameter. If it is invalid, an error message is printed otherwise it will display the address of the description entered with the id person's info.

When I try to execute the store procedure using

Code:

personAddress 4, Office;

There is an error message:
Msg 245, Level 16, State 1, Procedure personAddress, Line 27
Conversion failed when converting the varchar value 'Office' to data type int.

Can someone tell me why it is giving me that error msg?

Regards
Jthep

**Jim Doherty** · Nov 9 '07, 10:32 AM

Originally posted by jthep

Sorry, erase the first question and inserting a new one =D. I have a stored procedure below that displays data from both views and tables.

Code:

CREATE PROCEDURE personAddress
	@Person_Id int = NULL,
	@Address_Type varchar(35) = NULL
AS
	DECLARE @Employee_Id_Count as int
	DECLARE @Address_Type_Count as int
 
	SELECT @Employee_Id_Count = count(*)
	FROM PERSON P
	WHERE P.Person_Id = @Person_Id
 
	IF @Employee_Id_Count = 0
	BEGIN
		PRINT 'Error Msg 529141'
		RAISERROR(529141, 10, 1, @Person_Id)
		RETURN
	END
	SET NOCOUNT ON;
 
	SELECT @Address_Type_Count = count (*)
	FROM ADDRESS_TYPE AT
	WHERE Address_Type_Code = @Address_Type
 
	IF @Address_Type != NULL
		IF @Address_Type_Count = 0
		BEGIN 
			PRINT 'Error Msg 529142'
			RAISERROR(529142,10, 1, @Address_Type)
			RETURN
		END
 
		SELECT PA.[Employee Name] AS Name,
			PA.[Street Address] AS [Street Address],
			AT.Address_Type_Code AS [Type of Address Description]
		FROM Personnel_Address PA JOIN ADDRESS_TYPE AT
		ON PA.[Employee ID] = @Person_Id AND PA.[Address Type] = @Address_Type
 
	IF @Address_Type = NULL
		SELECT PA.[Employee Name] AS Name,
			PA.[Street Address] AS [Street Address],
			PA.[Address Type] AS [Type of Address Description]
		FROM Personnel_Address PA
		WHERE PA.[Employee ID] = @Person_Id

I don't know if I wrote it correctly but the user could either enter Person_Id, or (Person_Id, Address_Descrip tion)

If the user entered just the Person_Id param then the procedure checks if it is valid, and if not, it prints out an error. If the Id is valid, it displays all addresses the person of that Id has (it could be office, home, primary, etc).

If the user entered two params, it checks the second parameter. If it is invalid, an error message is printed otherwise it will display the address of the description entered with the id person's info.

When I try to execute the store procedure using

Code:

personAddress 4, Office;

There is an error message:
Msg 245, Level 16, State 1, Procedure personAddress, Line 27
Conversion failed when converting the varchar value 'Office' to data type int.

Can someone tell me why it is giving me that error msg?

Regards
Jthep

What is this datatype highlighted in bold in the source Personnel_Addre ss? is it integer or string?

Code:

SELECT PA.[Employee Name] AS Name,
			PA.[Street Address] AS [Street Address],
			AT.Address_Type_Code AS [Type of Address Description]
		FROM Personnel_Address PA JOIN ADDRESS_TYPE AT
		ON PA.[Employee ID] = @Person_Id AND[b] PA.[Address Type][/b] = @Address_Type

In the absence of table structures and datatypes one can only guess but basically it can't convert a string to integer if thats what your column contains and is comparing
Jim :)

**jthep** · Nov 9 '07, 10:54 AM

Personal_Addres s is a view. Employee_Id field in the view is a person that can either be a full time or part time employee. There are tables for each type of employee.

Code:

CREATE VIEW Personnel_Addresses ([Employee ID], [Employee Name], [Address Type],
	[Street Address], [City], [State], [Zip Code]) AS
SELECT 
	P.Person_Id, case when Middle_Name_Initial is null then
	(Last_Name + ', ' + First_Name) else (Last_Name + ', ' + First_Name + '  ' 
	+ Middle_Name_Initial + '.') end, Address_Type_Description,
	case when A.Address_line_2 is null then A.Address_line_1
	else (A.Address_line_1 + ', ' + A.Address_line_2) end, 
	Z.Zip_City, Z.Zip_State, Z.Zip_Code
FROM PERSON P JOIN PERSON_ADDRESS PA
ON (P.Person_Id = PA.Person_Id) JOIN ADDRESS_TYPE AT 
	ON (AT.Address_Type_Code = PA.Address_Type_Code) JOIN ADDRESS A 
	ON (A.Address_Id = PA.Address_Id) JOIN ZIP Z 
	ON (A.Zip_Code = Z.Zip_Code)

The following are the tables I made that are used to create this view:

Person table

Code:

CREATE TABLE PERSON (
	Person_Id int NOT NULL,
	First_Name varchar(20) NOT NULL,
	Last_Name varchar(20) NOT NULL,
	Middle_Name_Initial char(1) NULL,
	Date_Of_Birth DateTime NOT NULL,
	Employment_Status char(1) NOT NULL,
	CONSTRAINT ck_employmentStats
		CHECK(Employment_Status IN ('F', 'P')),
	CONSTRAINT pk_person 
		PRIMARY KEY (Person_Id),
);

Person_Address

Code:

CREATE TABLE PERSON_ADDRESS (
	Person_Id int NOT NULL,
	Address_Id int NOT NULL,
	Address_Type_Code int NOT NULL,
	CONSTRAINT pk_person_address
		PRIMARY KEY (Person_Id, Address_Id),
);

Address_Type

Code:

CREATE TABLE ADDRESS_TYPE (
	Address_Type_Code int NOT NULL,
	Address_Type_Description varchar(35) NOT NULL,
	CONSTRAINT pk_address_type_code
		PRIMARY KEY (Address_Type_Code),
);

Address

Code:

CREATE TABLE ADDRESS (
	Address_Id int NOT NULL IDENTITY(1,1),
	Address_line_1 varchar(25) NOT NULL,
	Address_line_2 varchar(25) NULL,
	Zip_Code char(9) NOT NULL,
	CONSTRAINT pk_addId
		PRIMARY KEY (Address_Id),
);

Zip

Code:

CREATE TABLE ZIP (
	Zip_City varchar(25) NOT NULL,
	Zip_State char(2) NOT NULL,
	Zip_Code char(9) NOT NULL,
	CONSTRAINT pk_zipcode
		PRIMARY KEY (Zip_Code),
);

Since the personnel_addre sses view displays the Address_Type_De scription from the ADDRESS_TYPE table (defined as a varchar(25)) as the Address Type field in the view, shouldnt it work?

**Jim Doherty** · Nov 9 '07, 04:25 PM

Originally posted by jthep

Personal_Addres s is a view. Employee_Id field in the view is a person that can either be a full time or part time employee. There are tables for each type of employee.

Code:

CREATE VIEW Personnel_Addresses ([Employee ID], [Employee Name], [Address Type],
	[Street Address], [City], [State], [Zip Code]) AS
SELECT 
	P.Person_Id, case when Middle_Name_Initial is null then
	(Last_Name + ', ' + First_Name) else (Last_Name + ', ' + First_Name + ' ' 
	+ Middle_Name_Initial + '.') end, Address_Type_Description,
	case when A.Address_line_2 is null then A.Address_line_1
	else (A.Address_line_1 + ', ' + A.Address_line_2) end, 
	Z.Zip_City, Z.Zip_State, Z.Zip_Code
FROM PERSON P JOIN PERSON_ADDRESS PA
ON (P.Person_Id = PA.Person_Id) JOIN ADDRESS_TYPE AT 
	ON (AT.Address_Type_Code = PA.Address_Type_Code) JOIN ADDRESS A 
	ON (A.Address_Id = PA.Address_Id) JOIN ZIP Z 
	ON (A.Zip_Code = Z.Zip_Code)

The following are the tables I made that are used to create this view:

Person table

Code:

CREATE TABLE PERSON (
	Person_Id int NOT NULL,
	First_Name varchar(20) NOT NULL,
	Last_Name varchar(20) NOT NULL,
	Middle_Name_Initial char(1) NULL,
	Date_Of_Birth DateTime NOT NULL,
	Employment_Status char(1) NOT NULL,
	CONSTRAINT ck_employmentStats
		CHECK(Employment_Status IN ('F', 'P')),
	CONSTRAINT pk_person 
		PRIMARY KEY (Person_Id),
);

Person_Address

Code:

CREATE TABLE PERSON_ADDRESS (
	Person_Id int NOT NULL,
	Address_Id int NOT NULL,
	Address_Type_Code int NOT NULL,
	CONSTRAINT pk_person_address
		PRIMARY KEY (Person_Id, Address_Id),
);

Address_Type

Code:

CREATE TABLE ADDRESS_TYPE (
	Address_Type_Code int NOT NULL,
	Address_Type_Description varchar(35) NOT NULL,
	CONSTRAINT pk_address_type_code
		PRIMARY KEY (Address_Type_Code),
);

Address

Code:

CREATE TABLE ADDRESS (
	Address_Id int NOT NULL IDENTITY(1,1),
	Address_line_1 varchar(25) NOT NULL,
	Address_line_2 varchar(25) NULL,
	Zip_Code char(9) NOT NULL,
	CONSTRAINT pk_addId
		PRIMARY KEY (Address_Id),
);

Zip

Code:

CREATE TABLE ZIP (
	Zip_City varchar(25) NOT NULL,
	Zip_State char(2) NOT NULL,
	Zip_Code char(9) NOT NULL,
	CONSTRAINT pk_zipcode
		PRIMARY KEY (Zip_Code),
);

Since the personnel_addre sses view displays the Address_Type_De scription from the ADDRESS_TYPE table (defined as a varchar(25)) as the Address Type field in the view, shouldnt it work?

Its easy now you have posted your tables thanks.

Look at your procedure for returning the count for address type...you are comparing int against string can you see?

Code:

 SELECT @Address_Type_Count = count (*) 
FROM ADDRESS_TYPE AT
WHERE [b]Address_Type_Code = @Address_Type[/b]

Address type code is an integer in your table and you mistakenly comparing @Address_Type defined as an varchar(35) parameter against it. It cannot convert that. That is where your error lies

In addition you also have an error that you might wish to fix at this line

Code:

FROM Personnel_Address PA JOIN ADDRESS_TYPE AT

it should have 'es' on the end of Personnel_Addre ss

Code:

FROM Personnel_Address[b]es[/b] PA JOIN ADDRESS_TYPE AT

Regards

Jim :)

**jthep** · Nov 16 '07, 09:26 AM

OOHHHH that's why. I think my problem is due to the naming convention. I have attributes in one table that has the either the same name or something that's very close to another table that I end up getting all confused or mix and match them up sometimes. =D

Thanks Jim!

**Jim Doherty** · Nov 16 '07, 12:58 PM

Originally posted by jthep

OOHHHH that's why. I think my problem is due to the naming convention. I have attributes in one table that has the either the same name or something that's very close to another table that I end up getting all confused or mix and match them up sometimes. =D

Thanks Jim!

You're very welcome..just place the box of sweets on the shared office desk on the way out :))

Jim

**iburyak** · Nov 16 '07, 07:35 PM

I am nto an Excel specialist but check this out:

http://office.microsof t.com/en-us/excel/HP051995481033. aspx

Good Luck.

Stored Proceedure

Stored Proceedure

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment