Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Newbie Parameter Question 2

Status
Not open for further replies.

schief13

Technical User
Jan 21, 2004
26
US
I use this delete query to update 4 tables. Is there a way to set a parameter for the field "siteno" so I only have to enter 1 time. Thanks


use [acr]
go

delete sb.SiteContractBill
where SiteNo = 'PA0041'
go

delete sb.SitePMInspection
where SiteNo = 'PA0041'

delete sb.SitePMInspectionSubtaskList
where SiteNo = 'PA0041'
go

delete sb.siteRenewalPrice
where SiteNo = 'PA0041'
go
 
Code:
declare @siteNo as varchar(10)
set @siteNo = 'PA0041'

use [acr]
go

delete sb.SiteContractBill
where SiteNo = @siteNo
go

delete sb.SitePMInspection
where SiteNo = @siteNo

delete sb.SitePMInspectionSubtaskList
where SiteNo = @siteNo
go

delete sb.siteRenewalPrice
where SiteNo = @siteNo
go
 
If you ask me it would be a job for referential integrity rules to cascade a delete of Site.SiteNo in all child data.

But yes, a parameter would work.

Code:
USE [acr]

IF OBJECT_ID ( 'sb.DeleteSite', 'P' ) IS NOT NULL 
    DROP PROCEDURE sb.DeleteSite;
GO
CREATE PROCEDURE sb.DeleteSites 
    @SiteNo nvarchar(10)
AS 
delete sb.SiteContractBill
where SiteNo =  @SiteNo;

delete sb.SitePMInspection
where SiteNo = @SiteNo;

delete sb.SitePMInspectionSubtaskList
where SiteNo =  @SiteNo;

delete sb.siteRenewalPrice
where SiteNo =  @SiteNo;
GO

You execute this once and thereby have a stored procedure in the database acr in the sb schema.

To delete a site you then call
Code:
USE [acr];
EXECUTE sb.DeleteSite N'PA0041';

Bye, Olaf.
 
Whats wrong with this:
Code:
use [acr]
declare @siteNo as varchar(10)
set @siteNo = 'PA0041'
delete sb.SiteContractBill            where SiteNo = @siteNo
delete sb.SitePMInspection            where SiteNo = @siteNo
delete sb.SitePMInspectionSubtaskList where SiteNo = @siteNo
delete sb.siteRenewalPrice            where SiteNo = @siteNo

BUT! I prefer Olaf's answer :)
The question is HOW many times you will use that SP?

Borislav Borissov
VFP9 SP2, SQL Server
 
There's nothing wrong with an adhoc script you execute in SSMS in a query window and enter your value in the head section, of course.

Since the question was about parameters that would need a stored proc or user defined function, as an adhoc script has no parameters. You can only define variables and set them to its value, which is about the same thing as using (named) constants, which T-SQL doesn't offer, so you can also work without adding a stored proc to the database. Both ways have their pros and cons.

Bye, Olaf.



 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top