Can SQL Server's CASE statement accommodate multiple SELECT queries?

Data and Databases
1

Hey everyone! I’m working on moving a Foxpro database to SQL Server. I’ve got this tricky CASE statement in Foxpro that I’m not sure how to handle in SQL Server. It looks something like this:

CASE some_var
  WHEN 1 THEN SELECT * FROM Table1 INTO CURSOR Cursor1
  WHEN 2 THEN SELECT * FROM Table2 INTO CURSOR Cursor2
  WHEN 3 THEN SELECT * FROM Table3 INTO CURSOR Cursor3
  ...
  WHEN N THEN SELECT * FROM TableN INTO CURSOR CursorN
END CASE

I know SQL Server’s CASE is usually used within a SELECT statement. Is there a way to do something similar without resorting to IF statements? I’d really appreciate any suggestions or alternatives. Thanks in advance for your help!

2

hey, have u thought about using a stored procedure? u could do something like:

CREATE PROCEDURE SelectBasedOnVar
@some_var INT
AS
BEGIN
IF @some_var = 1 SELECT * FROM Table1
ELSE IF @some_var = 2 SELECT * FROM Table2
– add more conditions
END

then just call the procedure with ur variable. might be easier to maintain?

3

While SQL Server’s CASE statement doesn’t directly support multiple SELECT queries, you can achieve similar functionality using a combination of techniques. One approach is to use dynamic SQL with sp_executesql. This method allows you to construct and execute SQL statements based on your variable conditions. Here’s an example:

DECLARE @sql NVARCHAR(MAX)
SET @sql = N’SELECT * FROM ’ +
CASE @some_var
WHEN 1 THEN ‘Table1’
WHEN 2 THEN ‘Table2’
WHEN 3 THEN ‘Table3’
– Add more cases as needed
END

EXEC sp_executesql @sql

This approach provides flexibility and maintains the structure of your original CASE statement while adapting to SQL Server’s syntax. Remember to consider performance implications and ensure proper security measures when using dynamic SQL.

4

hmm, interesting question! have u considered using temp tables instead of cursors? you could do something like:

DECLARE @TableName NVARCHAR(50)
SET @TableName = CASE @some_var
WHEN 1 THEN ‘Table1’
WHEN 2 THEN ‘Table2’
…
END

EXEC('SELECT * INTO #TempTable FROM ’ + @TableName)

what do u think? might this work for ur situation?