We help IT Professionals succeed at work.

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

woodwyn
woodwyn asked
on
Medium Priority
422 Views
Last Modified: 2012-05-12
Can someone tell me why this won't work and what an alternative approach might be?  I am trying to run one of two select statements in a stored procedures based on whether a parameter = 0 or 1.  I simplified the code here for testing in analyzer.  Whether the parameter is set to 0 or 1, when I run the statement I get the error "There is already an object named '#MSR1' in the database."  It appears to hit both select commands each time.

DECLARE @nCreatedForJob AS TinyInt
SET @nCreatedForJob=1

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

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

Commented:
also the if in that case is not needed

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

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

Author

Commented:
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

Author

Commented:
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
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
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
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
--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
 
CERTIFIED EXPERT

Commented:
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 )

Open in new window

deightonprog
CERTIFIED EXPERT

Commented:
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      

Open in new window

Author

Commented:
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'.
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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'.

Author

Commented:
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'
Senior Developer
CERTIFIED EXPERT
Commented:
Here it is for the new requirements woodwyn: After you run all the code below, feel free to use your SELECT statement.

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 * INTO MSR1 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 * INTO MSR1 FROM #MSR1; ')
END
SELECT * INTO #MSR1 FROM MSR1;
DROP TABLE MSR1;

deightonprog
CERTIFIED EXPERT

Commented:
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)  


CERTIFIED EXPERT
Commented:
Here is an example that creates a temp table that the entire stored-procedure may use (whether the following statements are dynamic or not).  The temp-table is populated using dynamic-SQL, we used dynamic-SQL because we don't know at runtime whether the passed-parameters would help filter our data (i.e., if the passed-paramter has a value then append it to the where-clause in @ls_SQL, if the value is blank/null then don't append it to the where-clause in @ls_SQL; this was achieved via case-expression).  

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

-- non-dynamic
CREATE TABLE #temp
(	field1 somedatatype 
,	field2 somedatatype 
,	field3 somedatatype 
)

-- dynamic
SELECT	@ls_SQL =
'	INSERT #temp
	(	field1
	,	field2
	,	field3
	)
	SELECT	a.field1
	,	a.field2
	,	b.field3
	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 ) 

-- non-dynamic
SELECT * FROM #temp

Open in new window


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

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.