CODEWITHSUNDEEP
sqlasp.nett-sqlwebformsado.net
mzh007
mzh007

Reputation: 1

problem using OUTPUT parameter insetad of SCOPE_IDENTITY

I am trying to use OUTPUT param instead of SCOPE_IDENTITY in my query. I have gone through some resources but couldn't understand how to use it. For example I am adding my existing code.

protected string Test(DataRow dataRow, out QueryParamList paramList)
    {
        paramList = new QueryParamList();

        StringBuilder sqlBuilder1 = new StringBuilder();
        StringBuilder sqlBuilder2 = new StringBuilder();

        for (int i = 0; i < dataRow.ItemArray.Length; i++)
        {
            if (!IsPrimaryKey(dataRow, i))
            {
                if (dataRow.ItemArray[i] != DBNull.Value)
                {
                    sqlBuilder1.Append(dataRow.Table.Columns[i].Caption + ",");
                    sqlBuilder2.Append("@" + dataRow.Table.Columns[i].Caption + ",");
                    DbType dbType = (DbType)Enum.Parse(typeof(DbType), dataRow.Table.Columns[i].DataType.Name);
                    paramList.Add(new QueryParamObj() { ParamName = dataRow.Table.Columns[i].Caption, ParamValue = dataRow.ItemArray[i], DBType = dbType });
                }
            }
        }
        if (sqlBuilder1.Length > 0) sqlBuilder1.Remove(sqlBuilder1.Length - 1, 1);
        if (sqlBuilder2.Length > 0) sqlBuilder2.Remove(sqlBuilder2.Length - 1, 1);

        string finalQuery = "Insert Into " + dataRow.Table.TableName + "(" + sqlBuilder1.ToString() + ")" + " values(" + sqlBuilder2.ToString() + ");select SCOPE_IDENTITY()";


        return finalQuery;
    }

Here in finalQuery I want to make change.

Upvotes: 0

Views: 75

Answers (1)

Selim Yildiz
Selim Yildiz

Reputation: 5370

Based on Using OUTPUT INTO with a simple INSERT statement you can follow this pattern:

INSERT INTO TableName(..,..)
OUTPUT INSERTED...
VALUES (.., .., ..)

So, in your case this should work(Assuming your identity column is ID):

string finalQuery = "Insert Into " + dataRow.Table.TableName + "(" + sqlBuilder1.ToString() + ")" + " OUTPUT INSERTED.ID "+ " values(" + sqlBuilder2.ToString() + ");";

See also IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

Upvotes: 1

Related Questions