Looping through a stored Procedure with different values

Question

Hey I have Multiple BannerID that I need for a parameter in a stored procedure I would like to loop through the stored procedure, changing the BannerID im passing along as a parameter. Here is my code,

 create table #a
        (BannerID int,
        BannerName varchar(100)NULL,
        InterActionRate Decimal(5,3) NULL
        )        

Declare @Banker int
Set @Banker = 0

insert into #a(BannerID)
Values (21212),(21577)

WHILE (@Banker > 2)
BEGIN

Insert Into #a(BannerName,InteractionRate)
Exec BannerSummaryReport @BannerID=BannerID,@DateStart = N'05/01/15',@DateEnd = N'05/20/15'
Set @Banker = @Banker +1
END
Select * from #a

I keep getting the error:

Msg 8114, Level 16, State 5, Procedure BannerSummaryReport, Line 0 Error converting data type nvarchar to int. The error loops through as if it is called more then twice and I never get to Execution of the Query. IF that helps. Any Ideas?

Edit: I added a Variable called @Banker which I use as a counter for my while loop. THis makes it so the loop now ends. But my stored procedure is still not filling in the data, IE BannerName and InteractionRate still null in table.

As some have requested the stored procedure the problem is I cant access it I can give you an example of the output though,

Exec BannerSummaryReport @BannerID=21212,@DateStart = N'05/01/15',@DateEnd = N'05/20/15'

I get a hugh list of items

ReportID    BannerName  TagName CompanyName BannerStatusID  BannerID    Impressions FlashImpressions    NoScriptImpressions UniqueViers TotalInterActions   WebCT   ListingCT   ListingClickThrough TotalCT InterActionRate ClickThroughRate    InterActionDiff ClickThroughDiff    ActiveBanners   DataSort1   DataSort2   RollOverCount   RollOverTime    ListingVideoPlayCount   ListingVideoPlayTime    ListingEmailDealer  ListingEmailFriend  ClickSortItem1  ClickSortItem2  ClickSortMenu   ClickLogo   ClickMap    ClickWebSite    DateAdded   PercentagePlayed    TagVideoPlayCount   TagVideoPlayTime    TagVideoTwitter ListingVideoTwitter TagVideoFacebook    ListingVideoFacebook    TagVideoPinterest   ListingVideoPinterest   ListingThumbnail    ListingScroll   TagVideoPlayButtonCount UpdateFilterButtonClick ClickTopSheetExtendImage    AccountID   ModelSelectClick    TrimSelectClick ExteriorColorSelectClick    InteriorColorSelectClick    InventoryViewClick  ShareButtonClick    ZipCodeEntered  MapClickThrough MenuOpen    SummaryClick    Misellaneous    BannerType  VPaidPreRollTime    VPaidPreRollCount   Mids    Ends    CreativeIsTrackable CreativeWasViewable
1   JimmyEllisDemo  All DO NOT TOUCH    1   21212   10932905    906549  0   0   11385   63  13  0   0   0.10414 0.00058 NULL    NULL    0   Make    Model   11291   128193472   14  163846  0   0   1   0   2   0   0   63  2012-01-13  0   7328    48262968    0   0   0   0   0   0   0   0   1   0   0   666 0   0   0   0   0   0   0   0   0   0   0   1   0   0   NULL    NULL    NULL    NULL

Answer 1

It is obvious you have error in your code. I can reproduce the error:

create procedure spTest @i int
as
Select @i as i
go

exec spTest @i = blabla

Error:

Error converting data type nvarchar to int.

Now look at your code:

 Exec BannerSummaryReport @BannerID=BannerID,@DateStart = N'05/01/15',@DateEnd = N'05/20/15'

What is @BannerID=BannerID? You are not passing int value to int parameter of stored proc. You are passing word BannerID.

EDIT:

I guess you want iterate through all rows in #a table and update BannerName and InterActionRate columns with data returned from stored proc for particular row. Unfortunately there is no direct way.

You need to declare another temporary table that will contain all columns stored proc returns in same order. I.e.

create table #a
(
 BannerID int,
 BannerName varchar(100) NULL,
 InterActionRate int NULL
)        

insert into #a(BannerID)
Values (21212),(21577)

create table #tmp
(
ReportID
BannerName
TagName
CompanyName
BannerStatusID
BannerID
Impressions
FlashImpressions
NoScriptImpressions
UniqueViers
TotalInterActions
WebCT
ListingCT
ListingClickThrough
TotalCT
InterActionRate
ClickThroughRate
InterActionDiff
ClickThroughDiff
ActiveBanners
DataSort1
DataSort2
RollOverCount
RollOverTime
ListingVideoPlayCount
ListingVideoPlayTime
ListingEmailDealer
ListingEmailFriend
ClickSortItem1
ClickSortItem2
ClickSortMenu
ClickLogo
ClickMap
ClickWebSite
DateAdded
PercentagePlayed
TagVideoPlayCount
TagVideoPlayTime
TagVideoTwitter
ListingVideoTwitter
TagVideoFacebook
ListingVideoFacebook
TagVideoPinterest
ListingVideoPinterest
ListingThumbnail
ListingScroll
TagVideoPlayButtonCount
UpdateFilterButtonClick
ClickTopSheetExtendImage
AccountID
ModelSelectClick
TrimSelectClick
ExteriorColorSelectClick
InteriorColorSelectClick
InventoryViewClick
ShareButtonClick
ZipCodeEntered
MapClickThrough
MenuOpen
SummaryClick
Misellaneous
BannerType
VPaidPreRollTime
VPaidPreRollCount
Mids
Ends
CreativeIsTrackable
CreativeWasViewable
) 

Give those columns appropriate types.

Then you will need cursor to iterate over rows in table #a:

Declare @BannerID int

declare cur cursor fast_forward for
select BannerID from #a

open cur

fetch next from cur into @BannerID

while @@FETCH_STATUS = 0
begin

insert into #tmp
exec spTest @BannerID

fetch next from cur into @BannerID
end

close cur
deallocate cur

The last step will be updating #a table from #tmp table:

update a set BannerName = t.BannerName, InterActionRate = t.InterActionRate
from #a a
join #tmp t on a.BannerID = t.BannerID

Now you have data updated in table #a.