472,145 Members | 1,799 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

Cursors and Strings to Create a table with two columns

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)
  3. Create table #Users (
  4. TraderNum nvarchar(1024),
  5. FirmName nvarchar(1024)
  6. )
  9. for select distinct Name from TransactionDB where Name like 'BooksRecords%' and Active=1
  11. OPEN db_nm
  13. fetch next from db_nm into @dbname
  15. declare @stmt nvarchar(1024)
  16. DECLARE @FirmID nvarchar(32)
  17. DECLARE @UserNum nvarchar(32)
  19. while @@fetch_status=0
  21. begin
  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
  26. fetch next from db_nm into @dbname
  28. end
  29. close db_nm
  30. deallocate db_nm
  32. select * from #Users
  33. drop table #Users
Apr 5 '07 #1
1 1527
1,017 Expert 512MB
I have couple of questions:
How do you change or just get values in following variables?


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]

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
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.