CODEWITHSUNDEEP
sql-server
Ian
Ian

Reputation: 291

How to select from different table by case when

I have some tables with different data, but them have the same column UserId:

If you declare like that : 

declare @Type nvarchar(max) = 'Video';
declare @Id nvarchar(max)='M00000000007199';
declare @UserIdTable TABLE (UserId bigint)

Then I want to insert value into @UserIdTable by @Type, and I imaged :

insert into @UserIdTable 
case when @Type=Video then
  select UserId from VideoTable
else
  select UserId from TvTable
end

I know that is wrong, what should I do for this problem?

Upvotes: 1

Views: 493

Answers (1)

marc_s
marc_s

Reputation: 755023

CASE in T-SQL is an expression which can return a value - it is not a flow control statement like in C# or VB.NET, and it cannot contain SQL statements or code blocks.

You'll need to rewrite your code to use a regular IF .. THEN ... ELSE statement - something like this:

IF @Type = Video 
   INSERT INTO @UserIdTable 
      SELECT UserId FROM dbo.VideoTable
ELSE
   INSERT INTO @UserIdTable 
      SELECT UserId FROM dbo.TvTable

Upvotes: 3

Related Questions