Parameter based column in Temporary table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parameter based column in Temporary table.

Author

Topic

sweta_singh
Starting Member

10 Posts

Posted - 2009-09-23 : 04:43:29

Hi,
I need to write a query that goes something like this.

IF @ProposalID IS NULL
BEGIN
SELECT TOP 0 *,0 As ThirdPartyStyle INTO #TempfnQuoteLineValues
FROM dbo.fnQuoteLineValues(@QuoteID, @ProposalID) ql
OPTION (FORCE ORDER)
END
ELSE
BEGIN
SELECT TOP 0 *
INTO #TempfnQuoteLineValues
FROM dbo.fnQuoteLineValues(@QuoteID, @ProposalID) ql
OPTION (FORCE ORDER)
END
I need to add the column ThirdPartyStyle only when @ProposalID is NULL.

But this gives a parse error as follows:
There is already an object named '#TempfnQuoteLineValues' in the database.

Even if the creation of temp table #TempfnQuoteLineValues is in IF ELSE, SQL Server gives an error for having the create logic twice.
I tried using case statements to check @ProposalID value and then add new column, but that too gave error.

Could anyone please suggest me a way to do this?

Thanks in advance,
Sweta.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 09:06:13

Try this

SELECT TOP 0 *
INTO #TempfnQuoteLineValues
FROM dbo.fnQuoteLineValues(@QuoteID, @ProposalID) ql
OPTION (FORCE ORDER)
GO
IF @ProposalID IS NULL
ALTER TABLE #TempfnQuoteLineValues
ADD ThirdPartyStyle int

UPDATE #TempfnQuoteLineValues
SET ThirdPartyStyle =0

Madhivanan

Failing to plan is Planning to fail

sweta_singh
Starting Member

10 Posts

Posted - 2009-09-24 : 03:48:33

Hey thanks. If it was just to create, this would have worked. But then I need to insert values in the table too based on the same condition. That again gives error.

Declare @ProposalID int
Declare @QuoteID int
Set @proposalID=NULL
Set @QuoteID=1234

SELECT TOP 0 *
INTO #TempfnQuoteLineValues
FROM dbo.fnQuoteLineValues(@QuoteID, @ProposalID)
OPTION (FORCE ORDER)

 IF @ProposalID IS NOT NULL
BEGIN
	INSERT INTO #TempfnQuoteLineValues				
	SELECT *									
	FROM dbo.fnQuoteLineValues(NULL, @ProposalID)
	OPTION (FORCE ORDER)
END
ELSE
BEGIN
	ALTER TABLE #TempfnQuoteLineValues
	ADD ThirdPartyStyle int
	

	INSERT INTO #TempfnQuoteLineValues
	SELECT ql.*,
	CASE WHEN ql.StyleNumber = s.StyleNumber
          THEN 1
	  ELSE 0
        END 
	FROM dbo.fnQuoteLineValues(@QuoteID, NULL) ql
	LEFT JOIN (SELECT DISTINCT StyleNumber FROM  Product_Maintenance_Style)s
	on ql.StyleNumber=s.StyleNumber
	OPTION (FORCE ORDER)
END

This gives the follwing error irrespective of @ProposalID being NULL:
Insert Error: Column name or number of supplied values does not match table definition.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 04:28:43

You need to specify the column names like

INSERT INTO #TempfnQuoteLineValues(col1,col2,...)

Madhivanan

Failing to plan is Planning to fail

sweta_singh
Starting Member

10 Posts

Posted - 2009-09-24 : 05:16:26

Well, When using a select statement such as the one below, to insert values into a table, not specifying the column name works just fine. The following piece of code works fine for the Store procedure (but again, the column ThirdPartyStyle is not needed for ProposalID NOT NULL)

SELECT TOP 0 *,0 As ThirdPartyStyle
INTO #TempfnQuoteLineValues
FROM dbo.fnQuoteLineValues(@QuoteID, @ProposalID) ql
OPTION (FORCE ORDER)

IF @ProposalID IS NOT NULL
BEGIN
	INSERT INTO #TempfnQuoteLineValues				
	SELECT ql.*,									
	CASE WHEN ql.StyleNumber = s.StyleNumber		
	THEN 1											
	ELSE 0											
	END 
	FROM dbo.fnQuoteLineValues(NULL, @ProposalID) ql
	LEFT JOIN (SELECT DISTINCT StyleNumber FROM Product_Maintenance_Style)s
	ON ql.StyleNumber=s.StyleNumber
	OPTION (FORCE ORDER)
END
ELSE
BEGIN
	INSERT INTO #TempfnQuoteLineValues
	SELECT ql.*,
	CASE WHEN ql.StyleNumber = s.StyleNumber		
	THEN 1											
	ELSE 0											
	END 
	FROM dbo.fnQuoteLineValues(@QuoteID, NULL) ql
	LEFT JOIN (SELECT DISTINCT StyleNumber FROM Product_Maintenance_Style)s
	on ql.StyleNumber=s.StyleNumber
	OPTION (FORCE ORDER)
END

I guess if this insert works in this case, it should work in the earlier case too. Thanks again though, I shall try specifying all the column names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources