calling a clr sp from a clr sp

31 Aug 2006 2:51 PM

Sam Jost

Say, for example, I've got two stored procedures in my dabase c#
project, can one of them call the other directly or do I need to use a
SqlCommand?

Example: I've got this stored proc:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void UserInsert(Guid id, String name)
{
    using (SqlConnection conn = new SqlConnection("Context
Connection=true"))
    {
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = "INSERT INTO [dbo].[Benutzer] ([ID], [Name])
VALUES (@ID, @Name)";
      cmd.Parameters.Add(new SqlParameter("@ID", id));
      cmd.Parameters.Add(new SqlParameter("@Name", name));
      conn.Open();
      cmd.ExecuteNonQuery();
    }
}

can I just call it from another stored proc like this:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void UserInsertCurrent()
{
    BenutzerInsert(Guid.NewGuid(), SqlContext.WindowsIdentity.Name);
}

or would I need to use SqlCommand like this:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void UserInsertCurrent()
{
    using (SqlConnection conn = new SqlConnection("Context
Connection=true"))
    {
      conn.Open();
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = "BenutzerGetByNameDomain";
      cmd.Parameters.Add(new SqlParameter("@Name",
SqlContext.WindowsIdentity.Name));
      cmd.ExecuteNoQuery();
    }
}

Calling the stored proc directly does compile, but I wonder if it is a
good thing to do - for example, how do I get values return through
SqlPipe?

Thanks,
Sam

31 Aug 2006 2:56 PM

Sam Jost

Stupid me, should just have run my example and I would have known it
does not work:

System.InvalidOperationException: The context connection is already in
use.

4 Sep 2006 1:05 AM

Peter W. DeBetta

Sam,

If you are doing something that is not doable in T-SQL that you didn't show
here, then you may ignore this comment, but if that is the actual stored
proc you are using, I suggest using T-SQL instead. If all you are doing is
inserting into a table and using the windows auth name from the login, then
all of this work can and should be done in T-SQL.

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--

Show quote

"Sam Jost" <radeldu***@gmail.com> wrote in message
news:1157035890.168549.194830@i42g2000cwa.googlegroups.com...
> Say, for example, I've got two stored procedures in my dabase c#
> project, can one of them call the other directly or do I need to use a
> SqlCommand?
>
> Example: I've got this stored proc:
>
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void UserInsert(Guid id, String name)
> {
>    using (SqlConnection conn = new SqlConnection("Context
> Connection=true"))
>    {
>      SqlCommand cmd = conn.CreateCommand();
>      cmd.CommandText = "INSERT INTO [dbo].[Benutzer] ([ID], [Name])
> VALUES (@ID, @Name)";
>      cmd.Parameters.Add(new SqlParameter("@ID", id));
>      cmd.Parameters.Add(new SqlParameter("@Name", name));
>      conn.Open();
>      cmd.ExecuteNonQuery();
>    }
> }
>
> can I just call it from another stored proc like this:
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void UserInsertCurrent()
> {
>    BenutzerInsert(Guid.NewGuid(), SqlContext.WindowsIdentity.Name);
> }
>
> or would I need to use SqlCommand like this:
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void UserInsertCurrent()
> {
>    using (SqlConnection conn = new SqlConnection("Context
> Connection=true"))
>    {
>      conn.Open();
>      SqlCommand cmd = conn.CreateCommand();
>      cmd.CommandType = CommandType.StoredProcedure;
>      cmd.CommandText = "BenutzerGetByNameDomain";
>      cmd.Parameters.Add(new SqlParameter("@Name",
> SqlContext.WindowsIdentity.Name));
>      cmd.ExecuteNoQuery();
>    }
> }
>
>
> Calling the stored proc directly does compile, but I wonder if it is a
> good thing to do - for example, how do I get values return through
> SqlPipe?
>
> Thanks,
> Sam
>

4 Sep 2006 3:03 PM

Sam Jost

Peter,

I know, this example was created for this post.

Anyway I'm still looking for a performance comparison of these two
techniques just to satisfy my curiosity - probably I got to make up one
myself ;)

Sam

Peter W. DeBetta schrieb:

Show quote

> Sam,
>
> If you are doing something that is not doable in T-SQL that you didn't show
> here, then you may ignore this comment, but if that is the actual stored
> proc you are using, I suggest using T-SQL instead. If all you are doing is
> inserting into a table and using the windows auth name from the login, then
> all of this work can and should be done in T-SQL.
>
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Sam Jost" <radeldu***@gmail.com> wrote in message
> news:1157035890.168549.194830@i42g2000cwa.googlegroups.com...
> > Say, for example, I've got two stored procedures in my dabase c#
> > project, can one of them call the other directly or do I need to use a
> > SqlCommand?
> >
> > Example: I've got this stored proc:
> >
> > [Microsoft.SqlServer.Server.SqlProcedure]
> > public static void UserInsert(Guid id, String name)
> > {
> >    using (SqlConnection conn = new SqlConnection("Context
> > Connection=true"))
> >    {
> >      SqlCommand cmd = conn.CreateCommand();
> >      cmd.CommandText = "INSERT INTO [dbo].[Benutzer] ([ID], [Name])
> > VALUES (@ID, @Name)";
> >      cmd.Parameters.Add(new SqlParameter("@ID", id));
> >      cmd.Parameters.Add(new SqlParameter("@Name", name));
> >      conn.Open();
> >      cmd.ExecuteNonQuery();
> >    }
> > }
> >
> > can I just call it from another stored proc like this:
> > [Microsoft.SqlServer.Server.SqlProcedure]
> > public static void UserInsertCurrent()
> > {
> >    BenutzerInsert(Guid.NewGuid(), SqlContext.WindowsIdentity.Name);
> > }
> >
> > or would I need to use SqlCommand like this:
> > [Microsoft.SqlServer.Server.SqlProcedure]
> > public static void UserInsertCurrent()
> > {
> >    using (SqlConnection conn = new SqlConnection("Context
> > Connection=true"))
> >    {
> >      conn.Open();
> >      SqlCommand cmd = conn.CreateCommand();
> >      cmd.CommandType = CommandType.StoredProcedure;
> >      cmd.CommandText = "BenutzerGetByNameDomain";
> >      cmd.Parameters.Add(new SqlParameter("@Name",
> > SqlContext.WindowsIdentity.Name));
> >      cmd.ExecuteNoQuery();
> >    }
> > }
> >
> >
> > Calling the stored proc directly does compile, but I wonder if it is a
> > good thing to do - for example, how do I get values return through
> > SqlPipe?
> >
> > Thanks,
> > Sam
> >