Using CASE for branched INPUT

• hassanmir

  Mr or Mrs. 500

  Points: 568

  More actions

  December 5, 2001 at 2:29 pm

  #420802

  Steve,, it does not give me any syntax error if i have just this

  SELECT @cmd = 'UPDATE ' + @Table + ' SET ' + @Variable + ' = ' +

  Case WHEN @DataType = 'int'

  THEN 'cast( ' + @Value + ' as ' + @DataType + ' where Patkey = ' + @Patkey

  END

  But if i add the 2nd Case, i get incorrect syntax error(156) near Case

  SELECT @cmd = 'UPDATE ' + @Table + ' SET ' + @Variable + ' = ' +

  Case WHEN @DataType = 'int'

  THEN 'cast( ' + @Value + ' as ' + @DataType + ' where Patkey = ' + @Patkey

  Case WHEN @DataType = 'char'

  THEN ' ''cast( ' + @Value + ' as ' + @DataType + ') '' where Patkey = ' + @Patkey

  END

  Any ideas why? Thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734449

  More actions

  December 5, 2001 at 2:41 pm

  #420803

  no second case needed.

  declare @cmd varchar( 100), @x int

  select @x = 1

  select @cmd = 'this' +

  case when @x = 1

  then ' or that'

  when @x = 2

  then ' or the other'

  when @x = 3

  then ' something new'

  else ' something else'

  end

  select @cmd

  Steve Jones

  steve@dkranch.net

• hassanmir

  Mr or Mrs. 500

  Points: 568

  More actions

  December 5, 2001 at 3:56 pm

  #420805

  Thanks Steve,, i think i almost got it ,,, i have the following trigger and getting this error message when i trigger it with sample data ,,, i am sure u can spot the error

  Server: Msg 295, Level 16, State 3, Procedure trgUpdateSQL, Line 28

  Syntax error converting character string to smalldatetime data type.

  CREATE TRIGGER [trgUpdateSQL] ON [TEST_MIR]

  FOR INSERT, UPDATE

  AS

  SET NOCOUNT ON

  Declare @Patkey varchar(12)

  Declare @QuestId varchar(12)

  Declare @QuestDate varchar(10)

  Declare @Table varchar(20)

  Declare @Date smalldatetime

  Declare @Variable varchar(128)

  Declare @Value varchar(128)

  Declare @DataType varchar(20)

  SELECT @Patkey=Patkey FROM INSERTED

  SELECT @QuestDate=Questdate FROM INSERTED

  SET @Date=CAST(@QuestDate AS smalldatetime)

  SELECT @QuestID=QuestID FROM INSERTED

  SELECT @Table= Tabl FROM INSERTED

  SELECT @Variable= V FROM INSERTED

  SELECT @Value=RTRIM(LTRIM(Va)) FROM INSERTED

  SELECT @DataType = DataType FROM INSERTED

  Select @Date

  DECLARE @cmd varchar(255)

  SELECT @cmd = 'UPDATE ' + @Table + ' SET ' + @Variable + ' = ' +

  Case WHEN @DataType = 'int'

  THEN 'cast( ' + @Value + ' as ' + @DataType + ' where Patkey = ''' + @Patkey + ''' and Questdat = ''' + @Date + ''''

  WHEN @DataType = 'float'

  THEN 'cast( ' + @Value + ' as ' + @DataType + ' where Patkey = ''' + @Patkey + ''' and Questdat = ''' + @Date + ''''

  ELSE ' ''cast( ' + @Value + ' as ' + @DataType + ') '' where Patkey = ' + @Patkey + ''' and Questdat = ''' + @Date + ''''

  END

• hassanmir

  Mr or Mrs. 500

  Points: 568

  More actions

  December 5, 2001 at 7:33 pm

  #420814

  just to update u Steve,, following works ,, as long as i dont try to convert @QuestDate to smalldatetime it works fine,, i dont know why? actual type of questdat variable is smalldatetime

  Declare @QuestDate varchar(10)

  SELECT @cmd = 'UPDATE ' + @Table + ' SET ' + @Variable + ' = ' +

  Case WHEN @DataType = 'int' THEN 'cast( ' + @Value + ' as ' + @DataType + ') where Patkey = ''' + @Patkey + ''' and Questdat = ''' + @QuestDate + ''''

  WHEN @DataType = 'float'

  THEN 'cast( ' + @Value + ' as ' + @DataType + ' ) where Patkey = ''' + @Patkey + ''' and Questdat = ''' + @QuestDate + ''''

  ELSE ' ''cast( ' + @Value + ' as ' + @DataType + ') '' where Patkey = ' + @Patkey + ''' and Questdat = ''' + @QuestDate + ''''

  END

  Exec(@cmd)

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734449

  More actions

  December 6, 2001 at 10:36 am

  #420845

  Not sur why that wouldn't work. Smalldatetime is just a little smaller (in size) than datetime.

  Glad its mostly working.

  Steve Jones

  steve@dkranch.net