Stored Proceedure

[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.

[Jim Doherty]

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]

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.

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_Description)

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

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]

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.

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_Description)

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

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_Address? is it integer or string?

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

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]

Personal_Address 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.

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

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

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

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

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

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_addresses view displays the Address_Type_Description from the ADDRESS_TYPE table (defined as a varchar(25)) as the Address Type field in the view, shouldnt it work?

[Jim Doherty]

Personal_Address 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.

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

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

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

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

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

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_addresses view displays the Address_Type_Description 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?

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

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

FROM Personnel_Address PA JOIN ADDRESS_TYPE AT

it should have 'es' on the end of Personnel_Address

FROM Personnel_Addresses PA JOIN ADDRESS_TYPE AT

Regards

Jim :)

[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!

[Jim Doherty]

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]

I am nto an Excel specialist but check this out:

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

Good Luck.