Search code examples
sql-serverdatabaseselectstored-proceduresopenrowset

SQL Server : 2 Stored Procedure 1 for XML & one for data

I have 2 systems that use the same stored procedure.

The problem is that one system needs data as normal select statement inside the stored procedure, while the other needs the data in XML format

I just looking for best practice,

  • do I have to create 2 stored procedures so each system read from its own stored procedure?
  • or I create one stored procedurefor normal data and the other stored procedure read data from the first one and fill it in temp table using OPENROWSET?
  • or add a parameter for data output so if I want data i pass 1 if I want xml I pass 2?
  • any other suggestions?

Thanks

Solution

  • Depends on the details of the stored procedure but as a rule of thumb I prefer to add a parameter to determine the output type. In this way you will avoid to maintain two procedures and the risk of going out of sync at some point in the future.