CODEWITHSUNDEEP
c#sqlsql-server
dearn44
dearn44

Reputation: 3422

Error with casting value returned by sql query

I have the following query

string sql =
          "IF NOT EXISTS(SELECT testId from TestData where testId=@testId) " +
          "Insert INTO TestData (testId, jsonString) VALUES(@testId, @jsonstring) " +
          "else " +
          "UPDATE TestData SET jsonString=@jsonstring WHERE testId=@testId " +
          "SELECT CAST(scope_identity() AS int)";

In order to have a row from TestData table wither created if not exists, or updated if it does.

The first time I use this, so when a record does not exist it works fine. If I call the function again though:

conn.Open();
newID = (Int32)cmd.ExecuteScalar();

So this time the record already exists, I get a casting error probably when trying to cast to Int32.

Is there something wrong with my query, and what can I do to fix this issue?

Upvotes: 1

Views: 389

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Presumably, you then want code that is more like this:

IF NOT EXISTS (SELECT 1 from TestData where testId = @testId) 
BEGIN
    Insert INTO TestData(testId, jsonString)
        VALUES(@testId, @jsonstring);
    SELECT CAST(scope_identity() AS int);
END
ELSE
BEGIN 
    UPDATE TestData SET jsonString = @jsonstring WHERE testId = @testId;
    SELECT CAST(id as int) FROM TestData WHERE testId = @testId;
END;

I am assuming that some column other than testId is the identity column -- say id. In general, you wouldn't be setting the identity column in an insert statement.

Also, scope_identity() is probably just fine for your purposes. But the safest way of returning information from new rows is to use the output clause (see here).

Upvotes: 1

arun G
arun G

Reputation: 224

Error may be because as you already have that testid.scope_identity() will return null as it does made any identity operation. Use SELECT @testId for update .

Upvotes: 1

Related Questions