dwaldner
asked on
Stored Procedure Help!
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/conn ection.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
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/conn
<%
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm running off of SQL Server 7.0 (shrug) :(
ASKER
BRILLIANT!!! It didn't like the idea of a certain type of temp table! Thanks a bunch!
Cheers,