Srored procedure standards

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  June 26, 2012 at 4:03 am

  #263315

  What would be the most common/standard stored procedure structure.

  In particular I am interested in return value.

  I currently have an XML template for various database types, this XML snippet is for SQL Server :

  <SQLFlavour>

  <Code>MSSQL</Code>

  <Name>Microsoft SQL Server</Name>

  <DBMSName>Microsoft SQL Server</DBMSName>

  <StoredProcMethod>SCRIPT</StoredProcMethod>

  <ExecProcFormat><![CDATA[DECLARE @ReturnCode int;[br]EXEC @ReturnCode = {proc_name}[br]|sp_params|@{param_name}={param_value}|end_sp_params|;[br]IF @ReturnCode <> 0 RAISERROR('Procedure failed',16,1)[br]]]></ExecProcFormat>

  <InsertFormat><![CDATA[INSERT INTO {table_name}[br]([br]{fields_csv}[br])[br] VALUES[br] ({values_csv})]]></InsertFormat>

  <FieldNameQualifierStart>[</FieldNameQualifierStart>

  <FieldNameQualifierEnd>]</FieldNameQualifierEnd>

  <ItemQualifierEnd>]</ItemQualifierEnd>

  <GetProcCode><![CDATA[sp_helptext {proc_name}]]></GetProcCode>

  <GetTableDefinitionCode><![CDATA[sp_help {table_name}]]></GetTableDefinitionCode>

  <StringDelimeter>'</StringDelimeter>

  <ReplaceStrings>

  <Replace>

  <from><![CDATA[']]></from>

  <to><![CDATA['']]></to>

  </Replace>

  </ReplaceStrings>

  <Notes />

  </SQLFlavour>

  The bit I want to get right is the stored procedure default template for SQL Server

  EXEC @ReturnCode = {proc_name}[br]|sp_params|@{param_name}={param_value}|end_sp_params|;

  IF @ReturnCode <> 0 RAISERROR('Procedure failed',16,1)

  I am assuming SQL pro's use a value of 0 (zero) to indicate procedure success.

  Is this right/standard practice?

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• Grant Fritchey

  SSC Guru

  Points: 399391

  More actions

  June 26, 2012 at 4:45 am

  #1505482

  The majority of people do, but it's not a guaranteed approach.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  June 26, 2012 at 5:34 am

  #1505501

  Many thanks Grant,

  I have made the sql either 'automatic' or 'custom' - but wanted automatic merge sql to be as 'common' as possible..

  

  Any Oracle guru's on central? or a recommended Oracle forum to pose the same question?

  Richard

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015