FIX: Stored Procedure Continues to Use Parameter Value Passed During First Execution

Article translations Article translations
Article ID: 288185 - View products that this article applies to.
This article was previously published under Q288185
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 101265 (SQLBUG_70)
Expand all | Collapse all

On This Page

SYMPTOMS

A stored procedure that accepts a parameter, which is used to determine how many columns to add to a temporary table that the stored procedure creates, might continue to use the value of the parameter passed the first time the procedure was executed, even though different values are passed for subsequent executions.

WORKAROUND

Create the procedure using a WITH RECOMPILE clause.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack SP3 for Microsoft SQL Server 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site:
http://www.microsoft.com/sql/prodinfo/previousversions/downloads/servicepacks.mspx
For more information, contact your primary support provider.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Run the script below.
  2. Modify the script, commenting out the three lines as indicated by the comment in the script.
  3. Run the script again.
USE tempdb
go
DROP PROCEDURE pr_TestProc
go
CREATE PROCEDURE pr_TestProc
 @Columns INT
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(1000)
-- Now remove the next three lines (which do nothing valuable) and try again.
CREATE TABLE #tmpJunk (JunkID INT)        -- COMMENT THIS LINE
INSERT #tmpJunk (JunkID) VALUES (0)       -- COMMENT THIS LINE
DROP TABLE #tmpJunk                       -- COMMENT THIS LINE
CREATE TABLE #tmpTbl (XX int)
SET @SQL = 'ALTER TABLE #tmpTbl ADD '
WHILE @Columns > 0
BEGIN
 SET @SQL = @SQL + '[Col' + CONVERT(VARCHAR(10), @Columns) + '] VARCHAR(20)'
+ ','
 SET @Columns = @Columns - 1
END
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
EXEC (@SQL)
SELECT * FROM #tmpTbl
go

exec pr_TestProc 7
exec pr_TestProc 3
exec pr_TestProc 12
go
				

Properties

Article ID: 288185 - Last Review: October 23, 2013 - Revision: 3.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB288185

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com