insert calculated field in Temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

insert calculated field in Temp table

Author

Topic

jn-at-uk
Starting Member

20 Posts

Posted - 2005-02-13 : 12:57:00

Hi, I created a temp table & want to insert 2 calculated fields in the temp table. I get an error : Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@nField1'. for some reason @Field1 seems to lose its value.
This is my sp:

------------------------------------------------------------
Create table #tmpNoConv(
field1 int, field2 int)

DECLARE @nField1 varchar(9),@sqlVisits nvarchar(200)
SELECT @sqlVisits=N'select count(*) as noOfVisits from Table1'
EXEC sp_executesql2 @sqlVisits, N'@nField1 varchar(9) OUTPUT', @nField1 OUTPUT

DECLARE @nField2 varchar(9),@sqlVisits nvarchar(200)
SELECT @sqlVisits2=N'select count(*) as noOfVisits from Table2'
EXEC sp_executesql2 @sqlVisits2, N'@nField2 varchar(9) OUTPUT', @nField2 OUTPUT

Select @Insert ='Insert into #tmpNoConv(field1,field2)'
Select @Insert = @Insert + ' values (@nField1, @nField2)'
@nVisits OUTPUT
exec(@Insert)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-13 : 13:08:32

you probably need:
SELECT @sqlVisits=N'select @nField1 = count(*) from Table1'
SELECT @sqlVisits2=N'select @nField2 = count(*) from Table2'

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-13 : 13:22:35

Create table #tmpNoConv(field1 int, field2 int)

DECLARE @sql nvarchar(200)
DECLARE @nField1 int
SELECT @sql = N'select @nField1 = count(*) from Table1'
EXEC sp_executesql @sql, N'@nField1 int OUTPUT', @nField1 OUTPUT

DECLARE @nField2 int
SELECT @sql = N'select @nField2 int = count(*) from Table2'
EXEC sp_executesql @sql, N'@nField2 int OUTPUT', @nField2 OUTPUT

insert #tmpNoConv(field1,field2) select @nField1, @nField2

You know that you don't need any dynamic sql for that?
DECLARE @nField1 int, @nField2 int
select @nField1 = count(*) from Table1
select @nField2 = count(*) from Table2
insert #tmpNoConv(field1,field2) select @nField1, @nField2

you can also do it without the variables.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jn-at-uk
Starting Member

20 Posts

Posted - 2005-02-13 : 13:52:03

Thanku all, that resolved my problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources