-1

I have this part of the code:

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = REPLACE(@TableName, 'form.', '') 
              AND COLUMN_NAME = 'INSTID'))
BEGIN
    SET @sql = 'SELECT COUNT(*) FROM '+ @TableName + ' WHERE id = ' + str(@id) + ' AND INSTID = ' + @in

    EXEC sp_executesql @sql, N'@instid2 int output', @instid2 output
END

But I get this error:

Conversion failed when converting the nvarchar value 'Select COUNT(*) from form.PRO_TTZ where id = 1070 and INSTID = ' to data type int.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aggicd
  • 727
  • 6
  • 28
  • 2
    I deduce that `@in` is declared as an `int`. `+` is an addition operator as well as a string concatenation one. You're using `sp_executesql` and passing it parameters - so aside from the table name (that cannot be parameterized) why are you continuing to use string concatenation to insert values into a query when you could just pass additional parameters? – Damien_The_Unbeliever Nov 28 '17 at 15:33
  • 1
    not related to the error message. You missed out the `@instid2` in your query – Squirrel Nov 28 '17 at 15:34
  • @Cowthulhu - the way the error message ends the string it's trying to convert at `... INSTID = ` should indicate to you that it's whatever would come next in assembling the complete query string that's an issue. – Damien_The_Unbeliever Nov 28 '17 at 15:38
  • Agree with @Damien_The_Unbeliever here, `@in` is likely to be an integer. Use `...and INSTID = ' + CONVERT(varchar(5),@in)` (guessed the varchar length) – Thom A Nov 28 '17 at 15:39
  • Possible duplicate of [Conversion failed when converting the nvarchar value ... to data type int](https://stackoverflow.com/questions/21655110/conversion-failed-when-converting-the-nvarchar-value-to-data-type-int) – Tab Alleman Nov 28 '17 at 15:57

1 Answers1

1

try that:

str(@in) --probably @in is declared as int
atroul
  • 219
  • 2
  • 9