These sql dynamic no work

• oigo

  SSC-Addicted

  Points: 422

  More actions

  May 5, 2003 at 7:52 am

  #80017

  Hello, I get these sentences:

  execute sp_executesql

  N'update ptype set @a=@b where ptype.nname = @C',N'@a char(1),@b int,@c varchar(5)',

  @a=@vartype,@b=@xqty ,@c=@xname

  and this

  execute sp_executesql

  N'insert ptype (nname,@a) values (@b,@c)',

  N'@a char(1),@b varchar(5),@c int',

  @a=@vartype,@b=@xname,@c=@xqty

  the variables are declared, the table ptype exist,the data type are similar, but these sentences no works, what is ti wrong? thanks

• Rajesh Patavardhan

  SSCrazy

  Points: 2801

  More actions

  May 5, 2003 at 8:26 am

  #456152

  The Update syntax does not allow to use variable names on left hand side of SET stagement

  i.e.,

  SET @colname = 'empid'

  UPDATE table

  SET @colname = 'Value'

  is not a valid syntax. Hence you are getting eror message.

  Alternatively, what you can do is concatenate the values while building dynamic sqls for the column names

  DECLARE @m_sql NVARCHAR(255)

  SET @m_sql = 'update ptype set '+ @a + ' =@b where ptype.nname = @C'

  execute sp_executesql @m_sql,N'@b int,@c varchar(5)',@b=@xqty ,@c=@xname

  Same explanation holds for insert also. The variables in columns clause not allowed.

  Hope this helps