why i am not able to get the expected answer from the written query.

• shaikhansar786

  SSC Enthusiast

  Points: 134

  More actions

  March 10, 2017 at 5:58 am

  #322450

  Hello All Experts,
  i am not getting the correct result or may be i am not able to understand it.
  i have written below query , can anyone figure it out what could be the wrong in this

  DECLARE @v1 sql_variant;
  SET @v1 = '1';
  declare @temp table (ID int )
  select 1 from @temp
  Where 1=(case when SQL_VARIANT_PROPERTY(@v1, 'BaseType') in ('varchar','nvarchar','char') then 1 else 0 end)

  the above query i am trying , ones it gets succesfull i will merge this into my main query.

• Thom A

  SSC Guru

  Points: 99130

  More actions

  March 10, 2017 at 6:07 am

  #1932683

  I'm not sure what you're expecting, but you're declared a table variable, not inserting any data and then selecting from it. Thus you won't receive any results.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  March 10, 2017 at 6:31 am

  #1932690

  If you don't tell us what you are expecting, how do you expect us to help?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  March 10, 2017 at 6:46 am

  #1932693

  DECLARE @v1 sql_variant;
SET @v1 = '1';
declare @temp table (ID int )
select (case when SQL_VARIANT_PROPERTY(@v1, 'BaseType') in ('varchar','nvarchar','char') then 1 else 0 end)
  
  Try this code.

  In your example the table is empty.

  Maybe this does illustrate what you are doing wrong:
  DECLARE @v1 sql_variant;
SET @v1 = '1';
declare @temp table (ID int )
insert into @temp values(1)
insert into @temp values(2)
insert into @temp values(3)
select *, 1 from @temp
Where 1=(case when SQL_VARIANT_PROPERTY(@v1, 'BaseType') in ('varchar','nvarchar','char') then 1 else 0 end)
  

  Ben

• shaikhansar786

  SSC Enthusiast

  Points: 134

  More actions

  March 12, 2017 at 11:18 pm

  #1932844

  ben.brugman - Friday, March 10, 2017 6:46 AM

  DECLARE @v1 sql_variant;
SET @v1 = '1';
declare @temp table (ID int )
select (case when SQL_VARIANT_PROPERTY(@v1, 'BaseType') in ('varchar','nvarchar','char') then 1 else 0 end)
  
  Try this code.

  In your example the table is empty.

  Maybe this does illustrate what you are doing wrong:
  DECLARE @v1 sql_variant;
SET @v1 = '1';
declare @temp table (ID int )
insert into @temp values(1)
insert into @temp values(2)
insert into @temp values(3)
select *, 1 from @temp
Where 1=(case when SQL_VARIANT_PROPERTY(@v1, 'BaseType') in ('varchar','nvarchar','char') then 1 else 0 end)
  

  Ben

  i have tried with the above except for i have not use sql_variant as datatype.
  i have used , int, varchar, char, etc.