Click here to Skip to main content
15,886,724 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

SQL
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)
Posted
Updated 10-Nov-19 4:24am
Comments
Kornfeld Eliyahu Peter 10-Nov-19 4:02am    
Why create a stored procedure for something that you wish to run only once in a lifetime?
Run a parametrized query...

1 solution

Use EXISTS to check if the data already exists, see example here:
SQL Server IF EXISTS THEN 1 ELSE 2 - Stack Overflow[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900