Temp tables in stored proc

  • 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

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

  • That makes no sense. All NoCount does is remove the line X Rows affected which SQL by default returns for all queries.

    Where were you running the stored procedure from? Query analyser? If not, try running the procedure from Query Analyser so that you can see exactly what it returns (both with nocount on and without that line.)

    Also note that you don't need to specify TOP 100% in your queries. In fact you shouldn't specify top unless you only want a portion of the result set.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply