November 29, 2007 at 8:37 am
hello,
i'm scratching my head trying to fix a SQL 2005 stored procedure. It works fine in Query Analyzer but doesn't return a result set when i call it from my ASP script.
The idea I have is I want to do a select statement and put the results in a temp table. Then I want to run a select statement using the temp table. However, I don't get a result set from script.
I tried writing a stored proc with a temp table. Then I tried it using a table variable. Then I tried to separate the stored proc into two procs, all to no avail.
With the table variable, the (simplified) first version of my stored proc resembles this:
create proc sp_Find_Duplicates
@vAgencyID1 int,
@vAgencyID2 int
as
--start out by putting data into the table variable
declare @Temp Table
(AlphaNumericID varchar(12),
myCount int)
insert into @Temp
select
tblClients.AlphaNumericID,
count(tblClients.AlphaNumericID) from
tblTreatment_Records
left join tblClients
on tblTreatment_Records.Client_ID=tblClients.ID
where (tblTreatment_Records.clinic_Id=@vAgencyID1 or
tblTreatment_Records.clinic_ID=@vAgencyID2)
group by
tblClients.AlphaNumericID
having count(tblClients.AlphaNumericID)>1
--now for the select statement using the temp table
select tblTreatment_Records.Clinic_ID,
tblClients.Client_First_Name,
tblClients.Client_Last_Name,
tblClients.AlphanumericID from
tblTreatment_Records
left join tblClients
on tblTreatment_Records.Client_ID=tblClients.ID
where
(tblTreatment_Records.clinic_Id=@vAgencyID1 or
tblTreatment_Records.clinic_ID=@vAgencyID2)
and
( tblCLients.AlphanumericID in
(select AlphanumericID from @Temp))
order by tblClients.AlphanumericID,
tblTreatment_Records.clinic_ID
(If the code above doesn't parse it is because I'm writing it from recent memory.)
So, why can't I first put data into a table variable, then run a select statement that uses the table variable and have output delivered to an ASP script? Again, i get the output I expect when i put this proc in query analyzer.
thanks!
November 29, 2007 at 9:12 am
Try placing a SET NOCOUNT ON at the start of the proc to eliminate the row count status messages. Sometimes confuses client side components.
Also, do you need the temp table/variable ? Why not join to a derived table that contains the same resultset that would be in the temp table ?
November 29, 2007 at 9:15 am
If your code returns a result set in QA, the fact that your ASP app is not getting results has nothing to do with what type of temporary structure you are using in your code. I can't tell you why your ASP app is not able to read the result set, but I can assure you it has nothing to do with the queries that you posted. To prove this, take your SELECT statement that you are using to populate your table variable and use it as a derived table in your second select. Your SP will produce the same results.
November 29, 2007 at 9:27 am
Why not join to a derived table that contains the same resultset that would be in the temp table ?
I have tried that. Another variation i've tried is similiar to:
create proc sp_my_Proc
--fill data in a table. this isn't a temp table; it shows up in enterprise manager.
exec Proc_Work;
--run a select statement that uses that table
select [Stuff] where
[field] is in (select [field] from table)
...
for some reason i don't get a result set in ASP when I execute the "Proc_Work" stored procedure within the "sp_my_Proc" procedure. It works in query analyzer, though.
naturally, if i remove the step where i call the procedure that fills the table, i do get a result set. So, it doesn't appear to be a problem with how i'm calling the stored procedure from ASP.
thanks for the quick reply. 🙂
November 29, 2007 at 9:47 am
PW is probably on the right track. Inserting into a temporary table or a table variable returns rowcount information. In many cases, this will actually cause your select to be returned as the second result set in your recordset object.
You either need to change the code in your ASO.Net application to move to the first recordset that actually has records in it, or (probably easier) put SET NOCOUNT ON at the top of your procedure.
December 4, 2007 at 8:18 am
PW, it worked!
I really appreciate your response. I also appreciate the responses from everyone else.
(Sorry i disappeared for a few days. I got taken off my task and had to do something else.)
this is really an excellent forum. Thanks again, for the help.
December 5, 2007 at 8:13 am
Just something of an aside really...
Microsoft recommend that people don't name stored procedures sp_anything. See http://msdn2.microsoft.com/en-us/library/ms190669.aspx#sectiontoggle0.
Apparently the search algorithm looks for sp_anything as a system procedure first. Qualifying it doesn't help so, for example, 'exec sp_who', 'exec dbo.sp_who' and 'exec sys.sp_who' all work and all call the same system procedure irrespective of whether dbo.sp_who exists or not.
Derek
December 7, 2007 at 8:56 am
yes, i'm familiar with Microsoft's suggestions regarding naming conventions. It seems like they waited until there were millions of databases out there where developers named their procs "sp_" before announcing this isn't the way to go.
however, our company doesn't have the time to invest in modifying the stored proc names. that's why i'm sticking with it for now.
thanks for the info.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply