none
Set Value Dynamically for several Stored Procedures in a single place

    질문

  • Hi All,

    I have a requirment of creating 200 reports for a client 'APG' which should show only the details of the 'APG' client based on the Stored procedures.Many clients are available in database 'client' table . In all the Stored Procedures,we have to add a condition like clientid=1 from the client table for identifying the client as APG.The problem I am facing is if I migrate the whole Stored Procedures to other environments,then it will throw an error due to the difference in the client id.In other environment ,client id may be different.So is there any way to dynamically set the value of the clientid ,so that at the time of migration to other environments we can change the value at only one time instead of changing in all the Stored procedures?I know there is an option to add that value in a table and we can select that value using a variable in the SP.So if we migrate to other environments ,then it is necessary to change that table value only.Is there any other way except this?Please help me in this.

    Thanks,

    Surya

    2018년 5월 17일 목요일 오후 5:07

모든 응답

  • Hey,

    wondering why can't you add optional parameter ClientID to your stored proc and call the stored proc with desired clientID ?

    If you have something like this usp_yoursproc @ClientID = NULL and then call exec usp_yoursproc @clientID= 1


    2018년 5월 17일 목요일 오후 5:24
  • Hi Vinay,

    Thanks for your reply. There is no constant field in each environments for identifying the APG Client in the table .So we can't use the parameter concept in the Stored Procedure.If we add the parameter in the Stored Procedure ,then we have to pass the Clientid right.But there is no option to identify the clientid in each environments.In dev,it is 1 and in Prod may be 2.So my question is if I set the value only once ,how client id value should reflect in all the Stored Procedures ?

    Thanks 

    Surya

    2018년 5월 17일 목요일 오후 5:45
  • I prefer to require my table to have a name that is the same.  If it is APG in one database, it will always have that name.  Then I have the following code in my stored procedure:

    DECLARE @ID int = (SELECT ID FROM clienttable 
    where name = 'APG');
    
    

    Then use the variable.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    2018년 5월 17일 목요일 오후 5:49
  • Hi Russ,

    The above solution also doesn't work.APG is the text value which is passing from the Application.It may not be constant.There is no constant value in the database table.So is there any other solution ?

    Thanks,

    Surya

    2018년 5월 17일 목요일 오후 5:53
  • Is the clientid a constant for the database?  Does your stored procedure always use the same clientid on in that database?  So in db1 it is always 1, but in db2 it is always 15.  In Db3 it is always 101.

    Then create a settings table and put one row in it with the clientid.

    CREATE TABLE SETTINGS (
    Name varchar(20) not null
    , Value varchar(100) null);
    
    INSERT Settings 
    values ('MyStoredProcedure', 1);
    
    -- Or in db2
    INSERT Settings 
    values ('MyStoredProcedure', 15);
    
    


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    2018년 5월 17일 목요일 오후 6:07
  • Thanks Russ for your answer. I have already mentioned the above answer in the question.Is there any other way to achieve this?

    2018년 5월 18일 금요일 오전 6:42
  • Out of curiosity, what is the problem with using a table to hold these values?  What are some of the alternatives that you think might be theoretically possible?

    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    2018년 5월 20일 일요일 오전 11:58