Cursors and Strings to Create a table with two columns

[reap76]

I am trying to create a table that has two columns. If possible I'd like to do that usign two seperate INSERT statements. I am a complete rookie at sql and am learning as I go along. So, please excuse my syntax and statement flow. However, I am getting the following error: "Must pass parameter number 4 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'." AND "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations." I believe the latter error is because I am doing two insert statements within the exec @stmt, however, I have been battling this all day.

DECLARE @dbname varchar(256)
 
Create table #Users (
TraderNum nvarchar(1024),
FirmName nvarchar(1024)
)
 
DECLARE db_nm CURSOR
for select distinct Name from TransactionDB where Name like 'BooksRecords%' and Active=1
 
OPEN db_nm
 
fetch next from db_nm into @dbname
 
declare @stmt nvarchar(1024)
DECLARE @FirmID nvarchar(32)
DECLARE @UserNum nvarchar(32)
 
while @@fetch_status=0
 
begin
 
select @stmt = 'Insert into #Users '+ 'VALUES (@UserNum,@FirmID)'--'Insert into #LavaUsers (TraderNum,FirmName) ' + 'select count(*) As NumUsers from ' + @dbname + '..FirmTrader ' + 'where Active=1' --, 'Insert into #LavaUsers (FirmName) '+ 'select FirmID from ' + @dbname + '..Firm' 
exec sp_executesql @stmt, N'@UserNum nvarchar(32) output', @UserNum = @UserNum output, N'@FirmID nvarchar(32) output', @FirmID = @FirmID output
 
fetch next from db_nm into @dbname
 
end
close db_nm
deallocate db_nm
 
select * from #Users
drop table #Users
 

[iburyak]

I have couple of questions:
1.
How do you change or just get values in following variables?

[PHP]@UserNum
@FirmID[/PHP]

When you just declare them they are both NULL.

2. Why use sp_executesql?
You can just do straight insert like this:

[PHP]Insert into #Users VALUES (@UserNum,@FirmID)[/PHP]


Irina.