May 8, 2016 at 11:28 am
Hello All, I am using SQL Server 2012. I am having some difficulties with a procedure I created. The procedure compiled correctly. But, I'm not sure how to execute the procedure from command prompt and if it's actually doing what I expect it to do.
The procedure should add new offices to the table making sure it is not a duplicate. A message should be returned with success or failure of the action.
Any assistance is appreciated.
CREATE TABLE Office
(
Office_ID INTPRIMARY KEYNOT NULL,
Office_Name VARCHAR (50)NOT NULL,
Office_AddressVARCHAR (50)NOT NULL,
Office_PhoneNumberINT NOT NULL,
Office_Type VARCHAR(15)NOT NULL
);
INSERT INTO Office
VALUES(101,'General','3730 Falls Road Baltimore, MD 21236','4102350999','Express');
INSERT INTO Office
VALUES(102,'Pediatrics','222 W. Cold Spring Lane Baltimore, MD 21258','4102348632','Express');
INSERT INTO Office
VALUES(103,'OBGYN','711 W 40th St #438 Towson, MD 21236','4432227665','Express');
create procedure [sp_Addoffices]
(
@Office_Name varchar(50),
@Office_Address varchar (50),
@Office_PhoneNumber int,
@Office_Type varchar (15)
)
as
begin
declare @OfficeId int=0;
set @OfficeId=(select Office_ID from Office where Office_Name=@Office_Name)
if(@OfficeId=0)
select 'failure'
else
begin
BEGIN TRY
INSERT INTO Office ( Office_Name,Office_Address,Office_PhoneNumber,Office_Type)
VALUES (@Office_Name,@Office_Address,@Office_PhoneNumber,@Office_Type)
select 'Success'
END TRY
BEGIN CATCH
select 'failure'
END CATCH
End
end
May 8, 2016 at 4:10 pm
Execute your stored procedure from SQL Server Management Studio with a statement like this:
exec [sp_Addoffices]
@Office_Name = 'The Batch',
@Office_Address = '1 Test Drive',
@Office_PhoneNumber = 123456,
@Office_Type = 'Holiday House'
I suggest editing your stored procedure to return an error message that describes any error encountered, something like below:
create procedure [sp_Addoffices]
(
@Office_Name varchar(50),
@Office_Address varchar (50),
@Office_PhoneNumber int,
@Office_Type varchar (15)
)
as
begin
declare @OfficeId int=0;
set @OfficeId=(select Office_ID from Office where Office_Name=@Office_Name)
if(@OfficeId=0)
select 'Failure', 'Office already exists' as errortext
else
begin
BEGIN TRY
INSERT INTO Office ( Office_Name,Office_Address,Office_PhoneNumber,Office_Type)
VALUES (@Office_Name,@Office_Address,@Office_PhoneNumber,@Office_Type)
select 'Success', null as errortext
END TRY
BEGIN CATCH
select 'Failure', ERROR_MESSAGE() as errortext
END CATCH
End
end
Those INSERT commands will give an error - the maximum value for an INT column is "2147483647" and your phone numbers are longer. You could change the column to be a BIGINT value, which is longer, but because phone numbers might include non-numeric values, like country codes and extension codes, for example "+61 346 545 43456 #2346", I suggest changing it to a VARCHAR value instead - a length of VARCHAR(50) would be more than enough.
If you were to change your existing stored procedure to the above, you would see that it's currently producing an error: you're not inserting a value into the NOT NULL Office_ID column.
If those Office_ID values are not used anywhere else than in this system, change your table definition to add "IDENTITY(1,1)" to the Office_ID column definition, that will automatically set the Office_ID value for each inserted row, starting at 1.
CREATE TABLE Office
(
Office_ID INT[highlight="#ffff11"]PRIMARY KEY IDENTITY(1,1)[/highlight]NOT NULL,
Office_Name VARCHAR (50)NOT NULL,
Office_AddressVARCHAR (50)NOT NULL,
Office_PhoneNumberVARCHAR(50) NOT NULL,
Office_Type VARCHAR(15)NOT NULL
);
If the Office_ID values come from outside of this system, add @Office_ID as a parameter to your stored procedure, and add it to the insert statements. You will need to wrap your INSERT INTO Office statements with IDENTITY_INSERT ON before the INSERT statements, and IDENTITY INSERT OFF after them - otherwise SQL won't let you insert a value into the column because it has IDENTITY set on it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply