Solved

Convert Select Statement into StoreProcedure to write to table

Posted on 2010-08-16
15
322 Views
Last Modified: 2013-11-05
Hi guys I have a large student image database which contains four tables at the minute:

dbo.ImageLibrary
photoID      sName            Camera            status      ImgType1      ImgType2      Area            Location      City            Misc1
1      Student Group a      Sony SLR      L               NA      group shot      Campus 3      Campus 3      BOS            NULL
2      Student Group b Cannon Digi      L               NA      single            Building 5 nine      Boston            Building 5 nine      NULL
3      Student a      Sony SLR      D               NA       group shot      Campus 3      BOS            Campus 3      NULL
4      Student Group b      NIKON            L               NA      single            Building 5 nine      Building 5 nine Building 5 nine      NULL
5      Student Group d      Cannon Digi      L               NA      Group Animated      New York City      New York City      New York City      NULL
6      Student b      NIKON            L               NA      group shot      Campus 3      Campus 3      New York City      NULL
7      Student a      Cannon Digi       L               NA      groups 3 to 4      New York City      New York City      New York City      NULL
8      Student Group c      Cannon Digi      L               NA       group shot      Campus 3      Campus 3      BOS            NULL
9      Student a      NIKON            L               NA      Group Animated      Campus 3      Campus 3      BOS            NULL

dbo.ImageType
TypeID      ImgType
1      range group close
2      Angle Shot Close Up
3      group shot
4      single
5      Group Animated
6      double sports play
7      groups 3 to 4

dbo.ImgArea
AreaID ImgArea
1      Campus 3
2      New York City
3      Campus 2
4      Building 5 nine
5      Area 2 stairs
6      campus 1
7      florida beach lawn
8      outside lawn

dbo.ImgCountsTypeAreaLocCity
ID ImgTYpe2     ImgAreaLocCity  ImgCount
1 outside lawn  campus 3         25
2 outside lawn  campus 2         5
3 outside lawn  campus 9         0

I need to get counts of each IMAGE TYPE we have in each IMAGE AREA,against the IMAGE LIBRAYTABLE and record the count along with the ImageType and ImgArea used to the table dbo.ImgCountsTypeAreaLocCity
At the minute I am manually running this select statement:

I take the image type name from table dbo.ImageType e.g: 'group shot' to check against the field ImgType2 and the image area from dbo.ImageArea e.g: 'Campus 3' to check against Area, Location and city in dbo.ImageLibrary and put
them into this statement

SELECT COUNT(*) AS ImgCount
FROM dbo.ImageLibrary
WHERE
(ImgType2 = 'group shot') AND (Area = 'Campus 3' or Location = '' or city = 'Campus 3') AND Status = 'L'

I then copy and paste the values used and the total ImgCount into my table dbo.ImgCountsTypeAreaLocCity which looks like this

ID ImgTYpe2     ImgAreaLocCity  ImgCount
1 outside lawn  campus 3         25
2 outside lawn  campus 2         5
3 outside lawn  campus 9         0


How can I get a stored procdure to do this for me? so that all I have to do to run the stored procdure in Query Anazlery is

exec GetImage 'group shot','Campus 3'

and it will do the count and write the information to the dbo.ImgCountsTypeAreaLocCity table
0
Comment
Question by:jay_eire
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 167 total points
ID: 33447673
what is the result of this query

SELECT COUNT(*) AS ImgCount, Area, Location, City
FROM dbo.ImageLibrary
GROUP BY ImgType2, Area, Location, City
0
 
LVL 7

Author Comment

by:jay_eire
ID: 33447717
@HainKurt thanks... that brings back
1    Area 2 stairs    Area 2 stairs    Area 2 stairs
3    campus 1    campus 1    campus 1
5    Campus 3    Campus 3    BOS
2    florida beach lawn    florida beach lawn    florida beach lawn
1    New York City    New York City    New York City
1    NULL    NULL    NULL
1    Campus 3    BOS    Campus 3
5    Campus 3    Campus 3    BOS
1    Campus 3    Campus 3    New York City
1    florida beach lawn    florida beach lawn    florida beach lawn
2    campus 1    campus 1    campus 1
1    Campus 3    Campus 3    BOS

but its missing the image type information 'group shot' or 'Group Animated' etc
0
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 166 total points
ID: 33447810
Try the below code..
CREATE PROC GetImage

(

@ImgType Varchar(100),

@Area Varchar(100)

)

AS



BEGIN



INSERT INTO dbo.ImgCountsTypeAreaLocCity VALUES (ID,ImgType2,imgAreLocCity,ImgCount)

SELECT IA.AreaID,'outside Lawn' as ImgType2, @Area as CampusCity,COUNT(*) AS ImgCount

FROM dbo.ImageLibrary I INNER JOIN dbo.ImgArea IA ON IA.ImgArea = I.Area

GROUP BY IA.AreaId





END

Open in new window

0
 
LVL 3

Accepted Solution

by:
piba earned 167 total points
ID: 33447865
try this one:
create procedure [GetImage](@type varchar(50),@place varchar(50)) as
begin
      insert ImgCountsTypeAreaLocCity(ImgTYpe2,ImgAreaLocCity,ImgCount)
      SELECT @type,@place,COUNT(*) AS ImgCount
      FROM ImageLibrary
      WHERE
      (ImgType2 = @type) AND (Area = @place or Location = '' or city = @place) AND Status = 'L'
end

or was the query of HainKurt (33447673) only missing 1 field you did want returned::
SELECT COUNT(*) AS ImgCount, ImgType2, Area, Location, City
FROM dbo.ImageLibrary
GROUP BY ImgType2, Area, Location, City
0
 
