Storing Result Of A Select Statement In A Variable

[MadDashCoder]

Hi everyone, I have a temporary table as shown below

tempTable(ID int not null identity(1,1) primary Key, Col1 int, Col2 int, Col3 int)

I am trying to get data of each column, store them in a variable, and manipulate them as I loop through the table using while loop. The following is what I have tried

create proc [dbo].[spGetData]
@Col1 int
@Col2 int
@Col3 int
as begin
declare @count int
declare @currentColumn varchar(25)
declare @currentColumnVal varchar(25)
set @count = 1
set @currentColumn = NULL
set @currentColumnVal = NULL
create #tempTable(ID int not null identity(1,1) primary Key, Val1 int, Val2 int, Val3 int)
insert #tempTable(Val1, Val2, Val3) Values(@Col1, @Col2, @Col3)
set @count = 1

while @count < 3
begin
set @currentColumn = 'Val' + CAST(@count AS VARCHAR(25))
select @currentColumnVal = @currentColumn from #tempTable
-- Do stuff
-- Do more Stuff
set @count = @count + 1
end

print @currentColumnVal -- Just to see what the value of the last Column is

end

I'm not getting any errors but I'm getting 0 as the return value which is incorrect.

[CHill60]

What were you expecting? There is no final select in this SP. In fact you can't create the SP because there are errors reported. If I fix the errors ... commas after @Col1 int and @Col2 int and create TABLE #tempTable the Stored Procedure is created and when I run it, it prints "Val2" - which is what I would expect given what you have in the body of the SP. Are you actually trying to use dynamic sql? Have a look at this CP article - Building Dynamic SQL In a Stored Procedure[^] You also need to understand how to return values from Stored Procedures - see MSDN article[^]

[Anudeep Jaiswal MCA]

--[Spgetdata] 1,2,3 ALTER PROC [dbo].[Spgetdata] @Col1 INT, @Col2 INT, @Col3 INT AS BEGIN DECLARE @count INT DECLARE @currentColumn NVARCHAR(25) DECLARE @currentColumnVal NVARCHAR(25) SET @count = 1 SET @currentColumn = NULL SET @currentColumnVal = NULL CREATE TABLE #tempTable ( ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, Val1 INT, Val2 INT, Val3 INT ) INSERT #tempTable (Val1, Val2, Val3) VALUES(@Col1, @Col2, @Col3) SET @count = 1 WHILE @count <= 3 BEGIN SET @currentColumn = 'Val' + Cast(@count AS VARCHAR(25)) --SELECT @currentColumnVal = @currentColumn --FROM #tempTable DECLARE @Q NVARCHAR(Max) SET @Q = 'SELECT @currentColumnValX = ' + @currentColumn + ' FROM #tempTable' EXEC Sp_executesql @Q, N'@currentColumnValX varchar(500) Output', @currentColumnVal Output -- Do stuff -- Do more Stuff SET @count = @count + 1 END PRINT @currentColumnVal -- Just to see what the value of the last Column is END