0
    declare @us table(name nvarchar(100),lastname nvarchar(100),UID bigint,available bit);
        declare
        @Name nvarchar(100),
        @lastname nvarchar(100),
        @UID bigint,
        @Avail bit

        insert into @us
        select @name=name,@lastname=lastname,@UID=UID,@Avail=available from Users where available='1'
 select * from @us

I got this error

An INSERT statement cannot contain a SELECT statement that assigns values to a variable.

I searched for this problem but many people used queries like this and they said there is no problem! i'm using sql server 2012, is it a deference between MSSQL2012 and MSSQL2008? and what is the best solution if I want to return a table from my Stored Procedures? what is wrong in my query?

m.Khaki
  • 73
  • 3
  • 13
  • 2
    Why not just assign to variables first, and insert using variables later? Also, is it guaranteed that exactly 1 row is returned by the `select` query? – shree.pat18 Aug 05 '14 at 06:35
  • 1
    What are you expecting this to do if more than one row in `Users` has `available` equal to 1? – Damien_The_Unbeliever Aug 05 '14 at 06:47
  • @shree.pat18 , yes, this is my another problem, some times result is more that one row! what I have to do? – m.Khaki Aug 05 '14 at 06:55
  • Why do you need the variables exactly? From this snippet of code I do not see their use. – shree.pat18 Aug 05 '14 at 06:56
  • @Damien_The_Unbeliever what in is the solution in this case? – m.Khaki Aug 05 '14 at 06:57
  • 3
    Um, we can't *both* ask each other what the expected behaviour is in a particular scenario. I'm asking you what you *expect* to happen in this case. I don't know what the "solution" is because you haven't told me what you'd expect. – Damien_The_Unbeliever Aug 05 '14 at 06:59
  • @Damien_The_Unbeliever, I expect To find the users where they are 'Available', so it can be 0 row, 1 row or more than one rows. this query is in a store procedure I want to have table as a result. i don't want to send single parameters. I need a table. – m.Khaki Aug 05 '14 at 07:16

2 Answers2

3

Based on the information provided in the question, the variables seem to be unnecessary. You can directly do INSERT...SELECT like so:

declare @us table(name nvarchar(100),lastname nvarchar(100),UID bigint,available bit);

insert into @us
select name,lastname,UID,available 
from Users 
where available='1'

select * from @us
shree.pat18
  • 21,449
  • 3
  • 43
  • 63
  • I do this but it Didn't work before your answer! now it's working well! i'm so confused, but thank you so much.. – m.Khaki Aug 05 '14 at 07:20
1

Alternate and complex way of doing the same task is the below one. Hope nobody likes this. Just I am showing we have one more alternative way.

declare @us table(name nvarchar(100),lastname nvarchar(100),UID bigint,available bit);
declare @Name nvarchar(100), @lastname nvarchar(100), @UID bigint,@Avail bit

DECLARE ALLRECORDS CURSOR FOR 
    SELECT name,lastname,UID,available from Users where available='1'

     OPEN ALLRECORDS
     FETCH NEXT FROM ALLRECORDS INTO @Name,@lastname,@UID,@Avail

        WHILE @@FETCH_STATUS = 0
        BEGIN
        INSERT INTO @us    
        SELECT @Name,@lastname,@UID,@Avail 

        FETCH NEXT FROM ALLRECORDS INTO @Name,@lastname,@UID,@Avail
        END
CLOSE ALLRECORDS
DEALLOCATE ALLRECORDS
SELECT * FROM @us
pyborg
  • 166
  • 10