none
How to allow stored procedure to work one time only ? RRS feed

  • Question

  • problem

    I have stored procedure move data by openrowset based on select statement
    I need to modify stored procedure to prevent repeated data
    suppose i have on table company on database SQL server 2012
    1 mazda
    2 toyotal
    first time Executed stored procedure it show on excel with data above
    when Execute stored procedure for second time it show repeated data on excel as following
    1 mazda
    2 toyotal
    1 mazda
    2 toyotal
    so that how to allow stored procedure for work for one time only to prevent repeating ?

    What I have tried:

    create proc exportcompanydata
    (
    @ImporterQueueID INT=1,
    @InputFilePath Nvarchar(500)='',
    @OutputFilePath Nvarchar(500)='',
    @UserID INT=1
    )
    as
     
    Declare @sqlout nvarchar(4000);
    Set @sqlout='INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml;HDR=YES;Database='+@OutputFilePath+ ''','' SELECT * FROM [Sheet1$]'' )
    			 select CompanyID,CompanyName,PersonLastCheckDate from CompanyManagers.Company with(nolock)
                 WHERE  CompanyID IS NOT NULL AND ISNULL(IsDeleted, 0) = 0'
    
    Execute(@sqlout)


    Sunday, November 10, 2019 3:15 AM

Answers

  • Hi engahmedbarbary, 

    Thank you for your issue .

    After creating a stored procedure , if you execute 'exec exportcompanydata' ,  your procedure will execute once .  If you don't execute 'exec exportcompanydata' again , your procedure will not  execute twice.  

    Please attention. A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

    So I agree that ,as Olaf said , you might not need to create a stored procedure . Just do it as a simple script .

    Hope it will help you.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 5:46 AM

All replies

  • Please read my old blog about duplicates and how to deal with it,

    http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, November 10, 2019 5:52 AM
    Answerer
  • until now not detect how to solve this problem can you help me please 
    Sunday, November 10, 2019 6:04 AM
  • Also see if this works  for you

    Declare @sqlout nvarchar(4000); Set @sqlout='INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml;HDR=YES;Database='+@OutputFilePath+ ''','' SELECT * FROM [Sheet1$]'' ) select CompanyID,CompanyName,PersonLastCheckDate from CompanyManagers.Company with(nolock) WHERE CompanyID IS NOT NULL AND ISNULL(IsDeleted, 0) = 0

    AND NOT EXISTS (SELECT * FROM ompanyManagers.Company C WHERE C.CompanyName=CompanyManagers.Company.CompanyName)'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, November 10, 2019 6:05 AM
    Answerer
  • so that how to allow stored procedure for work for one time only to prevent repeating

    drop the SP after execution or don't create a SP at all and execute your script just once.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Sunday, November 10, 2019 8:17 AM
  • Hi engahmedbarbary, 

    Thank you for your issue .

    After creating a stored procedure , if you execute 'exec exportcompanydata' ,  your procedure will execute once .  If you don't execute 'exec exportcompanydata' again , your procedure will not  execute twice.  

    Please attention. A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

    So I agree that ,as Olaf said , you might not need to create a stored procedure . Just do it as a simple script .

    Hope it will help you.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 11, 2019 5:46 AM