SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 How to run this stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gaby_58
Starting Member

30 Posts

Posted - 11/19/2013 :  12:15:12  Show Profile  Reply with Quote
I have stored procedure which inserts data if there are no duplicates and if there are duplicates then it will update, so just wondering how to run these to get these values from a partcular table. Thanks for any tips.

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 11/19/2013 :  13:04:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't understand what you are asking. What do you mean by "how to run these to get these values from a particular table"?

Please show us some sample data and schema for us to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gaby_58
Starting Member

30 Posts

Posted - 11/19/2013 :  13:24:05  Show Profile  Reply with Quote
I have a stored procedure with values declared, and these values need be getting it from a particular table, so how do I get these values from a table. Please see the example
CREATE Procedure [dbo].[proc_PermitsUploadRigData]

@ReportingId Integer,
@ReceivedDate SmallDateTime,
@LastUpdate SmallDateTime,
@LocationNumber Varchar(50),
@LocationNumber2 Varchar(50)

etc...

s

Set NoCount On

If Not Exists
(
Select LocationNumber, LocationNumber2 From Wells Where LocationNumber = @LocationNumber And LocationNumber2 = @LocationNumber2
)
Insert Wells (Formation, WorkType, FilingLocation, ReceivedDate, ReportingId, LocationNumber, LocationNumber2, PermitLocation, PermitOperator, Lease, SpudDate, DrillName, RigNo, PermitDepth, Class, OnOffshore, ContractName, DeviationType, RigType, Field, FilingCity, FilingState, WellState, WellCounty, WellClass) Values (@Formation, @WorkType, @FilingLocation, @ReceivedDate, @ReportingId, @LocationNumber, @LocationNumber2, @PermitLocation, @OperatorName, @Lease, @SpudDate, @DrillName, @RigNo, @PermitDepth, @Class, @OnOffshore, @ContractName, @DeviationType, @RigType, @Field, @FilingCity, @FilingState, @WellState, @WellCounty, @WellClass)
Else
Update Wells Set Formation = @Formation, WorkType = @WorkType, FilingLocation = @FilingLocation, ReportingId = @ReportingId, PermitLocation = @PermitLocation, PermitOperator = @OperatorName, Lease = @Lease, DrillName = @DrillName, RigNo = @RigNo, PermitDepth = @PermitDepth, Class = @Class, OnOffshore = @OnOffshore, ContractName = @ContractName, DeviationType = @DeviationType, RigType = @RigType, Field = @Field, FilingCity = @FilingCity, FilingState = @FilingState, WellState = @WellState, WellCounty = @WellCounty, WellClass = @WellClass, LastUpdate = @LastUpdate Where LocationNumber = @LocationNumber And LocationNumber2 = @LocationNumber2 And SpudDateFreeze = '0'

Thanks for any tips
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 11/19/2013 :  13:27:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you asking how to set the input variables using data from a table?

If so, then create local variables and query the table:

declare @var1 smalldatetime, @var2 smalldatetime

select @var1 = Column1, @var2 = Column2
from table1
where id = 4321

exec storedproc1 @inputvar1 = @var1, @inputvar2 = @var2

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gaby_58
Starting Member

30 Posts

Posted - 11/19/2013 :  13:48:32  Show Profile  Reply with Quote
So should I create a procedure like this

USE [testshare]
GO

/****** Object: StoredProcedure [dbo].[proc_UploadData] Script Date: 11/19/2013 10:43:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE Procedure [dbo].[proc_UploadData]

@ReportingId Integer,
@ReceivedDate SmallDateTime,
@LastUpdate SmallDateTime,
@LocationNumber Varchar(50),
@LocationNumber2 Varchar(50)

Select @ReportingId = reportingid, @lastdate=lastdate,@locationnumber=locationnumber, @locationnumber2 = locnumber2 from table1

As

Set NoCount On

If Not Exists
(
Select LocationNumber, LocationNumber2 From Wells Where LocationNumber = @LocationNumber And LocationNumber2 = @LocationNumber2
)
Insert Temp (Formation, WorkType, FilingLocation, ReceivedDate, ReportingId, LocationNumber, LocationNumber2, PermitLocation, PermitOperator, Lease, SpudDate, DrillName, RigNo, PermitDepth, Class, OnOffshore, ContractName, DeviationType, RigType, Field, FilingCity, FilingState, WellState, WellCounty, WellClass) Values (@Formation, @WorkType, @FilingLocation, @ReceivedDate, @ReportingId, @LocationNumber, @LocationNumber2, @PermitLocation, @OperatorName, @Lease, @SpudDate, @DrillName, @RigNo, @PermitDepth, @Class, @OnOffshore, @ContractName, @DeviationType, @RigType, @Field, @FilingCity, @FilingState, @WellState, @WellCounty, @WellClass)
Else
Update temp Set Formation = @Formation, WorkType = @WorkType, FilingLocation = @FilingLocation, ReportingId = @ReportingId, PermitLocation = @PermitLocation, PermitOperator = @OperatorName, Lease = @Lease, DrillName = @DrillName, RigNo = @RigNo, PermitDepth = @PermitDepth, Class = @Class, OnOffshore = @OnOffshore, ContractName = @ContractName, DeviationType = @DeviationType, RigType = @RigType, Field = @Field, FilingCity = @FilingCity, FilingState = @FilingState, WellState = @WellState, WellCounty = @WellCounty, WellClass = @WellClass, LastUpdate = @LastUpdate Where LocationNumber = @LocationNumber And LocationNumber2 = @LocationNumber2 And SpudDateFreeze = '0'

Then just execute the procedure? please advice...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 11/19/2013 :  13:50:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
No, you don't put these inside the stored procedure. You set the variables before you execute the stored procedure, just like how I showed in my example. The EXEC command is where I am executing the stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 11/19/2013 :  13:51:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
Also, you need to add a WHERE clause to that query so that it only returns one row. And if you need to execute the stored procedure for each row in the table, then you either need to loop through the rows or change this thing to handle multiple rows.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gaby_58
Starting Member

30 Posts

Posted - 11/19/2013 :  14:47:29  Show Profile  Reply with Quote
Could you please give me an example of how to handle this. We have multiple rows and this needs to check if it already exists in the destination table, if it already exists then it should update the values, if not insert the values. Thanks for any tips
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000