Change table in FROM statement

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 2005 Forums

Transact-SQL (2005)

Change table in FROM statement

Author

Topic

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-13 : 16:08:21

I have a sql query that needs to use a different table in the FROM statement based on an input parameter. Is it possible to use a CASE statement or IF statement?

Is there a way that I can use a variable to substitute the table name? These are permanent tables.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-13 : 16:24:05

That is possible using dynamic SQL.
declare @sql varchar(1000)
select @sql='select * from ' + @your_input_parm + ' where 1=1'
exec(@sql)

No, you're never too old to Yak'n'Roll if you're too young to die.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-13 : 16:28:24

These are permanent tables that may exceed the length that a variable can be. Isn't it 4000? THe input parameter will be used in the where clause, not the table name.

kira
Starting Member

17 Posts

Posted - 2009-05-13 : 16:35:58

quote:
Originally posted by valedaw2

These are permanent tables that may exceed the length that a variable can be. Isn't it 4000? THe input parameter will be used in the where clause, not the table name.

I think webfred is correct. the size of the result of exec(@sql) doesn't matter.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-13 : 16:47:00

Your topic says: Change table in FROM statement
Your first post says: table in the FROM statement based on an input parameter
Your second post says: input parameter will be used in the where clause

Now I am a bit confused.

Show a short example of wanted select and what in this select should come from input parm please.

No, you're never too old to Yak'n'Roll if you're too young to die.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-13 : 16:53:08

select cl_paynetid, min(c_ContractStartDt) cl_StartDt
into #stDate
from #LRM_procData left join dbo.contract with (nolock)
on c_subid = cl_subid
and c_paynetid = cl_paynetid
where cl_subid = @subid
group by cl_paynetid

dbo.contract is the permanent table
@subid is the input parameter
if my input parameter is '3272', then I need to use a different permanent table (dbo.contract_dell)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-13 : 17:03:08

Maybe this is what you want:
declare @sql varchar(1000)

select @sql=
'select cl_paynetid, min(c_ContractStartDt) cl_StartDt into #stDate from #LRM_procData left join ' +
case when @subid = '3272' then 'dbo.contract_dell' else 'dbo.contract' end +
' with (nolock) on c_subid = cl_subid and c_paynetid = cl_paynetid' +
' where cl_subid = ' + @subid +
' group by cl_paynetid'

exec(@sql)

No, you're never too old to Yak'n'Roll if you're too young to die.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-13 : 17:07:49

Thanks webfred. I will try it first thing tomorrow morning.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-18 : 09:55:15

Hi webfred,
I did try the dynamic sql and received the following error:
Conversion failed when converting the varchar value 'select cl_paynetid, min(c_ContractStartDt) cl_StartDt into #stDate from #LRM_procData left join CASE when ' to data type int.

Here is the code I used, @subid is the input parameter:
select @sql=
'select cl_paynetid, min(c_ContractStartDt) cl_StartDt into #stDate from #LRM_procData left join ' +
'CASE when ' + @subid + ' = 3272 then contract_dell else contract end ' +
' with (nolock) on c_subid = cl_subid and c_paynetid = cl_paynetid' +
' where c_paynetid in ' +
'(select distinct c_paynetid from ' +
'CASE when @subid = 3272 then contract_dell else contract end ' +
' with (nolock) where c_subid = ' + @subid +
' and c_closedt is null' +
' and c_RenwStartDt is Null' +
' and c_PaynetContractStatusCd not in (' + '' + 'MTOM' + '' + ',' + '' + 'RENW' + '' + ')' +
') and c_subid = ' + @subid +
' order by c_paynetid '
exec(@sql)

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-18 : 10:43:33

OK. I changed the script. I saw some mistakes I had made. But I am getting this error now:
An expression of non-boolean type specified in a context where a condition is expected, near 'exec'.

