Dynanamic SQL in a IF statement ?

[fredong]

Hi All,
on SQL 2000 can I use a IF statement to execute a dynamic sql. If I get the print statement output to creat tables theyare fine but in the Dynamic sql in the IF statement the tables did not get created. Below is my script



DECLARE @YR varchar(4)
DECLARE @T_Yr varchar(25)
DECLARE @sqlstr varchar(2000)

SET @YR = Year(getdate())

SET @T_Yr = 'Dairy_' + @YR

SET @sqlstr =
' CREATE TABLE ' + @T_Yr + '_T1 (' + Char(10) +
' [id] [int] IDENTITY (1, 1) NOT NULL ,' + Char(10) +
' [r_ID] [int] NULL ,'+ Char(10) +
' [sysobj_name] [varchar] (50) COLLATE '+ Char(10) +
')'+ Char(10) +
'GO'+ Char(10) +

' CREATE TABLE ' + @T_Yr + '_T2 (' + Char(10) +
' [id] [int] IDENTITY (1, 1) NOT NULL ,' + Char(10) +
' [r_ID] [int] NULL ,'+ Char(10) +
' [sysobj_name] [varchar] (50) COLLATE '+ Char(10) +
')'+ Char(10) +
'GO'

If DAY(getdate())> 50
Begin
print @sqlstr
print '-- Create tables'
Exec(@sqlstr)
END
GO

 

[AlexCuse]

I think you want to either remove COLLATE or have something after it in your dynamically created SQL.

Good Luck

Alex

Ignorance of certain subjects is a great part of wisdom

 

[fredong]

even I removed the collate it did not create any tables in the IF statement. Anybody here can help? Thanks.

 

[AlexCuse]

Try removing your 'GO' statements. This worked for me (MSSQL 2000):

DECLARE @YR varchar(4)
DECLARE @T_Yr varchar(25)
DECLARE @sqlstr varchar(2000)

SET @YR = Year(getdate()) 

SET @T_Yr = 'Dairy_' + @YR 
 
SET @sqlstr = 
' CREATE TABLE ' + @T_Yr + '_T1  (' + Char(10) +
'    [id] [int] IDENTITY (1, 1) NOT NULL ,' + Char(10) +
'    [r_ID] [int] NULL ,'+ Char(10) +
'    [sysobj_name] [varchar] (50)' +
')'+ Char(10) +
+ Char(10) +

    ' CREATE TABLE ' + @T_Yr + '_T2  (' + Char(10) +
'    [id] [int] IDENTITY (1, 1) NOT NULL ,' + Char(10) +
'    [r_ID] [int] NULL ,'+ Char(10) +
'    [sysobj_name] [varchar] (50)' +
')'+ Char(10) 


If DAY(getdate())< 50 
   Begin  
print @sqlstr 
print '-- Create tables'
exec (@sqlstr) 
       END

Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

Are you getting your print statement to work?

If not, the problem is bigger than the dynamic SQL being executed.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[fredong]

I believe is the If DAY(getdate())> 50 that I have put it in a wrong format. Thanks for all your help.

 

[gmmastros]

Day returns an integer representing the Day Of The Month, which, lately, hasn't exceeded 31.

Can you explain what you are trying to test for?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I was wondering about that. I had changed it to less than figuring you always wanted it to run. A couple sentences on the purpose of this code (ie whenever it is the 15th of the month I want to create new tables) would be helpful.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[fredong]

Another thing is never put the 'GO' in the Dynamic SQL within an IF statement that will never work.