How to run this stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

How to run this stored procedure

Author

Topic

gaby_58
Starting Member

33 Posts

Posted - 2013-11-19 : 12:15:12

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

38200 Posts

Posted - 2013-11-19 : 13:04:51

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/

gaby_58
Starting Member

33 Posts

Posted - 2013-11-19 : 13:24:05

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-19 : 13:27:38

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/

gaby_58
Starting Member

33 Posts

Posted - 2013-11-19 : 13:48:32

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...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-19 : 13:50:02

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/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-19 : 13:51:01

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/

gaby_58
Starting Member

33 Posts

Posted - 2013-11-19 : 14:47:29

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources