sql multiple concatenation in a string

Question

I have a big problem that I don't understand.

I have this query in Tsql :

SET @sSQL = 'SELECT * 
  INTO '+@sTableValeursDefaut+' 
  FROM OPENQUERY(LINKSVR_LOCAL , '' SET FMTONLY OFF 
  EXEC [AIGP].[dbo].[rp_WEB_ValeursDefaut_Get] 
  '''''+'sds'+''''',
  '+ISNULL(CONVERT(VARCHAR, @fkIDProjet), 'NULL+')+',
  '+'123'+''')'

EXEC(@sSQL)

This work perfectly. But when I change the '123' for CONVERT(VARCHAR, @fkIDCfgFormulaire) and the 'sds' to @sNoUsager the query don't work !

SET @sSQL = 'SELECT * 
  INTO '+@sTableValeursDefaut+' 
  FROM OPENQUERY(LINKSVR_LOCAL , '' SET FMTONLY OFF 
  EXEC [AIGP].[dbo].[rp_WEB_ValeursDefaut_Get] 
  '''''+@sNoUsager+''''',
  '+ISNULL(CONVERT(VARCHAR, @fkIDProjet), 'NULL+')+',
  '+CONVERT(VARCHAR, @fkIDCfgFormulaire)+''')'

EXEC(@sSQL)

What I am doing wrong ?

Mack · Accepted Answer · 2013-12-04 23:56:42Z

1

Without more information it's difficult to tell. Could be anything from the datatypes of the variables to the value of the variables you are passing...

We could do with some test data please.

It may also be that you have a '+' character in your literal I haven't tested it but 'NULL+' should probably read 'NULL'

Or it could be because in your CONVERT(VARCHAR, @fkIDProjet) you don't give a length to your varchar try something like this instead CONVERT(VARCHAR(5), @fkIDProjet)

edited Dec 4, 2013 at 23:56

answered Dec 4, 2013 at 23:43

Mack

2,5521 gold badge26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql multiple concatenation in a string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related