June 8, 2012 at 1:25 pm
Hi all, I'm new to SQL, any instructions or sample code to create the Store Proc below would be greatly appreciated.
Find below the details.
Requirement: create SP to validate the city and zip combination.
Step 1: validate zip vs state code
Step2 : if the above condition pass, then verify the zip code vs city
2 different errors for both cases.
Table name: Zips
Columns: Zip, City, StateCode, County.
Note: this proc need to accect 3 parameters zip, city, and state
Thanks,
SueTons
June 8, 2012 at 1:42 pm
SueTons (6/8/2012)
Hi all, I'm new to SQL, any instructions or sample code to create the Store Proc below would be greatly appreciated.Find below the details.
Requirement: create SP to validate the city and zip combination.
Step 1: validate zip vs state code
Step2 : if the above condition pass, then verify the zip code vs city
2 different errors for both cases.
Table name: Zips
Columns: Zip, City, StateCode, County.
Note: this proc need to accect 3 parameters zip, city, and state
Thanks,
SueTons
And you are going to need to provide more information to accomplish this task. Nothing you have provided even tells us how we are supposed to accomplish the validation. You need to provide the DDL (CREATE TABLE) statement(s) for the table(s) involved, sample data for the table(s) as a series of insert into statements, the expected output based on the sample data, and most importantly the code you have written so far to accomplish this task.
June 8, 2012 at 3:48 pm
create table Zips (Zip int, City [varchar](36) null, StateCode [varchar](2) NULL, County [varchar](36) null)
insert into Zips values (75056, 'Dallas', 'TX', 'Dallas');
insert into Zips values (75057, 'Irving', 'TX', 'Dallas');
insert into Zips values (15058, 'LA', 'CA', 'LA');
insert into Zips values (15059, 'Irvine', 'CA', 'Alameda');
drop PROCEDURE usp_TestZip
CREATE PROCEDURE usp_TestZip
@Zip int,
@State varchar(36),
@City varchar(36)
AS
IF 1 = (Select 1 from Zips where Zip = @Zip and StateCode = @State)
begin
IF 1 = (Select 1 from Zips where Zip = @Zip and City = @City) Print 'OK'
ELSE PRINT 'City and Zip combo failed'
end
ELSE PRINT 'State and Zip combo failed'
go
--passes
EXEC usp_TestZip 75056,'TX', 'Dallas'
-- fails state
EXEC usp_TestZip 75056,'CA', 'Dallas'
-- fails city
EXEC usp_TestZip 75056,'TX', 'la'
I am sure there is better validation code out there I just spit it out since the "Pro's" here always cry about not providing enough detail, the question had enough to go with in my opinion.
June 8, 2012 at 4:02 pm
thadeushuck (6/8/2012)
create table Zips (Zip int, City [varchar](36) null, StateCode [varchar](2) NULL, County [varchar](36) null)insert into Zips values (75056, 'Dallas', 'TX', 'Dallas');
insert into Zips values (75057, 'Irving', 'TX', 'Dallas');
insert into Zips values (15058, 'LA', 'CA', 'LA');
insert into Zips values (15059, 'Irvine', 'CA', 'Alameda');
drop PROCEDURE usp_TestZip
CREATE PROCEDURE usp_TestZip
@Zip int,
@State varchar(36),
@City varchar(36)
AS
IF 1 = (Select 1 from Zips where Zip = @Zip and StateCode = @State)
begin
IF 1 = (Select 1 from Zips where Zip = @Zip and City = @City) Print 'OK'
ELSE PRINT 'City and Zip combo failed'
end
ELSE PRINT 'State and Zip combo failed'
go
--passes
EXEC usp_TestZip 75056,'TX', 'Dallas'
-- fails state
EXEC usp_TestZip 75056,'CA', 'Dallas'
-- fails city
EXEC usp_TestZip 75056,'TX', 'la'
I am sure there is better validation code out there I just spit it out since the "Pro's" here always cry about not providing enough detail, the question had enough to go with in my opinion.
Maybe, but this is also a homework assignment based on the other post. We'd prefer to have the OP show us their work and help guide them in the development of a solution rather than just handing it to them.
And what is wrong with having the OPs provide as much information as possible up front? I have seen many times when an OP has actually solved their own problem while putting together the information we request.
June 8, 2012 at 4:08 pm
Just a couple of points.
It doesn't bother you that you did someone's homework for them without asking them to contribute to the work? Zip codes have leading '0' in some cases so INT probably isn't the best data type for them. Were the messages sufficient for the return of status, or was the proc supposed to stop and use raiserror to indicate a failure? Was the RETURN value of the proc supposed to indicate success or failure? I asked these questions on a duplicate thread and never got an answer.
In the best case the OP will study what you provided and learn from it. In the worst case they will just turn it in as the answer.
I'm not one of the "Pros", since I do more lurking and learning than answering, but the folks who do jump in here are usually concerned that the folks they are trying to help actually get something out of the experience that is of greater value than just the one time answer to a one time problem.
Just my 2 cents.
June 8, 2012 at 4:21 pm
Lynn Pettis (6/8/2012)
SueTons (6/8/2012)
Hi all, I'm new to SQL, any instructions or sample code to create the Store Proc below would be greatly appreciated.Find below the details.
Requirement: create SP to validate the city and zip combination.
Step 1: validate zip vs state code
Step2 : if the above condition pass, then verify the zip code vs city
2 different errors for both cases.
Table name: Zips
Columns: Zip, City, StateCode, County.
Note: this proc need to accect 3 parameters zip, city, and state
Thanks,
SueTons
And you are going to need to provide more information to accomplish this task. Nothing you have provided even tells us how we are supposed to accomplish the validation. You need to provide the DDL (CREATE TABLE) statement(s) for the table(s) involved, sample data for the table(s) as a series of insert into statements, the expected output based on the sample data, and most importantly the code you have written so far to accomplish this task.
Lynn, As requested here are the DDL, Inserts and the sample output.
Create database ZipCode
create table Zips (Zip char(5), City varchar(32), StateCode char(2), County varchar(32))
Insert into Zips values ('07087', 'Union City', 'NJ', 'Hudon')
Insert into Zips values ('07621', 'Bergenfield', 'NJ', 'Bergen')
Insert into Zips values ('07094', 'Secaucus', 'NJ', 'Hudson')
Insert into Zips values ('07628', 'Dumont', 'NJ', 'Bergen')
Sample output....
07087 Union City NJ
Regards,
SueTons
June 8, 2012 at 4:22 pm
thadeushuck (6/8/2012)
create table Zips (Zip int, City [varchar](36) null, StateCode [varchar](2) NULL, County [varchar](36) null)insert into Zips values (75056, 'Dallas', 'TX', 'Dallas');
insert into Zips values (75057, 'Irving', 'TX', 'Dallas');
insert into Zips values (15058, 'LA', 'CA', 'LA');
insert into Zips values (15059, 'Irvine', 'CA', 'Alameda');
drop PROCEDURE usp_TestZip
CREATE PROCEDURE usp_TestZip
@Zip int,
@State varchar(36),
@City varchar(36)
AS
IF 1 = (Select 1 from Zips where Zip = @Zip and StateCode = @State)
begin
IF 1 = (Select 1 from Zips where Zip = @Zip and City = @City) Print 'OK'
ELSE PRINT 'City and Zip combo failed'
end
ELSE PRINT 'State and Zip combo failed'
go
--passes
EXEC usp_TestZip 75056,'TX', 'Dallas'
-- fails state
EXEC usp_TestZip 75056,'CA', 'Dallas'
-- fails city
EXEC usp_TestZip 75056,'TX', 'la'
I am sure there is better validation code out there I just spit it out since the "Pro's" here always cry about not providing enough detail, the question had enough to go with in my opinion.
Thanks, I will try this as well.
June 8, 2012 at 4:24 pm
What do you want the proc to do if an invalid combination is passed in?
What do you want it to do when a valid combination is passed in?
June 8, 2012 at 4:30 pm
thadeushuck (6/8/2012)
create table Zips (Zip int, City [varchar](36) null, StateCode [varchar](2) NULL, County [varchar](36) null)insert into Zips values (75056, 'Dallas', 'TX', 'Dallas');
insert into Zips values (75057, 'Irving', 'TX', 'Dallas');
insert into Zips values (15058, 'LA', 'CA', 'LA');
insert into Zips values (15059, 'Irvine', 'CA', 'Alameda');
drop PROCEDURE usp_TestZip
CREATE PROCEDURE usp_TestZip
@Zip int,
@State varchar(36),
@City varchar(36)
AS
IF 1 = (Select 1 from Zips where Zip = @Zip and StateCode = @State)
begin
IF 1 = (Select 1 from Zips where Zip = @Zip and City = @City) Print 'OK'
ELSE PRINT 'City and Zip combo failed'
end
ELSE PRINT 'State and Zip combo failed'
go
--passes
EXEC usp_TestZip 75056,'TX', 'Dallas'
-- fails state
EXEC usp_TestZip 75056,'CA', 'Dallas'
-- fails city
EXEC usp_TestZip 75056,'TX', 'la'
I am sure there is better validation code out there I just spit it out since the "Pro's" here always cry about not providing enough detail, the question had enough to go with in my opinion.
Using what you provided, this is one way to accomplish the task. While working on this, the OP did provide some information, but still not enough to provide the answer that is really needed.
create table dbo.Zips (
Zip CHAR(5) NOT NULL, -- zip codes can hav leading zeros, therefore needs to be a character string
City [varchar](36) NOT NULL,
StateCode [varchar](2) NOT NULL,
County [varchar](36) NULL);
GO
insert into Zips values ('75056', 'Dallas', 'TX', 'Dallas');
insert into Zips values ('75057', 'Irving', 'TX', 'Dallas');
insert into Zips values ('95058', 'LA', 'CA', 'LA');
insert into Zips values ('95059', 'Irvine', 'CA', 'Alameda');
GO
CREATE PROCEDURE dbo.usp_TestZip
@Zip CHAR(5),
@State varchar(36),
@City varchar(36)
AS
BEGIN
DECLARE @retval INT;
IF EXISTS(SELECT 1 FROM dbo.Zips WHERE Zip = @Zip AND StateCode = @State AND City = @City)
SET @retval = 0 -- Pass
ELSE IF NOT EXISTS(SELECT 1 FROM dbo.Zips WHERE Zip = @Zip AND StateCode = @State)
SET @retval = -1 -- Fail, Zip/State
ELSE IF NOT EXISTS(SELECT 1 FROM dbo.Zips WHERE Zip = @Zip AND City = @City)
SET @retval = -2 -- Fail, Zip/City
ELSE
SET @retval = -3; -- Fail, Undefined
RETURN (@retval);
END
go
DECLARE @retval INT;
--passes
EXEC @retval = dbo.usp_TestZip 75056,'TX', 'Dallas';
SELECT @retval;
-- fails state
EXEC @retval = dbo.usp_TestZip 75056,'CA', 'Dallas';
SELECT @retval;
-- fails city
EXEC @retval = dbo.usp_TestZip 75056,'TX', 'la';
SELECT @retval;
go
drop PROCEDURE dbo.usp_TestZip;
DROP TABLE dbo.Zips;
go
June 8, 2012 at 4:32 pm
David Webb-200187 (6/8/2012)
What do you want the proc to do if an invalid combination is passed in?What do you want it to do when a valid combination is passed in?
Hi, Is that the same questions asked twice?......or do they mean different? Please confirm?
Regards,
SueTons
June 8, 2012 at 4:39 pm
The proc Lynn gave you returns different values when zip code/state/city combinations that are in the table and zip code/state/city combinations that are not in the table are passed in. Is that what you want it to do?
That's a very reasonable way to return the information and status, but your instructions from the teacher may be different. The instructor may have wanted you to use a 'raiserror' to indicate an invalid condition, or they might not have specified anything regarding the results. Just want to make sure all the criteria for completion are met.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply