Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Stored Procedure Help!

Posted on 2004-08-24
4
245 Views
Last Modified: 2008-03-06
Ok, here's a doozy, that I don't know if anybody has encountered:

I have this T-SQL script:

DECLARE @results varchar(1024)
EXEC ids_to_names @id_list = '34, 35, 36', @results = ''
SELECT @results

What it does is take those numbers(34,35,36) and convert them to what their foreign key dictates.  It returns something like this :  Active Member, Passive Member, Non-Member.  

Here's the problem:  When I execute this is the SQL Query Analyzer, it works all hunky-dory.

When I try and execute this from an ASP page using this script:

<!--#include virtual="/connections/connection.asp"-->
<%
set rs = my_Conn.execute("DECLARE @results varchar(1024) EXEC ids_to_names @id_list = '34, 35, 36', @results = '' SELECT @results")
if not rs.eof then
  response.write rs(0)
else
  response.write "EOF"
end if
set rs = Nothing
%>

I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'ttemp'.

/common/sp_test.asp, line 3

But I don't get this error when I run it in the SQL Query Analyzer....weird.

NOTE: I DO create a temporary table called 'ttemp'...use it, then drop it...however, when I reload this page, it says "Table already exists"...I'm at a loss...

Any help would be greatly appreciated (and subsequently awarded :P)

PS:  Here's the SP:

ALTER PROCEDURE ids_to_names (
    @id_list    varchar(32),@results  varchar(1024)
)
AS
    DECLARE @query varchar(256), @name VARCHAR(32)
    SET @query = 'SELECT [membership types].[membership type english] INTO ttemp FROM [Membership types] WHERE [membership type id] IN (' + @id_list + ')'
    EXEC( @query )
    DECLARE temp_cursor CURSOR FOR
    SELECT ttemp.[membership type english] FROM ttemp
    OPEN temp_cursor
    FETCH NEXT FROM temp_cursor INTO @name
    WHILE @@FETCH_STATUS = 0
        BEGIN
        IF @results = ''
            SET @results = @name
        ELSE
            SET @results = @results + ', ' + @name
        FETCH NEXT FROM temp_cursor INTO @name
        END
    CLOSE temp_cursor
    DEALLOCATE temp_cursor
    DROP TABLE ttemp
    SELECT @results

Cheers,

Dan


0
Comment
Question by:dwaldner
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
darksinclair earned 500 total points
ID: 11884738
0
 
LVL 5

Expert Comment

by:darksinclair
ID: 11884760
oh, and I have had this error before and that fixed me up.  

Cheers,
0
 
LVL 3

Author Comment

by:dwaldner
ID: 11884801
I'm running off of SQL Server 7.0 (shrug) :(
0
 
LVL 3

Author Comment

by:dwaldner
ID: 11884866
BRILLIANT!!!  It didn't like the idea of a certain type of temp table!  Thanks a bunch!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Update field in order 21 148
Import csv files to MS SQL 5 85
Need to rewrite code for checking if a file exists 3 69
Issues with Insert statement 12 29
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question