LVL 7

Author Comment

by:jay_eire
ID: 33447874
@vdr1620 thanks for that but I need the Stored Procedure not to be locked down so I can run it multiple times for different image types and areas e.g:

exec GetImage 'group shot',' Building 5 nine'

or

exec GetImage 'single','campus 1'

is that possible?

0
 
LVL 7

Author Comment

by:jay_eire
ID: 33447932
@piba thanks mate this looks to work :)

could i run that StoredProcedure in batches? there is a lot of records in my table i was thinking it would be good if i could run it in a batches of 10K etc
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33447988
oops.. I forgot to include a Where clause (which would have been similar to Where Clause Piba mentioned)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 7

Author Comment

by:jay_eire
ID: 33448052
thanks guys Il post my other question as a new question as its a little off topic about running that SP in batches.

 I got three really responses thank you VERY much for that, the group statement would work but it would return all the records in the table sometimes I only need to run reports for certain image types and areas.

The stored procedures both work, if its ok with you all il split the points three ways?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33448063
I am assuming Photo id as an identity is that correct?

you can run it batches by including two other input variables using which you can input the Min and Max row Numbers.. and use them in Where


create procedure [GetImage](@type varchar(50),@place varchar(50),@MinId Int,@MaxId Int) as

begin

      insert ImgCountsTypeAreaLocCity(ImgTYpe2,ImgAreaLocCity,ImgCount)

      SELECT @type,@place,COUNT(*) AS ImgCount

      FROM ImageLibrary

      WHERE

      (ImgType2 = @type) AND (Area = @place or Location = '' or city = @place) AND Status = 'L' and PhotoIdId >= @MinId and PhotoId < @MaxId

end

Open in new window

0
 
LVL 7

Author Comment

by:jay_eire
ID: 33448149
@vdr1620: wow thanks mate, how do i execute that in query analyzer? say i want to process row 5 through 10 is it?

exec GetImage 5, 10
0
 
LVL 3

Expert Comment

by:piba
ID: 33448150
if you split points is OK for me.
im not entyrly sure what you mean by running in "batches".. the procedure itself doesnt and cant be split up to only count "parts" of at a time,
you could run it from for example a cursor if thats what you want ?
if you open a new question please post a link here
0
 
LVL 3

Expert Comment

by:piba
ID: 33448220
new try:

create procedure [GetImage](@type varchar(50),@place varchar(50),@MinId Int,@MaxId Int) as
begin
      if not exists(select * from ImgCountsTypeAreaLocCity where ImgTYpe2 = @type  and ImgAreaLocCity = @place)

            insert ImgCountsTypeAreaLocCity(ImgTYpe2,ImgAreaLocCity,ImgCount)
            SELECT @type,@place,COUNT(*) AS ImgCount
            FROM ImageLibrary
            WHERE
            (ImgType2 = @type) AND (Area = @place or Location = '' or city = @place) AND Status = 'L' and
            photoID >= @MinId and photoID <= @MaxId
      else
            update ImgCountsTypeAreaLocCity
            set ImgCount = ImgCount +
            (SELECT COUNT(*) AS ImgCount
            FROM ImageLibrary
            WHERE
            (ImgType2 = @type) AND (Area = @place or Location = '' or city = @place) AND Status = 'L' and
            photoID >= @MinId and photoID <= @MaxId
            and ImgTYpe2 = @type  and ImgAreaLocCity = @place
)
end

exec [GetImage] 'group shot','Campus 3',1,4
exec [GetImage] 'group shot','Campus 3',5,10
0
 
LVL 3

Expert Comment

by:piba
ID: 33448379
Sorry in previous post wrong query in second part, also now checking Location=@place:

drop procedure [GetImage]
GO
create procedure [GetImage](@type varchar(50),@place varchar(50),@MinId Int,@MaxId Int) as
begin
      if not exists(select * from ImgCountsTypeAreaLocCity where ImgTYpe2 = @type  and ImgAreaLocCity = @place)

            insert ImgCountsTypeAreaLocCity(ImgTYpe2,ImgAreaLocCity,ImgCount)
            SELECT @type,@place,COUNT(*) AS ImgCount
            FROM ImageLibrary
            WHERE
            (ImgType2 = @type) AND (Area = @place or Location = @place or city = @place) AND Status = 'L' and
            photoID >= @MinId and photoID <= @MaxId
      else
            update ImgCountsTypeAreaLocCity
            set ImgCount = ImgCount +
            (SELECT COUNT(*) AS ImgCount
            FROM ImageLibrary
            WHERE
            (ImgType2 = @type) AND (Area = @place or Location = @place or city = @place) AND Status = 'L' and
            photoID >= @MinId and photoID <= @MaxId)
            where ImgTYpe2 = @type  and ImgAreaLocCity = @place
end

exec [GetImage] 'group shot','Campus 3',1,4
exec [GetImage] 'group shot','Campus 3',5,10
0
 
LVL 7

Author Comment

by:jay_eire
ID: 33448498
@piba thanks for that mate, this is kind of what im looking for but

but is it possible for the dbo.ImageType to be the master table for controlling the batches, I would like to be able to run the stored procedure based on the ImgType field in dbo.ImageType.

so I can control how many Image Types can be processed, to run the stored procedure I would just type
exec GetImage 10, 100 so the storedproecudre would process the image types from rows 10 to 100 then the next day I can step it up to process 101 - to 500 on so on.
0
 
LVL 7

Author Comment

by:jay_eire
ID: 33448685
I posted a New Question Here for batches I explain better what im looking for thanks guys.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26406525.html
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now