SQL Server: set 2 column into 1 variable from variable table

Question

Can someone check what is wrong with this codes? I already check the other questions for reference but its still not working.

declare @sourceTable varchar(500)
declare @year varchar(22)
declare @month varchar(3)
declare @test varchar(12)
declare @result varchar(8)
declare @index int

declare @string varchar(15)
set @string = (SELECT DISTINCT TOP 1 REPLACE(dbo.fn_Parsename(WHOLEROW, '|', 0), CHAR(9), '') FROM #temp1)
set @test = (select UPPER(convert(datetime,substring(@string,2,charindex('-',@string,1)-2))))
set @month =(left(@test,3))

set @year = (right(@test,5))

set @result = @month + @year
-- select @result

set @sourceTable = 'gen_048_'+@result
select @sourceTable

declare @string2 varchar(255)
set @string2 = (select convert(varchar(55),refdate)+''-''+convert(varchar(55),refcount) FROM @sourceTable)
select @string2

This is the error

Must declare the table variable "@sourceTable".

What is the value of @test? And what do you get now? What did you expect to get instead? — marc_s
– marc_s, Commented Mar 28, 2016 at 7:24

Pரதீப் · Accepted Answer · 2016-03-28 07:26:35Z

2

You need dynamic query

SET @string2 = 'select convert(varchar(55),refdate)+''-''+convert(varchar(55),refcount) FROM '
               + Quotename(@sourceTable)

EXEC (@string2)

answered Mar 28, 2016 at 7:26

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vince Osana Over a year ago

Thanks~! this one works just fine. my only problem now is how to update the rows of this reference number.

sagi · Accepted Answer · 2016-03-28 07:30:35Z

1

You are not declaring @sourcetable , the error shouts it loud and clear.

Add this At the beginning :

declare @sourcetable varchar(50)

Also, I believe you need to use dynamic SQL for this sort of queries and variable using.

answered Mar 28, 2016 at 7:30

sagi

40.6k7 gold badges66 silver badges87 bronze badges

1 Comment

Vince Osana Over a year ago

Yes I declared the @sourceTable that is why its kinda weird that I still got that error. I just forgot to put it there.

Marco Bong · Accepted Answer · 2016-03-28 07:21:56Z

0

set @string2 = (select convert(varchar(55),refdate)+'-'+convert(varchar(55),refcount) FROM @sourceTable)

answered Mar 28, 2016 at 7:21

Marco Bong

6904 silver badges13 bronze badges

4 Comments

Vince Osana Over a year ago

I already did this one, this is the exact string from the other question but its not working. This is the error Must declare the table variable "@sourceTable".

Marco Bong Over a year ago

obviously from the code you provided, don't have such variable : @sourceTable

Marco Bong Over a year ago

it seem "@sourceTable" is varchar? so you add one more line something like this. ==> declare "@sourceTable" varchar(15)

Marco Bong Over a year ago

ok then, this line "set @string2 = (select convert(varchar(55),refdate)+''-''+convert(varchar(55),refcount) FROM @sourceTable)" you use "@sourceTable" as a table? what is the column?

Collectives™ on Stack Overflow

SQL Server: set 2 column into 1 variable from variable table

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related