469,313 Members | 2,577 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,313 developers. It's quick & easy.

Cursors and Strings to Create a table with two columns

2
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.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @dbname varchar(256)
  2.  
  3. Create table #Users (
  4. TraderNum nvarchar(1024),
  5. FirmName nvarchar(1024)
  6. )
  7.  
  8. DECLARE db_nm CURSOR
  9. for select distinct Name from TransactionDB where Name like 'BooksRecords%' and Active=1
  10.  
  11. OPEN db_nm
  12.  
  13. fetch next from db_nm into @dbname
  14.  
  15. declare @stmt nvarchar(1024)
  16. DECLARE @FirmID nvarchar(32)
  17. DECLARE @UserNum nvarchar(32)
  18.  
  19. while @@fetch_status=0
  20.  
  21. begin
  22.  
  23. 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' 
  24. exec sp_executesql @stmt, N'@UserNum nvarchar(32) output', @UserNum = @UserNum output, N'@FirmID nvarchar(32) output', @FirmID = @FirmID output
  25.  
  26. fetch next from db_nm into @dbname
  27.  
  28. end
  29. close db_nm
  30. deallocate db_nm
  31.  
  32. select * from #Users
  33. drop table #Users
  34.  
Apr 5 '07 #1
1 1432
iburyak
1,017 Expert 512MB
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.
Apr 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Gustavo Randich | last post: by
6 posts views Thread by Shannan Casteel via AccessMonster.com | last post: by
17 posts views Thread by vishal | last post: by
2 posts views Thread by Martijn Mulder | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.