Temp Table and Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Temp Table and Stored Procedure

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2006-03-29 : 14:02:04

I take the information below in query analyzer and everything runs fine-returns 48 rows. I try to run it as a stored proc and I get no records. If I put a simple select statement into the stored proc, it runs, so it's not permissions. Can anyone help me with why this won't execute as a stored procedure? Articles seem to indicate you can do this with temp tables in a stored procedure. Thanks

declare
@style as int,
@disc as int,
@key as varChar(500),
@sdate as varChar(15),
@edate as varChar(15),
@ld as varChar(15)

set @style=0
set @disc=0
set @sdate='3/1/2006'
set @ld='2'

create table #ListAll (wid int, parentID int, myFlag int)

insert into #ListAll
SELECT top 100 percent wid, parentID, 0 as myFlag FROM myTable WHERE (@style=0 or styleID=@style)
and (@edate is null or start_date < @edate)
and ((start_date is null) or (datediff(day,start_date,@sdate) <1))
and (@ld='9' or charIndex(convert(varchar(1),datepart(dw,start_date)),@ld)>0)
and wid in (select wid from myTable2 where (@disc=0 or discID=@disc))
and wid in (select wid from myTable where @key is null or ([title] like '%' + @key + '%' or [keywords] like '%' + @key + '%'))

update #ListAll set myFlag=1 where parentID<>0
insert into #ListAll
select w.wid, w.parentID, 0 as myFlag from myTable w right join #ListAll on #ListAll.parentID=w.wid where #ListAll.parentID<>0
delete #ListAll where myFlag=1

SELECT top 100 percent srt, w.WID, w.parentID, w.[title], w.start_date, w.end_date, w.cancelled, w.url, styleID, w.[keywords], w.onlineID, w.httplocation, datepart(dw,w.start_date) as lddate
FROM myTable w
right join #ListAll on #ListAll.wid=w.wid
ORDER BY srt, start_date, [title]

drop table #ListAll
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 14:10:13

I see no reason why this won't work.
Post the code you used to "run it as a stored proc" as well as the code to execute the procedure.

Be One with the Optimizer
TG

janetb
Yak Posting Veteran

71 Posts

Posted - 2006-03-29 : 14:39:29

Ummmm, the article I was referencing had much ado about yaks. Any relation? Thanks for the fast reply.

Crud! Found it - must set nocount on in stored procedure for the code to run. Only change and everything works fine.

jhermiz

3564 Posts

Posted - 2006-03-29 : 14:43:33

That is the 4th SET NOCOUNT ON problem I have seen this week, what is up with that :-p.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 14:53:52

>>must set nocount on in stored procedure for the code to run
The code will run either way (on or off)

You probably are only looking at it through a datareader or something where you need to readNext to the subsequent result sets.

For anyone developing sql code for use with applications would do themselves a favor if they got the sql side working in Query Analyzer first, then when all looks good call it from the front end.

Be One with the Optimizer
TG

janetb
Yak Posting Veteran

71 Posts

Posted - 2006-03-30 : 09:17:00

Ummm, Yak-Yak, read the first post. I had developed it in Query Analyzer and it did work fine. But, when I copy/pasted to a Stored Proc and tried to use in a web page, the SP wouldn't work. The only change I made was to add the nocount setting and the web page worked fine. I can't remember the website I found referencing the nocount setting and stored procedures - I was searching temp tables vs. table variables before I realized table variables were 2005.

That help clarify things at all?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-30 : 09:35:41

>>I was searching temp tables vs. table variables before I realized table variables were 2005.
table variables are also in 2K.

>>The only change I made was to add the nocount setting and the web page worked fine
I was only suggesting a reason that set nocount on fixed your problem. With nocount set off, "(n row(s) affected)" messages can be treated as a sepeate result sets before your intended result set is returned. So it's not that the "code" didn't run" its just that all the code looked at was the first result set which contained nothing but, "(n row(s) affected)"

>>"Yak-Yak"??
What are you saying I'm talking too much?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources