Newbie Parameter Question 2

[schief13]

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

 

[jbenson001]

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

 

[Olaf Doschke]

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.

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

USE [acr];
EXECUTE sb.DeleteSite N'PA0041';

Bye, Olaf.

 

[schief13]

Thanks very much!

 

[bborissov]

Whats wrong with this:

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

 

[Olaf Doschke]

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.