Trying to use a Case statement inside a cursor.

Question

I am trying to make the following work:

declare @ActTable as varchar(1000)
declare @cPK as VarChar(100)
declare @SQL as nvarchar(2000)
declare @FK as VarChar(100)
declare @FKRef as VarChar(200)
declare @TblRef as varchar (100)


create table #temp (
M2MTable varchar(50),
PK varchar (100), 
FK Varchar(100),
FKRefTable Varchar(50))
insert into #temp
select 'slcdpm' , 'fcustno', '','' union all
select 'somast' , 'fsono', 'fcustno','slcdpm' union all
select 'soitem' , 'fsono,finumber', 'fsono','somast' union all
select 'sorels', 'fsono,finumber,frelease', 'fsono,finumber','soitem' union all
select 'qtmast', 'fquoteno', 'fcustno', 'slcdpm'  union all
select 'qtitem' , 'fquoteno', 'fquoteno','qtmast'  union all
select  'armast', 'fcinvoice','fcustno','scldpm' union all
select 'aritem','fcinvoice,fitem','fcinvoice','armast' union all
select  'apvend', 'fvendno','','' union all
select 'apmast','fvendno,fcinvoice','fvendno','apvend'union all
--select  'apitem','fvendno,fcinvoice,union all
select  'pomast','fpono','fvendno','apvend'union all
select  'poitem', 'fpono,fitemno','fpono','pomast'    union all
select  'shmast', 'fshipno','fsokey','sorels'              union all
select  'shitem','fshipno,fitemno','fshipno','shmast'     --         union all


declare M2M_AddFK cursor for select M2MTable,FK,FKRefTable from #temp
open M2M_AddFK
fetch next from M2M_AddFK into @ActTable,@FK,@TblRef
while @@FETCH_STATUS = 0
Begin

case 
when @FK <> ''then Set @SQL = N'alter table dbo.'+ @ActTable + ' ADD  FOREIGN KEY (' + @FK + ') References DBO.' + @TblRef + '(' + @FK + ')'
Print @SQL
fetch next from M2M_AddFK into @ActTable,@FK,@TblRef
else 


fetch next from M2M_AddFK into @ActTable,@FK,@TblRef
end

end 

close M2M_AddFK
deallocate M2M_AddFK

drop table #temp

Please notice the Case statement:

case 
when @FK <> ''then Set @SQL = N'alter table dbo.'+ @ActTable + ' ADD  FOREIGN KEY (' + @FK + ') References DBO.' + @TblRef + '(' + @FK + ')'
Print @SQL
fetch next from M2M_AddFK into @ActTable,@FK,@TblRef
else 


fetch next from M2M_AddFK into @ActTable,@FK,@TblRef
end

I simply want to create the alter table statement when there is a @FK value and to skip it if it's ''.

Can someone point me in the right direction?

Answer 1

You have to uase an IF ELSE statement.

Also, Formatting the code a little, will make it a LOT easier to read X-)

Replace the WHILE loop with

while @@FETCH_STATUS = 0 
Begin 

    IF @FK <> ''
    BEGIN
        Set @SQL = N'alter table dbo.'+ @ActTable + ' ADD  FOREIGN KEY (' + @FK + ') References DBO.' + @TblRef + '(' + @FK + ')' 
        Print @SQL 
        fetch next from M2M_AddFK into @ActTable,@FK,@TblRef 
    END
    ELSE
    BEGIN
        fetch next from M2M_AddFK into @ActTable,@FK,@TblRef 
    end  
end 

So that your entire statement looks like

declare @ActTable as varchar(1000) 
declare @cPK as VarChar(100) 
declare @SQL as nvarchar(2000) 
declare @FK as VarChar(100) 
declare @FKRef as VarChar(200) 
declare @TblRef as varchar (100) 


create table #temp ( 
M2MTable varchar(50), 
PK varchar (100),  
FK Varchar(100), 
FKRefTable Varchar(50)) 

insert into #temp 
select 'slcdpm' , 'fcustno', '','' union all 
select 'somast' , 'fsono', 'fcustno','slcdpm' union all 
select 'soitem' , 'fsono,finumber', 'fsono','somast' union all 
select 'sorels', 'fsono,finumber,frelease', 'fsono,finumber','soitem' union all 
select 'qtmast', 'fquoteno', 'fcustno', 'slcdpm'  union all 
select 'qtitem' , 'fquoteno', 'fquoteno','qtmast'  union all 
select  'armast', 'fcinvoice','fcustno','scldpm' union all 
select 'aritem','fcinvoice,fitem','fcinvoice','armast' union all 
select  'apvend', 'fvendno','','' union all 
select 'apmast','fvendno,fcinvoice','fvendno','apvend'union all 
--select  'apitem','fvendno,fcinvoice,union all 
select  'pomast','fpono','fvendno','apvend'union all 
select  'poitem', 'fpono,fitemno','fpono','pomast'    union all 
select  'shmast', 'fshipno','fsokey','sorels'              union all 
select  'shitem','fshipno,fitemno','fshipno','shmast'     --         union all 


declare M2M_AddFK cursor for select M2MTable,FK,FKRefTable from #temp 

open M2M_AddFK 
fetch next from M2M_AddFK into @ActTable,@FK,@TblRef 
while @@FETCH_STATUS = 0 
Begin 

    IF @FK <> ''
    BEGIN
        Set @SQL = N'alter table dbo.'+ @ActTable + ' ADD  FOREIGN KEY (' + @FK + ') References DBO.' + @TblRef + '(' + @FK + ')' 
        Print @SQL 
        fetch next from M2M_AddFK into @ActTable,@FK,@TblRef 
    END
    ELSE
    BEGIN
        fetch next from M2M_AddFK into @ActTable,@FK,@TblRef 
    end  
end  

close M2M_AddFK 
deallocate M2M_AddFK 

drop table #temp