@return_status from SQLCLR stored procedure

I'm trying to write a VB CLR stored procedure that will set the return statu
s
to 0 or 1 depending on whether the CLR was successful or not. How do I do
that? Basically I want to use the following T-SQL code to call my CLR and
set the @.return_status
declare @.return_status int
EXEC @.return_status=My_CLR_SP
select @.return_statusI would like to know how to do this as well. Can anyone help us?
Greg Larsen wrote:
> I'm trying to write a VB CLR stored procedure that will set the return sta
tus
> to 0 or 1 depending on whether the CLR was successful or not. How do I do
> that? Basically I want to use the following T-SQL code to call my CLR and
> set the @.return_status
> declare @.return_status int
> EXEC @.return_status=My_CLR_SP
> select @.return_status|||Greg,
I found this at http://msdn2.microsoft.com/en-us/library/ms131094.aspx.
I tried it out and it works great.
Here is a really simple example:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Function TestProc() As Int32
TestProc = 1
End Function
And the test script:
DECLARE @.result AS int
EXEC @.result = TestProc
PRINT @.result
Tom
Greg Larsen wrote:
> I'm trying to write a VB CLR stored procedure that will set the return sta
tus
> to 0 or 1 depending on whether the CLR was successful or not. How do I do
> that? Basically I want to use the following T-SQL code to call my CLR and
> set the @.return_status
> declare @.return_status int
> EXEC @.return_status=My_CLR_SP
> select @.return_status|||Thank you for the VB example.
I got mine to working by writing it in C # and doing the following:
[Microsoft.SqlServer.Server.SqlProcedure]
public static int TestProc()
{ return=1;}|||You're welcome. Thanks for posting the C# example.
Tom
Greg Larsen wrote:
> Thank you for the VB example.
> I got mine to working by writing it in C # and doing the following:
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static int TestProc()
> { return=1;}

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),

Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

Thankful in advance for advice.Shouldn′t you use the SELECT before the SCOPE_IDENTITY() ? Perhaps you are returning 0 (rows affected) rather than the identity Value. If no identity value is available normally NULL is given back to the caller.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the suggestion, and yes, I am already using SELECT in front of SCOPE_IDENTITY(). And there should be an identity value available because I can successfully do this in Query Analyzer:

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

GO

SELECT @.@.IDENTITY

GO

In VB2005 using CLR, I can use command.ExecuteScalar() after "select @.@.identity" to retrieve the value and it works. But "select scope_identity()" does not work to retrieve the scalar. I was confused because it is not consistent with the behavior in Query Analyzer. All in all, it is not a big deal since @.@.identity works fine but perhaps there is some fundamental difference on the context connection which could be useful to be aware of.|||

There are better way to do this, with SQL Server 2005 you are able to use the OUTPUT clause which will directly and inline′will enable you to pass back a parameter from within the query. Soo the BOL for more Information and the syntax for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

@@IDENTITY vs. SCOPE_IDENTITY() on CLR Context Connection

Dear all,

I am trying to use SCOPE_IDENTITY() on the CLR Context Connection since it is limited to insertions in a more narrow scope than @.@.IDENTITY.

The connection string in the .NET Assembly is:
Using connection As New SqlConnection("context connection=true;"),

Onwards, I insert a new row to a table with an int(4) identity column, but the following returns zero (0):
Using command2 As New SqlCommand("SCOPE_IDENTITY() ", connection)
Try
NewTagID = CInt(command2.ExecuteScalar().ToString)

However, the following code returns the actual identity value:
Using command2 As New SqlCommand("SELECT @.@.IDENTITY", connection)

Try

NewTagID = CInt(command2.ExecuteScalar().ToString)

Why doesn't the SCOPE_IDENTITY() work on the context connection? In the meantime, I assume that @.@.IDENTITY would be the better option.

Thankful in advance for advice.Shouldn′t you use the SELECT before the SCOPE_IDENTITY() ? Perhaps you are returning 0 (rows affected) rather than the identity Value. If no identity value is available normally NULL is given back to the caller.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the suggestion, and yes, I am already using SELECT in front of SCOPE_IDENTITY(). And there should be an identity value available because I can successfully do this in Query Analyzer:

INSERT INTO mytable (col1, col2, col3)
VALUES ('', '', 1)
GO
SELECT SCOPE_IDENTITY()
GO

And I get a 1x1 grid with unnamed column showing the new ID and two rows of messages, each saying (1 row(s) affected).

I get exactly the same result (grid as well as messages) by isusing:

INSERT INTO mytable (col1, col2, col3)

VALUES ('', '', 1)

GO

SELECT @.@.IDENTITY

GO

In VB2005 using CLR, I can use command.ExecuteScalar() after "select @.@.identity" to retrieve the value and it works. But "select scope_identity()" does not work to retrieve the scalar. I was confused because it is not consistent with the behavior in Query Analyzer. All in all, it is not a big deal since @.@.identity works fine but perhaps there is some fundamental difference on the context connection which could be useful to be aware of.|||

There are better way to do this, with SQL Server 2005 you are able to use the OUTPUT clause which will directly and inline′will enable you to pass back a parameter from within the query. Soo the BOL for more Information and the syntax for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de