By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,168 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,168 IT Pros & Developers. It's quick & easy.

Sql Server 2005 SP CRL C#

P: n/a
Hi I am trying to make a stored procedure which verifies that a record
exists in an other table if not creates it then gets the id of the new
record to complete its transaction.

here is my current code, it seems not to be able to get the record id
even if I have inserted it to the new table.
Code:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 LogEntryType_Index(SqlString Type,
SqlBoolean IsFistTrial)
{
SqlConnection oConn = new SqlConnection("Context
Connection=true");
try
{
SqlCommand oCmd = new SqlCommand("SELECT Log_Entry_Type_Id
from dbo.Log_Entry_Type WHERE Log_Entry_Type_Name LIKE '" + Type.Value
+ "';", oConn);
oConn.Open();
SqlDataReader result =
oCmd.ExecuteReader(CommandBehavior.CloseConnection ); ;
if (result.HasRows)
{
SqlInt32 index =
SqlInt32.Parse(result["Log_Entry_Type_Id"].ToString());
result.Close();
return index;
}
else
{
if (IsFistTrial.IsTrue)
{
StoredProcedures.InsertLogEntryType(Type, "n/a");
return
UserDefinedFunctions.LogEntryType_Index(Type, new SqlBoolean(false));
}else{
throw new Exception("LogEntryType insertion
failed");
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (oConn.State != ConnectionState.Closed)
{
oConn.Close();
}
}
}

};
any help would be greatly apreciated. This is my first crl store
procedure so I am lost at the moment.

best regards,
Alexandre Brisebois

Mar 28 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.