Stored Procedure execution

• pandeesh

  Say Hey Kid

  Points: 673

  More actions

  December 24, 2011 at 9:10 am

  #391198

  Can i have a 2 stored procedures in same name provided both are under different schema under same db?

  Or should be only one SP under one database?

  E.G:

  I have executed the below statement:

  exec SPNAME param

  it was executed fine.

  But if i execute like:

  exec DBNAME.SCHEMANAME.SPNAME param

  it was not executed.,

  What could be the problem?

  Thanks

  Thanks,
  Pandeeswaran

• salum

  SSCommitted

  Points: 1598

  More actions

  December 24, 2011 at 10:19 am

  #1425343

  Yes , you can create 2 Strored procedure with same name in two different schema. but not in dbo schema.

  This below command should execute perfectly. what error you are getting.

  exec DBNAME.SCHEMANAME.SPNAME param

  java[/url]

• pandeesh

  Say Hey Kid

  Points: 673

  More actions

  December 24, 2011 at 10:35 am

  #1425345

  So,if we have a s.p in DBO schema, then can't we have the S.P with the same name in different schema?

  In my case i am also having the S.P under dbo schema and While executing

  exec DBNAME.dbo.SPNAME

  i got a connection failure error.

  So, if we have a SP in DBO schema, can't we include the dbname and schemaname while executing the SP?

  Thanks,
  Pandeeswaran

• salum

  SSCommitted

  Points: 1598

  More actions

  December 24, 2011 at 11:47 am

  #1425359

  So,if we have a s.p in DBO schema, then can't we have the S.P with the same name in different schema?

  Yes, You can create it but in reverse if you have SP in any XYZ schema then you can't create a sp with same name in DBO schema.

  So, if we have a SP in DBO schema, can't we include the dbname and schemaname while executing the SP?

  You can give db name it won't give any error. you just try

  java[/url]

• okbangas

  SSChampion

  Points: 11773

  More actions

  December 24, 2011 at 2:49 pm

  #1425368

  You're a bit wrong here salum, the following code works just fine, and as expected:

  create schema test1;

  go

  create procedure test1.test as

  begin

  print 'Schema test1';

  end;

  go

  create procedure dbo.test as

  begin

  print 'Schema dbo';

  end;

  go

  create schema test2;

  go

  create procedure test2.test as

  begin

  print 'Schema test2';

  end;

  go

  exec test1.test;

  exec dbo.test;

  exec test2.test;

  go

  drop procedure test1.test;

  drop procedure dbo.test;

  drop procedure test2.test;

  drop schema test1;

  drop schema test2;

  What you have to take care of though, is that different users may have different default schemas. So, if UserA and UserB both execute the following statement:

  exec test

  They may execute different stored procedures, for instance test1.test and test2.test respectively. If these accept or require different parameters, execution may fail for one of the users, but not for the other. So, multiple objects with the same name can be confusing.

  ---

  
  Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

  Concatenating Row Values in Transact-SQL[/url]