While statement question
-
I'm using the following while statement to parse through some requests that I have for remote offices. This particular while statement dynamically builds a stored procedure call which uses various inputs to place the requests into the correct files,etc.
What ends up happening for me though is that I end up with my lists containing duplicates. As I search through my code and run each step individually I find that there is no risk of duplicating my data. It seems to only happen when I try to automate the request process and my automation piece is based on this while statement submitting the stored procedure.
My first question would be, in this format, would SQL basically continue through the cycling process without waiting on the stored procedure to be finished processing? IE submits the stored procedure, and then continues onward? And if this is something that might be happening, what might the best way to resolve it be? Wrapping the stored procedure call in an transaction in order to force SQL to wait? Or is there better options?
Thanks.
declare @Dept varchar(10),@Project varchar(10),@State varchar(2),@Request varchar(10),@Office varchar(10),
@Cycle int,@SQL nvarchar(2000)
set @Cycle=43
while @Cycle>0
begin
set @Dept=(select rtrim(Dept) from Leadrequest where Cycle=@Cycle)
set @Project=(select rtrim(Project) from Leadrequest where Cycle=@Cycle)
set @State=(select rtrim(State) from Leadrequest where Cycle=@Cycle)
set @Request=(select rtrim(Request) from LeadRequest where Cycle=@Cycle)
set @Office=(Select rtrim(Office) from Leadrequest where Cycle=@Cycle)
set @sql='exec Processcold '''+@Dept+''','''+@Project+''','''+@State+''','''+@Request+''','''+@Office+''''
exec sp_executesql @sql
set @Cycle=@Cycle-1
end
-
I assume that the sp Processcold changes data in the Leadrequest table. Can you post that code? I have never heard of SQL doing asynchronous execution of sps, but you can satisfy yourself that it isn't by capturing a return value from the sp into a variable:
- exec @variable = sp_executesql @sql
- --debug code only:
- print @variable
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Tim, here is the code that the while statement is calling and as you can see with the dynamic SQL of @SQLU being built, while something as simple as an inner join should suffice, for whatever reason the two tables just simply won't work right, therefore I was relegated to updating a single record at a time so bear with me.
ALTER Procedure ProcessCold
(
@Dept varchar(10),
@Project varchar(10),
@State varchar(2),
@Request varchar(10),
@Office varchar(10)
)
as
declare @sql nvarchar(2000),@SQLU nvarchar(2000),@SameCharityDate datetime,@DiffcharityDate datetime
declare @Distribution varchar(10),@SameCharity int,@DiffCharity int,@Countermax int,@Countermin int,@Phone varchar(10)
set @Distribution=(select convert(varchar(10),getdate(),101))
set @SameCharity=180
set @DiffCharity=60
set @SameCharityDate=(select (getdate()-@SameCharity))
set @DiffCharityDate=(select (getdate()-@DiffCharity))
set @Dept=rtrim(@Dept)
set @State=rtrim(@State)
set @Project=rtrim(@Project)
set @Request=rtrim(@Request)
set @Office=rtrim(@Office)
set @sql='Insert into Import'+@Office+' (Phonenum,Title,Fname,Lname,Address,City,State,Zip,Dept,SaleType,Projectid)
select top '+@Request+' PhoneC3,Title,Fname,Lname,Address,City,State,Zip,'''+@Dept+''',75,'''+@Project+'''
from Cold_'+@State+' with (nolock) where [LC_'+@Dept+']<'''+convert(varchar(10),@SameCharityDate,101)+'''
and [LastDistributed]<'''+convert(varchar(10),@DiffCharityDate,101)+'''
and Available=1 order by Sort,Attempts,['+@Dept+'_Attempts]'
exec sp_executesql @sql
If @Office=5
begin
set @Countermax=(select max(DialSeed) from Import5 I where I.Dept=@Dept and I.State=@State)
set @Countermin=(select min(DialSeed) from Import5 I where I.Dept=@Dept and I.State=@State)
end
else
If @Office=6
begin
set @Countermax=(select max(DialSeed) from Import6 I where I.Dept=@Dept and I.State=@State)
set @Countermin=(select min(DialSeed) from Import6 I where I.Dept=@Dept and I.State=@State)
end
else
If @Office=2
begin
set @Countermax=(select max(DialSeed) from Import2 I where I.Dept=@Dept and I.State=@State)
set @Countermin=(select min(DialSeed) from Import2 I where I.Dept=@Dept and I.State=@State)
end
while @Countermax>=@CounterMin
begin
set @Phone=(select Phonenum from Import5 where <A href="mailto
ialseed=@CounterMax">Dialseed=@CounterMax)set @SQLU='Update CS set [LC_'+@Dept+']='''+@Distribution+''',['+@Dept+'_Attempts]=['+@Dept+'_Attempts]+1,
CS.Attempts=CS.Attempts+1,Available=0,LastDistributed='''+@Distribution+'''
from [Cold_'+@State+'] CS,Import'+@Office+' I where CS.PhoneC3='''+@Phone+''' and I.Dept='''+@Dept+''' and I.State='''+@State+''''
exec sp_executesql @SQLU
set @CounterMax=@CounterMax-1
end
-
Somedays it helps to proofread code I think.
One problem I just found in the proc is my set @Phone=(select phonenum from line).
I'm working on changing it currently and will re-test to see if maybe this was my entire problem.
-
And I just noticed that you use
- set @sql='exec Processcold ''' + @Dept + ''',''' + @Project + ''',''' + @State + ''',''' + @Request + ''',''' + @Office + ''''
exec sp_executesql @sql
instead of
exec Processcold @Dept, @Project, @State, @Request, @Office
The only reason I can think of for doing this is as an indirect way of trapping nulls,
but if so, a more explicit null check would aid debugging and maintenance
as well as not being dependent on the setting of CONCAT_NULL_YIELDS_NULL.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant - set @sql='exec Processcold ''' + @Dept + ''',''' + @Project + ''',''' + @State + ''',''' + @Request + ''',''' + @Office + ''''
-
Actually I use that to make sure that when the procedure is called it goes with quotes around the fields to ensure that since they are all based as varchar's that there will be no problems.
The readout on the stored proc call would look like:
exec processcold 'ABC','24','CA','25000','5'
It just seemed cleaner to me to make sure that each field was quoted, although I honestly haven't tried to simply shortcut it and do your way.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply