Why can't I run a select command in an IF ELSE SQL 2000 statement

Dont drop the table...

DECLARE @nCreatedForJob AS TinyInt
SET @nCreatedForJob=1

IF @nCreatedForJob = 1
      BEGIN
      SELECT * INTO #MSR1 FROM Items WHERE Number='L0100001' AND Quantity=1
      END
ELSE
      BEGIN
      SELECT * INTO #MSR1 FROM Items WHERE Number='L0100001'
      END

also the if in that case is not needed

SELECT * INTO #MSR1 FROM Items WHERE Number='L0100001' AND (Quantity=1 or @nCreatedForJob = 0)

IF OBJECT_ID('dbname..#MSR1') IS NOT NULL
BEGIN
DROP TABLE #MSR1
END

I get the same effect whether or not I drop the table.  As I mentioned, I simplified the code for testing and this posting, but maybe too much.  Here's the actual code. The primary difference here is that the tables called are unique per statement, so I don't think the first suggestion by Eyal works for me either.

      BEGIN
            SELECT keyItems, Number INTO #MSR1 FROM Items
                  LEFT OUTER JOIN Jobs ON Items.keyWorkOrders = Jobs.keyWorkOrders AND @cJobNumber <> ''
                  LEFT OUTER JOIN ItemCategories ON ItemCategories.keyItemCategories = Items.keyItemCategories
            WHERE (keyCustomers = @cKeyCustomers) AND
                  (Code IN (SELECT Items FROM dbo.Split(@cCategoryCodes,',')) OR @cCategoryCodes = '') AND
                  (Number = @cItemNumber OR @cItemNumber = '') AND
                  ((ItemsDesc LIKE @cDescription OR ItemsNotes1 LIKE @cDescription OR ItemsNotes2 LIKE @cDescription) OR @cDescription = '') AND
                  (Jobs.ChildJobNum = @cJobNumber OR @cJobNumber = '') AND
                  (Width = @nWidth OR @nWidth=-1) AND
                  (Height = @nHeight OR @nHeight=-1) AND
                  (Length = @nLength OR @nLength=-1)
            ORDER BY Number
      END
ELSE
      BEGIN
            SELECT Items.keyItems, Number INTO #MSR1 FROM ItemsHistory
                  LEFT OUTER JOIN Items ON Items.keyItems = ItemsHistory.keyItems
                  LEFT OUTER JOIN Jobs ON ItemsHistory.keyWorkOrders = Jobs.keyWorkOrders
                  LEFT OUTER JOIN ItemCategories ON ItemCategories.keyItemCategories = Items.keyItemCategories
            WHERE (keyCustomers = @cKeyCustomers) AND
                  (Code IN (SELECT Items FROM dbo.Split(@cCategoryCodes,',')) OR @cCategoryCodes = '') AND
                  (Number = @cItemNumber OR @cItemNumber = '') AND
                  ((ItemsDesc LIKE @cDescription OR ItemsNotes1 LIKE @cDescription OR ItemsNotes2 LIKE @cDescription) OR @cDescription = '') AND
                  (Jobs.ChildJobNum = @cJobNumber OR @cJobNumber = '') AND
                  (Width = @nWidth OR @nWidth=-1) AND
                  (Height = @nHeight OR @nHeight=-1) AND
                  (Length = @nLength OR @nLength=-1)
            ORDER BY Number
      END

The real question here is how do I populate a temp table, within a sql 2000 stored procedure, using on one of two possible select commands based on a parameter (valued either 1 or 0).

For example,

IF parameter = 0
BEGIN
select statement #1 into temptable1
END
ELSE
BEGIN
select statement #2 into temptable1
END

You can use this code:

DECLARE @nCreatedForJob AS TinyInt
SET @nCreatedForJob = 1
IF (@nCreatedForJob = 1)
BEGIN
      EXEC('IF OBJECT_ID(''#MSR1'') IS NOT NULL ' +
             '    DROP TABLE #MSR1; ' +
             'SELECT TOP 1 * INTO #MSR1 FROM items WHERE Number=''L0100001'' AND Quantity=1; ' +
             'SELECT * FROM #MSR1; ')
END
ELSE
BEGIN
      EXEC('IF OBJECT_ID(''#MSR1'') IS NOT NULL ' +
             '    DROP TABLE #MSR1; ' +
             'SELECT TOP 1 * INTO #MSR1 FROM items WHERE Number=''L0100001''; ' +
             'SELECT * FROM #MSR1; ')
END

There are few things to note on using EXEC():
1. string data should be appended with an extra apostrophe.
2. whatever you put inside EXEC is accessible only inside the EXEC statement also.

Cheers

armchang

This works well until I try to use other parameters passed to the proc in the select statement.  There are quite a few of them I will need to work with (examples listed below).   I get a Must declare the variable message when I insert one into the EXEC statement like this.

DECLARE @cKeyCustomers AS UNIQUEIDENTIFIER
SET @cKeyCustomers='{F965EEC1-8BEC-11D4-88CD-0001024856CF}'
DECLARE @nCreatedForJob AS TinyInt
SET @nCreatedForJob = 1

IF (@nCreatedForJob = 1)
BEGIN
      EXEC('IF OBJECT_ID(''#MSR1'') IS NOT NULL ' +
             '    DROP TABLE #MSR1; ' +
             'SELECT TOP 1 * INTO #MSR1 FROM items ' +
            'WHERE (keyCustomers = @cKeyCustomers); ' +
             'SELECT * FROM #MSR1; ')
END
ELSE
BEGIN
      EXEC('IF OBJECT_ID(''#MSR1'') IS NOT NULL ' +
             '    DROP TABLE #MSR1; ' +
             'SELECT TOP 1 * INTO #MSR1 FROM items WHERE Number=''L0100001''; ' +
             'SELECT * FROM #MSR1; ')
END


@cKeyCustomers UNIQUEIDENTIFIER,
@cCategoryCodes varchar(8000),
@cItemNumber char(12),
@cDescription varchar(8000),
@cJobNumber char(10),
@nCreatedForJob TINYINT,
@cWidth char(10),
@cHeight char(10),
@cLength char(10),
@nStartAtRecord INT,
@nResultSetSize INT

If you want to put variables, you put them outside the string (see code):
Note: same goes to all variables.

DECLARE @cKeyCustomers AS UNIQUEIDENTIFIER
SET @cKeyCustomers='{F965EEC1-8BEC-11D4-88CD-0001024856CF}'
DECLARE @nCreatedForJob AS TinyInt
SET @nCreatedForJob = 1

IF (@nCreatedForJob = 1)
BEGIN
      EXEC('IF OBJECT_ID(''#MSR1'') IS NOT NULL ' +
             '    DROP TABLE #MSR1; ' +
             'SELECT TOP 1 * INTO #MSR1 FROM items ' +
            'WHERE (keyCustomers = ' + @cKeyCustomers + '); ' +
             'SELECT * FROM #MSR1; ')
END
ELSE
BEGIN
      EXEC('IF OBJECT_ID(''#MSR1'') IS NOT NULL ' +
             '    DROP TABLE #MSR1; ' +
             'SELECT TOP 1 * INTO #MSR1 FROM items WHERE Number=''L0100001''; ' +
             'SELECT * FROM #MSR1; ')
END

--it is how sql compiles .... try to use  2 #temp tables for your 2 conditions




-------------------------------------------------------------------
DECLARE @nCreatedForJob AS TinyInt
SET @nCreatedForJob=1

IF @nCreatedForJob = 1
      BEGIN
      DROP TABLE #MSR1  ---in stor proc proc you do not need this line -- just for troubleshooting
      SELECT * INTO #MSR1 FROM Items WHERE Number='L0100001' AND Quantity=1
      END
ELSE
      BEGIN
      DROP TABLE #MSR2  ---in stor proc proc you do not need this line -- just for troubleshooting
      SELECT * INTO #MSR2 FROM Items WHERE Number='L0100001'
      END
 

SQL Server ignores constructs when compiling the optimization/query plan, so both insert-into commands are "seen" during compile.

I would dynamically build the SQL statement in a local varchar variable, and execute the variable.  Case-expressions work well, temp-table may not be necessary if you only build one statement to return to the caller.  If you need the temp-table in several statements then you can:

a. create the temp-table prior to the dynamic portion, or
b. build multiple-statements into the local varchar variable

This example would return a result-set to the caller, using a single SQL statement built dynamically with passed-in parameters:

CREATE PROC yourschema.whatever
(	@var1 somedatatype = null
,	@var2 somedatatype = null
,	@var3 somedatatype = null
)
AS
DECLARE	@ls_SQL varchar(4096)

SELECT	@ls_SQL =
'	SELECT	a.field1
	,	a.field2
	,	...
	,	n.fieldn
	FROM	table1 a
	JOIN	table2 b ON a.field = b.field
	JOIN	table3 c ON b.field = c.field
	WHERE	1=1'				+ CASE WHEN @var1 IS NULL THEN '' ELSE '
	AND	a.fieldx = ' + @var1	END	+ CASE WHEN @var2 IS NULL THEN '' ELSE '
	AND	b.fieldy = ' + @var2	END	+ CASE WHEN @var3 IS NULL THEN '' ELSE '
	AND	c.fieldz = ' + @var3	END

EXEC( @ls_SQL )

Select allOpen in new window

try something like the following, I make an empty table then insert
 I had to make sure #MSR1 was not initially present at compile time or something (took a couple of goes!)  

DECLARE @nCreatedForJob AS TinyInt
SET @nCreatedForJob=1

IF object_id('tempdb..#MSR1') IS NOT NULL
BEGIN
   DROP TABLE #MSR1
END


SELECT * INTO #MSR1 FROM exlog where 1=0;

IF @nCreatedForJob = 1
      BEGIN
      
	  insert #MSR1 select * from exlog
	  
      END
ELSE
      BEGIN
      
      insert #MSR1 select * from exlog 
      END
      
      
DROP TABLE #MSR1      

Select allOpen in new window

armchang,

When I run the code with the variable outside the string I get the following error message (Note that BEC is part of @cKeyCustomers and if I attempt a different key I get the same type of error with portion of that key):

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'BEC'.

I think there's a correction, string variables should be put a double extra apostrophe:
Note: Before and after the variable @cKeyCustomers, there's 3 apostrophe's each side.

DECLARE @cKeyCustomers AS UNIQUEIDENTIFIER
SET @cKeyCustomers='{F965EEC1-8BEC-11D4-88CD-0001024856CF}'
DECLARE @nCreatedForJob AS TinyInt
SET @nCreatedForJob = 1

IF (@nCreatedForJob = 1)
BEGIN
      EXEC('IF OBJECT_ID(''#MSR1'') IS NOT NULL ' +
             '    DROP TABLE #MSR1; ' +
             'SELECT TOP 1 * INTO #MSR1 FROM items ' +
            'WHERE (keyCustomers = '''+@cKeyCustomers+'''); ' +
             'SELECT * FROM #MSR1; ')
END
ELSE
BEGIN
      EXEC('IF OBJECT_ID(''#MSR1'') IS NOT NULL ' +
             '    DROP TABLE #MSR1; ' +
             'SELECT TOP 1 * INTO #MSR1 FROM items WHERE Number=''L0100001''; ' +
             'SELECT * FROM #MSR1; ')
END

armchang - That does seem to work

John Vidmar - Your approach seems to be working as well and I may potentially use this instead, however I am running into the following issue.  I will need to check the parameters for empty strings.  In this instance, how would I work @cJobNumber into @ls_SQL?

DECLARE @cKeyCustomers AS char(36)
SET @cKeyCustomers='F965EEC1-8BEC-11D4-88CD-0001024856CF'
DECLARE @cJobNumber AS char(10)
SET @cJobNumber='S-217122'
DECLARE @cItemNumber AS char(12)
SET @cItemNumber='L0100001'

IF object_id('tempdb..#MSR1') IS NOT NULL
BEGIN
   DROP TABLE #MSR1
END

DECLARE      @ls_SQL varchar(4096)
SELECT      @ls_SQL =
'SELECT keyItems, Number INTO #MSR1 FROM items a
LEFT OUTER JOIN Jobs b ON a.keyWorkOrders = b.keyWorkOrders AND @cJobNumber <> ''''
WHERE keyCustomers='''+@cKeyCustomers+''''

EXEC( @ls_SQL )

Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@cJobNumber'.

Ignoring the last "Must declare the variable..." issue, I've discovered that using either the armchang or John Vidmar approach, I am unable to access the data in the temp table, #MSR1, after the EXEC statement.  I need to work with the data set in #MSR1.

SELECT * FROM #MSR
Yields
Invalid object name '#MSR1'

I believe if you do, for example

EXEC('select * into #temp from TableX')

then when you return from the EXEC, you no longer have the temporary table. it gets deleted when you drop out of the call stack

if you start using non temporary tables like MSR, then it is a table available to everyone (not a temporary table)  

Why don't you use it without SELECT INTO, when the resultset columns of both select statements are the same??

like this:



CREATE TABLE #MSR1 (
      keyItems INT,
      Number int
)

IF @nCreatedForJob = 1
      BEGIN
                  
                  INSERT INTO #MSR1
            SELECT keyItems, Number FROM Items
                  LEFT OUTER JOIN Jobs ON Items.keyWorkOrders = Jobs.keyWorkOrders AND @cJobNumber <> ''
                  LEFT OUTER JOIN ItemCategories ON ItemCategories.keyItemCategories = Items.keyItemCategories
            WHERE (keyCustomers = @cKeyCustomers) AND
                  (Code IN (SELECT Items FROM dbo.Split(@cCategoryCodes,',')) OR @cCategoryCodes = '') AND
                  (Number = @cItemNumber OR @cItemNumber = '') AND
                  ((ItemsDesc LIKE @cDescription OR ItemsNotes1 LIKE @cDescription OR ItemsNotes2 LIKE @cDescription) OR @cDescription = '') AND
                  (Jobs.ChildJobNum = @cJobNumber OR @cJobNumber = '') AND
                  (Width = @nWidth OR @nWidth=-1) AND
                  (Height = @nHeight OR @nHeight=-1) AND
                  (Length = @nLength OR @nLength=-1)
            ORDER BY Number
      END
ELSE
      BEGIN
     
                  INSERT INTO #MSR1
            SELECT Items.keyItems, Number INTO #MSR1 FROM ItemsHistory
                  LEFT OUTER JOIN Items ON Items.keyItems = ItemsHistory.keyItems
                  LEFT OUTER JOIN Jobs ON ItemsHistory.keyWorkOrders = Jobs.keyWorkOrders
                  LEFT OUTER JOIN ItemCategories ON ItemCategories.keyItemCategories = Items.keyItemCategories
            WHERE (keyCustomers = @cKeyCustomers) AND
                  (Code IN (SELECT Items FROM dbo.Split(@cCategoryCodes,',')) OR @cCategoryCodes = '') AND
                  (Number = @cItemNumber OR @cItemNumber = '') AND
                  ((ItemsDesc LIKE @cDescription OR ItemsNotes1 LIKE @cDescription OR ItemsNotes2 LIKE @cDescription) OR @cDescription = '') AND
                  (Jobs.ChildJobNum = @cJobNumber OR @cJobNumber = '') AND
                  (Width = @nWidth OR @nWidth=-1) AND
                  (Height = @nHeight OR @nHeight=-1) AND
                  (Length = @nLength OR @nLength=-1)
            ORDER BY Number
      END

jvejskrab

That is exactly what I did in the end.  Although the accepted comments above worked on easily enough with simpler queries, my attempts to apply to full queries as I created them were blocked by multiple errors.  Your approach makes for a long stored procedure, but the logic is simple enough that it runs without issue.