I did some quick testing on QV and stored procs and was able to use temp tableswithout any issues, see below for my simple script.
Here is what I did discover:
- - QV stops at the first select statement that itencounters, so in the case I have below it shows the select of the temp tablebut not the data from the select off the POS$ table ( a physical table)
- - Deletes and updates have no effect on QV andexecute as expected
- - If you do not set NOCOUNT on QV stops at thefirst message(which may be what you are encountering)
USE [QVDemo]
GO
/****** Object: StoredProcedure[dbo].[spQV] Script Date: 04/01/2012 10:45:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=============================================
--Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--=============================================
ALTER PROCEDURE [dbo].[spQV]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE[#tmp_Response]
(
[SurveyKey] [INTEGER] NULL
);
INSERT INTO[#tmp_Response] ([SurveyKey]) values(1)
INSERT INTO[#tmp_Response] ([SurveyKey]) values(2)
INSERT INTO[#tmp_Response] ([SurveyKey]) values(3)
delete from[#tmp_Response] where [SurveyKey] = 3
update [#tmp_Response] set[SurveyKey] = 3 where[SurveyKey] = 1
select * from [#tmp_Response]
SELECT* from dbo.POS$
select * from [#tmp_Response]
END
