更好的存储过程代码?

问题描述：

这是我的sp，可以从两个表中获取详细信息

hi here is my sp to get details from two tables

create PROCEDURE [dbo].[usp_GetCategoryByID1]
@ID int
AS BEGIN
select  tblCategory.*,tblContractType.*
From tblCategory left outer join tblContractType on tblCategory.catContractTypeID=tblContractType.cttContractTypeID
 and tblContractType.cttisDelStatus='0'
where catID=@ID and tblCategory.catisDelStatus='0'
 END

我想更改此sp，我想再添加一张表格以获取详细信息

tblCategorySub

和@ ID = subcID ..任何人都可以建议我如何加入此sp ..

感谢

and i want to alter this sp i want to add one more table to get details

tblCategorySub

and @ID=subcID..can any one suggest me how to join in this sp..

thanks

答

要更改您的过程，请使用Alter 关键字，我们如何知道您的表结构和关系?

To alter your procedure use Alter keyword and how do we know your table structure and relation??

Alter PROCEDURE [dbo].[usp_GetCategoryByID1]
@ID int
AS BEGIN
--Write your sql query here..

select  tblCategory.*,tblContractType.*,tblCategorySub.*
From tblCategory left outer join tblContractType on tblCategory.catContractTypeID=tblContractType.cttContractTypeID 
--here I added (change your table name according to your table structure and relation)
inner join tblCategorySub on tblCategory.subcID= tblCategorySub.subcID
--upto here
 and tblContractType.cttisDelStatus='0'
where catID=@ID and tblCategory.catisDelStatus='0'

 END

嗨..

请尝试以下查询.

Hi ..

Please try the below query .

Alter  PROCEDURE [dbo].[usp_GetCategoryByID1]
@ID int
AS BEGIN
select  tblCategory.*,tblContractType.*
From tblCategory left outer join tblContractType 
on tblCategory.catContractTypeID=tblContractType.cttContractTypeID
join tblCategorySub on tblCategorySub.subcID =tblCategory.catContractTypeID
 
and tblCategorySub.@ID=subcID
 and tblContractType.cttisDelStatus='0'
 
where catID=@ID and tblCategory.catisDelStatus='0'
 END

select  tblCategory.*,tblContractType.*
From tblCategory 
 left outer join tblContractType on (tblCategory.catContractTypeID=tblContractType.cttContractTypeID)

left outer join tblCategorySub  on 
(tblCategorySub.ID=tblCategory.subid)

where catID=@ID and tblCategory.catisDelStatus='0' and  tblContractType.cttisDelStatus='0'

相关推荐