stored procedure question - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

stored procedure question

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-11 : 06:39:57

Hi i have a stored procedure as below which does an insert. Now what i want do do is that if @jobumber has a value (it is not null) then do a different insert.
How can i do this. Could i use if and else?

eg.

if @jobnumber is not null

insert into dbo.archivefilerequest (archiverequestid, fileid)
select @archivereqid, arg.dbo.[file].fileId
from arg.dbo.asset INNER JOIN
arg.dbo.[file] ON asset.assetId = arg.dbo.[file].assetId INNER JOIN
arg.dbo.fileType ON arg.dbo.[file].fileTypeId = arg.dbo.fileType.fileTypeId INNER JOIN
arg.dbo.bomAsset ON arg.dbo.asset.assetId = arg.dbo.bomAsset.assetId INNER JOIN
CSA.dbo.Jobs ON asset.tmJobNumber = CSA.dbo.Jobs.TMJobNumber INNER JOIN
CSA.dbo.ProductLookup ON CSA.dbo.Jobs.ProductID = CSA.dbo.ProductLookup.ProductID AND
CSA.dbo.Jobs.ProductID = CSA.dbo.ProductLookup.ProductID AND CSA.dbo.Jobs.ProductID = CSA.dbo.ProductLookup.ProductID AND
CSA.dbo.Jobs.ProductID = CSA.dbo.ProductLookup.ProductID
where
(arg.dbo.asset.tmJobNumber = @jobnumber AND arg.dbo.bomAsset.bomId = @bomid AND arg.dbo.fileType.resolution = 'high' AND (arg.dbo.asset.releasedForDownload = 1 OR arg.dbo.asset.releasedForDownload = 2)) AND (CSA.dbo.ProductLookup.flags & 2 = 2)

else
insert into dbo.archivefilerequest (archiverequestid, fileid)
select @archivereqid, arg.dbo.[file].fileId
from CSA.dbo.ProductLookup INNER JOIN
CSA.dbo.Jobs ON CSA.dbo.ProductLookup.ProductID = CSA.dbo.Jobs.ProductID AND CSA.dbo.ProductLookup.ProductID = CSA.dbo.Jobs.ProductID AND
CSA.dbo.ProductLookup.ProductID = CSA.dbo.Jobs.ProductID AND CSA.dbo.ProductLookup.ProductID = CSA.dbo.Jobs.ProductID RIGHT OUTER JOIN
arg.dbo.asset INNER JOIN
arg.dbo.bomAsset ON arg.dbo.asset.assetId = arg.dbo.bomAsset.assetId INNER JOIN
arg.dbo.[file] ON arg.dbo.asset.assetId = arg.dbo.[file].assetId INNER JOIN
arg.dbo.fileType ON arg.dbo.[file].fileTypeId = arg.dbo.fileType.fileTypeId ON CSA.dbo.Jobs.TMJobNumber = arg.dbo.asset.tmJobNumber
where
(arg.dbo.bomAsset.bomId = @bomid AND arg.dbo.fileType.resolution = 'high' AND (arg.dbo.asset.releasedForDownload = 1 OR arg.dbo.asset.releasedForDownload = 2)) and (CSA.dbo.ProductLookup.flags & 2 = 0 or CSA.dbo.ProductLookup.flags & 2 is null)

/* my procedure at the moment */
create PROCEDURE dbo.ArchiveRequestAddBomMKT

@bomid int,
@userid int,
@clientid varchar(40),
@jobnumber int = null,
@fileid uniqueidentifier = null

as

begin

/* declare the variable that will hold the uniqueidentifier value for
@ArchiveRequestID */
declare @archivereqid uniqueidentifier

/* assign the declared variable a value */
set @archivereqid = newid()

/* insert into archive request table */
insert into dbo.archiverequest (archiveRequestId, bomid, tmjobnumber, userid, ClientID)
values (@archivereqid, @bomid, @jobnumber, @userid, @clientid)

/* now we want find all files for this bom and loop through them one by one and then
insert each file into archive file request */

insert into dbo.archivefilerequest (archiverequestid, fileid)
select @archivereqid, arg.dbo.[file].fileId
from arg.dbo.asset INNER JOIN
arg.dbo.[file] ON asset.assetId = arg.dbo.[file].assetId INNER JOIN
arg.dbo.fileType ON arg.dbo.[file].fileTypeId = arg.dbo.fileType.fileTypeId INNER JOIN
arg.dbo.bomAsset ON arg.dbo.asset.assetId = arg.dbo.bomAsset.assetId INNER JOIN
CSA.dbo.Jobs ON asset.tmJobNumber = CSA.dbo.Jobs.TMJobNumber INNER JOIN
CSA.dbo.ProductLookup ON CSA.dbo.Jobs.ProductID = CSA.dbo.ProductLookup.ProductID AND
CSA.dbo.Jobs.ProductID = CSA.dbo.ProductLookup.ProductID AND CSA.dbo.Jobs.ProductID = CSA.dbo.ProductLookup.ProductID AND
CSA.dbo.Jobs.ProductID = CSA.dbo.ProductLookup.ProductID
where
(arg.dbo.asset.tmJobNumber = @jobnumber AND arg.dbo.bomAsset.bomId = @bomid AND arg.dbo.fileType.resolution = 'high' AND (arg.dbo.asset.releasedForDownload = 1 OR arg.dbo.asset.releasedForDownload = 2)) AND (CSA.dbo.ProductLookup.flags & 2 = 2)

end
go

mr_mist
Grunnio

1870 Posts

Posted - 2003-11-11 : 06:43:25

Yes. You use if..

If @variable = something
BEGIN
... somecode
END ELSE BEGIN
... Some other code
END

-------
Moo. :)

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-11-11 : 07:02:39

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources