CODEWITHSUNDEEP
sqlsql-server
Eugene Sukh
Eugene Sukh

Reputation: 2727

Cannot convert VARCHAR to INT

I wrote variable in sql

Here is code

 DECLARE @rowNumber INT
SET @rowNumber = 'SELECT CAST((-1) * ROW_NUMBER() over (ORDER BY (t.Id)) AS INT)
FROM [dbo].[AbpTenants] as t
ORDER BY t.Id'
EXEC (@rowNumber)

But when I try to run this, I got this error.

S0001][245] Conversion failed when converting the varchar value 'SELECT CAST((-1) * ROW_NUMBER() over (ORDER BY (t.Id)) AS INT) FROM [dbo].[AbpTenants] as t ORDER BY t.Id' to data type int.

Where is problem and how I can convert it to int?

Upvotes: 0

Views: 1415

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do multiplication with negative number :

DECLARE @rowNumber INT

SET @rowNumber = COUNT(*) * -1
FROM [dbo].[AbpTenants] t;

SELECT @rowNumber

EDIT : If you want row numbers for all rows then you need table variable instead.

You have a scaler variable @rowNumber, and it will hold only single value not multiple values. 

DECLARE @rownumber TABLE (id INT, rowNumbers INT)

INSERT INTO @rownumber (id, rowNumbers)
    SELECT t.id, ROW_NUMBER() OVER (ORDER BY t.id)
    FROM [dbo].[AbpTenants] t;

SELECT *
FROM @rownumber;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Your query does not make sense for multiple reasons:

  • You are setting an integer parameter to a string.
  • You are setting a single value to the results of a query, that will likely return multiple rows.
  • You are casting the result of a function that returns an int to an int.
  • You are creating a negative number and calling it "row number". Row numbers are not usually negative.

I suspect you want a calculation, not a subquery. If you want the count of rows in the table, you would use:

DECLARE @rowNumber INT;

SELECT @rowNumber = COUNT(*)
FROM [dbo].[AbpTenants] t;

This is not quite equivalent to your logic, but it does make sense.

Upvotes: 1

Related Questions