Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

Dynamic SQL query with Columns

Trying to run this code not sure why i am getting an error

Declare @sql nvarchar(4000), @Table varchar(25), @BatchNo varchar(25)
, @BatchCol varchar(25),@IDColumn varchar(25), @Max int, @Loopcount int
,@returnValue int, @Statement nvarchar(4000)

set @Statement = N'
SELECT @result = Count(1) 
from [CKOLTP_Staging].dbo.[@TableValue] tbl (nolock)
where tbl.[@BatchColValue] = Convert(varchar(10),@BatchNoValue)'
exec sp_executesql @Statement, 
    N'@TableValue varchar(25)
	,@BatchColValue varchar(25) 
	,@BatchNoValue varchar(25)
	,@result INT OUTPUT', 
    @TableValue = @Table
	,@BatchColValue = @BatchCol
	,@BatchNoValue = @BatchNo 
	,@result=@returnValue OUTPUT    

Open in new window



Error
Msg 208, Level 16, State 1, Line 5
Invalid object name 'CKOLTP_Staging.dbo.@TableValue'.

Open in new window


Can we not have the column as a variable?
SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Leo Torres

ASKER

Yes, I ended up doing that. I didnt know you cant do this with objects