Problems with Dynamic Query from Newbie

Thanks for the help thus far. I'm still having problems
Below are the error message.
I can run the print statement noted below and it works.
I can also just do the select statement but placing the insert into breaks
the procedure.
Questions.
1. What am I doing wrong.
2. I need to output the @.TempVS variable to use. How is that done?
3. Can I do a Select * from @.VStemp after the table has been inserted?
4/ Any other way to optimize this code?
The query below works when I substitute values
(Insert Into @.Level3
Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.level and (Result <> 'DNR' ) and Result
<>''
and (DateDrawn > (GetDate() - @.Level2)
)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Insert'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.Level3'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near ')'.
/****** Object: Table [dbo].[TempVsTest] Script Date: 5/7/2006 3:02:18
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TempVSTEst]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TempVSTest] (
[patNo] [int] NULL ,
[DateDrawn] [datetime] NULL ,
[Sort] [int] NULL ,
[TestnameDisplay] [varchar] (50) NULL ,
[Result] [varchar] (100) NULL ,
[Units] [varchar] (50) NULL ,
[abn] [varchar] (50) NULL ,
[Range] [varchar] (50) NULL
) ON [PRIMARY]
END
Insert Into TempVSTest
(patNo, DateDrawn, Sort, TestnameDisplay, Result, Units,abn, Range)
values
(240, '1/1/2005', 1,'Cholesterol', '300', 'mg/dl','H','<200')
GO
/* Test script */
DECLARE
@.TempVS varchar(20),
@.table Varchar(20)
-- @.PatNo int,
-- @.Type int,
-- @.Range int
-- @.sqlStmt nvarchar(500)
Set @.table = 'TempVSTest'
--Set @.Type = 0
--Set @.Range = 36000
--Make unique Global Temp Table Name
set @.TempVS = '##TempVS' + cast(datepart(minute, getdate()) as
varchar(3)) + cast(datepart(millisecond, getdate()) as varchar(3))
--print @.TempVS
EXEC ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
--EXEC ('Select * from '+ @.TempVS)
--IF @.Type = 0 -- All
--BEGIN
Declare @.sqlStmt nvarchar(500)
Declare @.Param1 NVARCHAR(500)
Declare @.Param2 NVARCHAR(500)
Declare @.PatNo int
Declare @.Range int
set @.PatNO = 1
Set @.Range = 120
SET @.sqlStmt = N'(Insert Into @.Level3
Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.level and (Result <> ''DNR'' ) and Result
<>''''
and (DateDrawn > (GetDate() - @.Level2))
)'
print @.sqlStmt
SET @.Param1 = N'@.level int, @.level2 int, @.Level3 varchar(20)'
EXECUTE sp_Executesql @.sqlStmt,
@.Param1,
@.level = @.PatNO ,
@.level2 = @.Range,
@.level3 = @.TempVSHi
I am not sure what the braces are at the start and end of the statement! You
can not use a variable as the table name in an insert statement, you will
need to build up the string in the same way as you use @.TempVS in EXEC
('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
It is not clear why you are going to the trouble of using a global temporary
table, your code should work with a temporary table, and therefore remove th
e
need to substituting the table name.
John
"Stephen K. Miyasato" wrote:

> Thanks for the help thus far. I'm still having problems
> Below are the error message.
> I can run the print statement noted below and it works.
> I can also just do the select statement but placing the insert into breaks
> the procedure.
> Questions.
> 1. What am I doing wrong.
> 2. I need to output the @.TempVS variable to use. How is that done?
> 3. Can I do a Select * from @.VStemp after the table has been inserted?
> 4/ Any other way to optimize this code?
> The query below works when I substitute values
> (Insert Into @.Level3
> Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.level and (Result <> 'DNR' ) and Result
> <>''
> and (DateDrawn > (GetDate() - @.Level2)
> )
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Insert'.
> Server: Msg 137, Level 15, State 1, Line 2
> Must declare the variable '@.Level3'.
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near ')'.
>
> /****** Object: Table [dbo].[TempVsTest] Script Date: 5/7/2006 3:02:18
> PM ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TempVSTEst]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [TempVSTest] (
> [patNo] [int] NULL ,
> [DateDrawn] [datetime] NULL ,
> [Sort] [int] NULL ,
> [TestnameDisplay] [varchar] (50) NULL ,
> [Result] [varchar] (100) NULL ,
> [Units] [varchar] (50) NULL ,
> [abn] [varchar] (50) NULL ,
> [Range] [varchar] (50) NULL
> ) ON [PRIMARY]
> END
> Insert Into TempVSTest
> (patNo, DateDrawn, Sort, TestnameDisplay, Result, Units,abn, Range)
> values
> (240, '1/1/2005', 1,'Cholesterol', '300', 'mg/dl','H','<200')
> GO
> /* Test script */
> DECLARE
> @.TempVS varchar(20),
> @.table Varchar(20)
> -- @.PatNo int,
> -- @.Type int,
> -- @.Range int
> -- @.sqlStmt nvarchar(500)
> Set @.table = 'TempVSTest'
> --Set @.Type = 0
> --Set @.Range = 36000
> --Make unique Global Temp Table Name
> set @.TempVS = '##TempVS' + cast(datepart(minute, getdate()) as
> varchar(3)) + cast(datepart(millisecond, getdate()) as varchar(3))
> --print @.TempVS
> EXEC ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
> --EXEC ('Select * from '+ @.TempVS)
> --IF @.Type = 0 -- All
> --BEGIN
>
> Declare @.sqlStmt nvarchar(500)
> Declare @.Param1 NVARCHAR(500)
> Declare @.Param2 NVARCHAR(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 1
> Set @.Range = 120
> SET @.sqlStmt = N'(Insert Into @.Level3
> Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.level and (Result <> ''DNR'' ) and Result
> <>''''
> and (DateDrawn > (GetDate() - @.Level2))
> )'
> print @.sqlStmt
> SET @.Param1 = N'@.level int, @.level2 int, @.Level3 varchar(20)'
> EXECUTE sp_Executesql @.sqlStmt,
> @.Param1,
> @.level = @.PatNO ,
> @.level2 = @.Range,
> @.level3 = @.TempVS
>
>|||> It is not clear why you are going to the trouble of using a global
> temporary
> table, your code should work with a temporary table, and therefore remove
> the
> need to substituting the table name.
I need the table to build a array similar to crosstabs. I then had problems
with multi-user use and other persons data coming into the tables. I thought
that using global temp tables would be the answer to this. Any comments?
Thanks
Stephen K. Miyasato
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:881223D1-5B5E-45BB-B154-940BF5A4DE2E@.microsoft.com...
> Hi
> I am not sure what the braces are at the start and end of the statement!
> You
> can not use a variable as the table name in an insert statement, you will
> need to build up the string in the same way as you use @.TempVS in EXEC
> ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
> It is not clear why you are going to the trouble of using a global
> temporary
> table, your code should work with a temporary table, and therefore remove
> the
> need to substituting the table name.
>
> John
>
> "Stephen K. Miyasato" wrote:
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I need the table to build a array similar to crosstabs. I then had
> problems with multi-user use and other persons data coming into the
> tables. I thought that using global temp tables would be the answer to
> this. Any comments?
Use a regular temp table instead. A plain #temp table is visible only
to the process that created it.
Global temp tables are very messy to work with. And I have a strong
feeling that it was a design mistake that brought them into the product.
I've yet found any useful application for them.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
For static crosstabs you should not need to use a temporary table
http://msdn.microsoft.com/library/d...r />
_04j7.asp
or use the new PIVOT function in SQL 2005, for dynamic crosstabs you may wan
t
to look at Itziks articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/Arti...5608/15608.html
John
"Stephen K. Miyasato" wrote:

> I need the table to build a array similar to crosstabs. I then had problem
s
> with multi-user use and other persons data coming into the tables. I thoug
ht
> that using global temp tables would be the answer to this. Any comments?
> Thanks
> Stephen K. Miyasato
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:881223D1-5B5E-45BB-B154-940BF5A4DE2E@.microsoft.com...
>
>