To write a stored procedure

25 pts.
Tags:
Relational databases
SQL Server stored procedures
Stored Procedures
there are two tables one ZipCode and one tempTable each has ZipCode and CongDist as columns. Each Zipcode has more than one CongressionalDistrict. How can I write a SP to have a seperate entry for each senario of Zipcode and Congressional District in tmp Table

Answer Wiki

Thanks. We'll let you know when a new response is added.

We might need a little more info on this. A stored procedure usually takes some input, applies some logic, and returns a result.
If the second table already has Zipcode and Congressional District, why do you need to join them? Simply querying the second table would give the desired result.

Making some assumptions…
ZipCode table has a primary key “pkZip”, and a column with the Zip Code, plus possibly other data.
CongDist table has a foreign key on ZipCode with “pkZip”, but doesn’t actually have the ZipCode value, so you need to join the two tables.

<pre>select cd.CongDist, zc.ZipCode
from
CongDist cd
join ZipCode zc on zc.pkZip = cd.pkZip;</pre>

The above query will return the pairs of congressional districts and zip codes.

You can embed that directly in other queries, or if you really need to make a Stored Procedure, it is just:

<pre>create function spfCongDistZip()
returns table
as
return
select
cd.congdist, zc.zipcode
from
congdist cd
join zipcodes zc on zc.pkzip = cd.pkzip;</pre>

You can then use that function just like a table, e.g.:

<pre>select * from spfCongDistZip();</pre>

or

<pre>select …
from myOtherTable mot
join spfCongDistZip() cdz on cdz.zipcode = mot.zipcode;</pre>

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Yes, more details are needed. You might want to post some example data as well.
    69,160 pointsBadges:
    report
  • SProcedure
    Okay here is what I am looking for 1. I have two tables Zipcode and a temp . 2. Zipcode has many columns along with Zipcode and CongDist 3. I need a temp table to store only Zipcode and CongDist 4. They was the values are stored in Zipcode table is Zipcode = 44819 and CongDist = 15|13|14 5. So what it means is Zipcode 44819 has 3 CongDist 6. I want to seperate each CongDist as a seperate entry in temp table 7. So the way it should store in temp table is each entry for 44819 and 15, 44819 and 13, 44819 and 14 Thanks
    25 pointsBadges:
    report
  • SProcedure
    oaky not a sp but a query I am looking for
    25 pointsBadges:
    report
  • Kccrosser
    I would do this with a function that returns a table, as follows:
    create function spf_ZipCodeCongDist()
    returns @ziptable table (
    	zip	varchar(9),
    	cong	varchar(255)) 
    as
    begin
    	declare @zip	varchar(9);
    	declare @cong	varchar(255);
    	declare @ipos	int;
    	declare cur cursor for 
    		select ZipCode, CongDist
    		from ZipCode;
    	open cur;
    	fetch next from cur into @zip, @cong;
    	while @@FETCH_STATUS = 0
    	begin
    		while @cong is not null
    		begin
    			set @ipos = charindex('|',@cong);
    			if @ipos > 0
    			begin
    				insert into @ziptable values (@zip, left(@cong, @ipos-1));
    				set @cong = substring(@cong, @ipos+1, 255);
    			end
    			else
    			begin
    				insert into @ziptable values (@zip, @cong);
    				set @cong = null;
    			end;
    		end;
    		fetch next from cur into @zip, @cong;
    	end;
    	close cur;
    	deallocate cur;
    	return;
    end;
    You can then query it to return your temp table as:
    select * from spf_ZipCodeCongDist();
    Or, use it in a join, like:
    select ...
    from myTable mt
    join spf_ZipCodeCongDist() zd on zd.zip = mt.zipcode
    ...
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following