exec()

• swush

  SSC Rookie

  Points: 41

  More actions

  June 3, 2002 at 9:32 am

  #357543

  i have to port a sql statement from mysql to mssql which goes like :

  select fieldname=1 from tablename

  which returns 1 for fieldname if its true else 0, but doesn't affect the rowcount. the syntax is not compatible at all, so i've built a stored proc in mssql. unfortunatly the tablename has a dynamic postfix which is submitted as an additional parameter. i thought

  @count=(exec("select count(fieldname) from tablename"+@postfix+" where fieldname= 1 and id ="+@id))

  and then check if the count is >0 or not for every id, but somehow that won't work.

  any help would would be appreciated

  jan

• danw

  SSCommitted

  Points: 1842

  More actions

  June 5, 2002 at 10:00 am

  #430373

  Try this instead:

  create table #tmp1 (result int)

  insert into #tmp1 exec(@sqlstatement)

  select @count = result from #tmp1

  also an fyi... string or varchar values in mssql are encapsulated in single quotes, not double quotes.

  Hope this helps,

  DanW

• swush

  SSC Rookie

  Points: 41

  More actions

  June 7, 2002 at 2:27 am

  #430487

  thanks for your help danW, but in between i found another way to do it:

  instead of

  select a=1 from ...

  now in t-sql

  select a = case count(a) when >0 then 1 else 0 from ...

  hope i wrote it right, i didn't find the statement now, but anyway it worked that way.

  jan