Stack Exchange
Execute a SQL Statement that Returns a Single Value
I have a SQL select statement like this:
 SET @list = 'TEST,TEST1'
 SET @Delimiter = ',';
 SET @list =  @list + @Delimiter;

 SET @listIndexTemp = CHARINDEX(',',@list);  
 WHILE(@listIndexTemp > 0)
 BEGIN
    SET @listTemp = SUBSTRING(@list, 1,  @listIndexTemp -1);
    DECLARE @string VARCHAR(MAX)

    SET @string = 'SET @listTempUid = SELECT id FROM ['+CAST(@database as varchar(50))+'].[dbo].[MAIN] WHERE [name] = '''+CAST(@listTemp as varchar(100))+'''';
    EXEC (@string);

    INSERT INTO #Roles (uid, name) VALUES(@listTempUid, @listTemp);

    SET @list = SUBSTRING(@list, @listIndexTemp+1,  LEN(@list));
    SET @listIndexTemp = CHARINDEX(',',@list);

 END

For my case the above query is in a loop. Therefore, I have to assign the returned value from the above query statement 'string' to a SQL variable 'listTempUid' for each iteration. 
The above query is not working. I have to add id and name into a temporary table for the corresponding name in the input list.
Please help

Possible post your procedure here.

updated code. Hope you can understand!

So what do you want?

The above query is not working.

This can be achieved by using EXECUTE SP_EXECUTESQL instead of EXEC. For using SP_EXECUTESQL you need to declare @string as NVACHAR. In the dynamic query, you have to assign value using SELECT instead of SET statement. And for getting value from Dynamic Query you have to use OUT parameter.
Try something like this.
 SET @list = 'TEST,TEST1'
 SET @Delimiter = ',';
 SET @list =  @list + @Delimiter;

 SET @listIndexTemp = CHARINDEX(',',@list);
 WHILE(@listIndexTemp > 0)
 BEGIN
    SET @listTemp = SUBSTRING(@list, 1,  @listIndexTemp -1);

    DECLARE @string NVARCHAR(MAX)

        SET @string = 'SELECT @TempID = id FROM ['+CAST(@database as
           varchar(50))+'].[dbo].[MAIN] WHERE [name] = '''+CAST(@listTemp as
           varchar(100))+'''';

            EXECUTE SP_EXECUTESQL @string, N'@TempID INT OUT', @listTempUid OUT

    INSERT INTO #Roles (uid, role) VALUES(@listTempUid, @listTemp);

    SET @list = SUBSTRING(@list, @listIndexTemp+1,  LEN(@list));
    SET @listIndexTemp = CHARINDEX(',',@list);

 END