Here is the script:
select @sql=
'select cl_paynetid, min(c_ContractStartDt) cl_StartDt into #stDate from #LRM_procData left join ' +
CASE when @subid + ' = 3272 then contract_dell else contract end ' +
' with (nolock) on c_subid = cl_subid and c_paynetid = cl_paynetid' +
' where c_paynetid in ' +
'(select distinct c_paynetid from ' +
CASE when @subid + ' = 3272 then contract_dell else contract end ' +
' with (nolock) where c_subid = ' + @subid +
' and c_closedt is null' +
' and c_RenwStartDt is Null' +
' and c_PaynetContractStatusCd not in (' + '' + 'MTOM' + '' + ',' + '' + 'RENW' + '' + ')' +
') and c_subid = ' + @subid +
' order by c_paynetid '
exec(@sql)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-18 : 11:35:16

I made some adjustments:

DECLARE @subID INT
DECLARE @sql VARCHAR(8000)
DECLARE @TableName VARCHAR(255)

--SET @SubID = 6
SET @SubID = 3272

SET @TableName = CASE WHEN @subid = 3272 THEN 'contract_dell' ELSE 'contract' END 

select @sql= 
'select cl_paynetid, min(c_ContractStartDt) cl_StartDt into #stDate from #LRM_procData left join ' +
 @TableName +
' with (nolock) on c_subid = cl_subid and c_paynetid = cl_paynetid' +
' where c_paynetid in ' +
'(select distinct c_paynetid from ' +
@TableName  +
' with (nolock) where c_subid = ' + CAST(@subid AS VARCHAR(15)) +
' and c_closedt is null' +
' and c_RenwStartDt is Null' +
' and c_PaynetContractStatusCd not in (' + '' + 'MTOM' + '' + ',' + '' + 'RENW' + '' + ')' +
') and c_subid = ' + CAST(@subid AS VARCHAR(15)) +
' order by c_paynetid '

PRINT @sql

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-18 : 11:39:59

Or you could use an IF statement (if you only have a couple SubIDs):

DECLARE @subID INT

SET @SubID = 3272

IF @SubID = 3272
BEGIN
	SELECT 
		cl_paynetid,
		MIN(c_ContractStartDt) cl_StartDt
	INTO 
		#stDate
	FROM 
		#LRM_procData
	LEFT JOIN 
		contract_dell WITH (NOLOCK)
		ON  c_subid    = cl_subid
		AND c_paynetid = cl_paynetid
	WHERE 
		c_paynetid IN (
						SELECT DISTINCT c_paynetid
						FROM contract_dell WITH (NOLOCK)
						WHERE c_subid = 3272
						AND c_closedt IS NULL
						AND c_RenwStartDt IS NULL
						AND c_PaynetContractStatusCd NOT IN (MTOM, RENW)
					)
		AND c_subid = 3272
	ORDER BY 
		c_paynetid
END
ELSE
BEGIN
	SELECT 
		cl_paynetid,
		MIN(c_ContractStartDt) cl_StartDt
	INTO 
		#stDate
	FROM 
		#LRM_procData
	LEFT JOIN 
		contract WITH (NOLOCK)
		ON  c_subid    = cl_subid
		AND c_paynetid = cl_paynetid
	WHERE 
		c_paynetid IN (
						SELECT DISTINCT c_paynetid
						FROM contract WITH (NOLOCK)
						WHERE c_subid = @SubID
						AND c_closedt IS NULL
						AND c_RenwStartDt IS NULL
						AND c_PaynetContractStatusCd NOT IN (MTOM, RENW)
					)
		AND c_subid = @SubID
	ORDER BY 
		c_paynetid

END

EDIT: Cut-n-paste error

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-18 : 11:45:49

I tried the IF statement first. It tells me that the temp table that is it is creating (#stDate) already exist for the else part of it.

This procedure has at least 15 statements where this type of thing exist.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-18 : 11:55:38

That is why it is good form to explicitly declare your temp tables. Plus you can properly index them instead of leaving them as heaps.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-18 : 11:58:34

Thanks Lamprey, that makes sense. I will try with the IF statement again declaring the temp table first.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-05-18 : 14:59:44

Creating the tables did the trick.
Thanks Lamprey and everyone else who tried to help.