sql存付过程动态排序

wuxiaoqqqq 2008-10-07 11:35:49



ALTER PROCEDURE [dbo].[usp_GetAllEmployeeEffectiveFringeBenefitsByProjectIDCompanyID]

	@pCompanyID int,

	@pProjectID int,

    @pFilterChar varchar (1),

    @pSort varchar(50)

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

    DECLARE @tblFringeBenefit TABLE

	(

		EmployeeID int,

		FirstName varchar(255),

		LastName varchar(255),

		CraftCode varchar(18),

		FringeBenefitID int

	)



	INSERT INTO @tblFringeBenefit

	SELECT DISTINCT E.EmployeeID, E.FirstName , E.LastName, FB.CraftCode,

			(

			   SELECT TOP 1 FB1.FringeBenefitID 

			   FROM FringeBenefits FB1 INNER JOIN FringeBenefitsEmployees FBE1 ON (FBE1.EmployeeID = E.EmployeeID OR FBE1.EmployeeID = 0) AND FBE1.FringeBenefitID = FB1.FringeBenefitID

			   WHERE FB1.CompanyID = @pCompanyID AND FB1.ProjectID = @pProjectID AND FB1.CraftCode = FB.CraftCode 

			   ORDER BY FB1.DurationFrom DESC

			)

	FROM Employees E INNER JOIN ProjectEmployee PE ON E.EmployeeID = PE.EmployeeID AND PE.ProjectID = @pProjectID AND PE.ActiveRecord = 1

					 INNER JOIN FringeBenefitsEmployees FBE ON (FBE.EmployeeID = PE.EmployeeID OR FBE.EmployeeID = 0)

					 INNER JOIN FringeBenefits FB ON FBE.FringeBenefitID = FB.FringeBenefitID AND FB.CompanyID = @pCompanyID AND FB.ProjectID = @pProjectID

	WHERE E.CompanyID = @pCompanyID AND E.ActiveRecord = 1 AND E.LastName like @pFilterChar+'%'



	INSERT INTO @tblFringeBenefit

	SELECT DISTINCT E.EmployeeID, E.FirstName , E.LastName, '', 0

	FROM Employees E INNER JOIN ProjectEmployee PE ON E.EmployeeID = PE.EmployeeID AND PE.ProjectID = @pProjectID AND PE.ActiveRecord = 1

	WHERE E.CompanyID = @pCompanyID AND E.ActiveRecord = 1 AND E.LastName like @pFilterChar+'%' AND E.EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM @tblFringeBenefit)





SELECT TE.EmployeeID, TE.FirstName, TE.LastName, 

		   FB.FringeBenefitID, FB.CraftCode, FB.ClassCode, FB.StepCode, FB.AppPeriod, FB.ConstructionTrade,

		   FB.VacationOrHoliday, FB.VacationOrHolidayPercentage, FB.PaidToNameVacationOrHoliday, FB.PaidToAddressVacationOrHoliday,

		   FB.HealthAndWelfare, FB.HealthAndWelfarePercentage, FB.PaidToNameHealthAndWelfare, FB.PaidToAddressHealthAndWelfare,

		   FB.Pension, FB.PensionPercentage, FB.PaidToNamePension, FB.PaidToAddressPension,

		   FB.ApprenticeOrTraining, FB.ApprenticeOrTrainingPercentage, FB.PaidToNameApprenticeOrTraining, FB.PaidToAddressApprenticeOrTraining,

		   FB.OtherFringe1, FB.OtherFringe1Percentage, FB.OtherFringe2, FB.OtherFringe3, 

		   FB.OtherFringes, FB.OtherFringesPercentage, FB.PaidToNameOtherFringes, FB.PaidToAddressOtherFringes,

		   FB.TravelSubsistancePercentage, FB.TravelSubsistance, FB.DurationFrom, FB.DurationTo, FB.OverRideIndividual, FB.RateID, 

		   R.AppBasicHourlyRatePercentage, R.VersionCode, CR.CraftDescription, CL.ClassDescription, IsNull(ST.StepDescription,'') AS StepDescription

	FROM  @tblFringeBenefit  TE LEFT JOIN FringeBenefits FB ON FB.FringeBenefitID = TE.FringeBenefitID 

							LEFT JOIN Crafts CR ON FB.CraftCode = CR.CraftCode

							LEFT JOIN Classes CL ON FB.ClassCode = CL.ClassCode

							LEFT OUTER JOIN Steps ST ON FB.StepCode = ST.StepCode

							LEFT OUTER JOIN Rates R ON FB.RateID = R.RateID

	ORDER BY  TE.EmployeeID, CR.CraftDescription

这是我的存储过程，我想跟据传进来的@psort可以在最后的查询语句上加一个条件，跟据FirstName或着LastName进行desc或着asc排序。
我想过用动态sql，但不知道是不是格式写得有问题，每次都提醒我@tblFringeBenefit不存在。
比较急的一个简单问题，大家帮忙看看。

...全文

134 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

wuxiaoqqqq 2008-10-07

打赏
举报

我想把这个变量加到最后一个查询语句中，应该怎么加？

zero8500 2008-10-07

打赏
举报

好长

pt1314917 2008-10-07

打赏
举报

[Quote=引用 8 楼 wuxiaoqqqq 的回复:]
恩，那个我知道，现在要求在sql中排
[/Quote]

楼主这个存储过程中似乎并没有用到@pSort这个变量啊

wuxiaoqqqq 2008-10-07

打赏
举报

恩，那个我知道，现在要求在sql中排

vfan2010 2008-10-07

打赏
举报

在C#中利用dataview来排序也行的。

wuxiaoqqqq 2008-10-07

打赏
举报

我现在连存储过程语法检查都过不了，每次提示我要DECLARE @tblFringeBenefit
但我确实有这个

tim_spac 2008-10-07

打赏
举报

动态sql是指在字符串中拼接要SQLServer去执行的代码，然后再用exec的方式提交执行。

liangCK 2008-10-07

打赏
举报

FROM 后面指的是变量的话..
那就要用EXEC 或sp_executesql去执行..

水族杰纶 2008-10-07

打赏
举报

动态sql语句基本语法 

1 :普通SQL语句可以用Exec执行 



eg:   Select * from tableName 

         Exec('select * from tableName') 

         Exec sp_executesql N'select * from tableName'    -- 请注意字符串前一定要加N 



2:字段名，表名，数据库名之类作为变量时，必须用动态SQL 



eg:   

declare @fname varchar(20) 

set @fname = 'FiledName' 

Select @fname from tableName              -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 

Exec('select ' + @fname + ' from tableName')     -- 请注意 加号前后的 单引号的边上加空格 



当然将字符串改成变量的形式也可 

declare @fname varchar(20) 

set @fname = 'FiledName' --设置字段名 



declare @s varchar(1000) 

set @s = 'select ' + @fname + ' from tableName' 

Exec(@s)                -- 成功 

exec sp_executesql @s   -- 此句会报错 







declare @s Nvarchar(1000)  -- 注意此处改为nvarchar(1000) 

set @s = 'select ' + @fname + ' from tableName' 

Exec(@s)                -- 成功     

exec sp_executesql @s   -- 此句正确 



3. 输出参数 

declare @num int, 

        @sqls nvarchar(4000) 

set @sqls='select count(*) from tableName' 

exec(@sqls) 

--如何将exec执行结果放入变量中？ 



declare @num int, 

               @sqls nvarchar(4000) 

set @sqls='select @a=count(*) from tableName ' 

exec sp_executesql @sqls,N'@a int output',@num output 

select @num

liangCK 2008-10-07

打赏
举报

动态sql语句基本语法 

1 :普通SQL语句可以用Exec执行 



eg:   Select * from tableName 

         Exec('select * from tableName') 

         Exec sp_executesql N'select * from tableName'    -- 请注意字符串前一定要加N 



2:字段名，表名，数据库名之类作为变量时，必须用动态SQL 



eg:   

declare @fname varchar(20) 

set @fname = 'FiledName' 

Select @fname from tableName              -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。 

Exec('select ' + @fname + ' from tableName')     -- 请注意 加号前后的 单引号的边上加空格 



当然将字符串改成变量的形式也可 

declare @fname varchar(20) 

set @fname = 'FiledName' --设置字段名 



declare @s varchar(1000) 

set @s = 'select ' + @fname + ' from tableName' 

Exec(@s)                -- 成功 

exec sp_executesql @s   -- 此句会报错 







declare @s Nvarchar(1000)  -- 注意此处改为nvarchar(1000) 

set @s = 'select ' + @fname + ' from tableName' 

Exec(@s)                -- 成功     

exec sp_executesql @s   -- 此句正确 



3. 输出参数 

declare @num int, 

        @sqls nvarchar(4000) 

set @sqls='select count(*) from tableName' 

exec(@sqls) 

--如何将exec执行结果放入变量中？ 



declare @num int, 

               @sqls nvarchar(4000) 

set @sqls='select @a=count(*) from tableName ' 

exec sp_executesql @sqls,N'@a int output',@num output 

select @num

liangCK 2008-10-07

打赏
举报

这么长?

adam0730 2008-10-07

打赏
举报

名字真长。。。。

pt1314917 2008-10-07

打赏
举报



--调用

exec usp_GetAllEmployeeEffectiveFringeBenefitsByProjectIDCompanyID 3,4,'a','FirstName desc'

pt1314917 2008-10-07

打赏
举报



--try:

alter PROCEDURE [dbo].[usp_GetAllEmployeeEffectiveFringeBenefitsByProjectIDCompanyID]

    @pCompanyID int,

    @pProjectID int,

    @pFilterChar varchar (1),

    @pSort varchar(50)

AS

BEGIN

	declare @sql nvarchar(4000)

   set @sql=' DECLARE @tblFringeBenefit TABLE

    (

        EmployeeID int,

        FirstName varchar(255),

        LastName varchar(255),

        CraftCode varchar(18),

        FringeBenefitID int

    )



    INSERT INTO @tblFringeBenefit

    SELECT DISTINCT E.EmployeeID, E.FirstName , E.LastName, FB.CraftCode,

            (

               SELECT TOP 1 FB1.FringeBenefitID 

               FROM FringeBenefits FB1 INNER JOIN FringeBenefitsEmployees FBE1 ON (FBE1.EmployeeID = E.EmployeeID OR FBE1.EmployeeID = 0) AND FBE1.FringeBenefitID = FB1.FringeBenefitID

               WHERE FB1.CompanyID = @pCompanyID AND FB1.ProjectID = @pProjectID AND FB1.CraftCode = FB.CraftCode 

               ORDER BY FB1.DurationFrom DESC

            )

    FROM Employees E INNER JOIN ProjectEmployee PE ON E.EmployeeID = PE.EmployeeID AND PE.ProjectID = @pProjectID AND PE.ActiveRecord = 1

                     INNER JOIN FringeBenefitsEmployees FBE ON (FBE.EmployeeID = PE.EmployeeID OR FBE.EmployeeID = 0)

                     INNER JOIN FringeBenefits FB ON FBE.FringeBenefitID = FB.FringeBenefitID AND FB.CompanyID = @pCompanyID AND FB.ProjectID = @pProjectID

    WHERE E.CompanyID = @pCompanyID AND E.ActiveRecord = 1 AND E.LastName like @pFilterChar+''%''



    INSERT INTO @tblFringeBenefit

    SELECT DISTINCT E.EmployeeID, E.FirstName , E.LastName, '''', 0

    FROM Employees E INNER JOIN ProjectEmployee PE ON E.EmployeeID = PE.EmployeeID AND PE.ProjectID = @pProjectID AND PE.ActiveRecord = 1

    WHERE E.CompanyID = @pCompanyID AND E.ActiveRecord = 1 AND E.LastName like @pFilterChar+''%'' AND E.EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM @tblFringeBenefit)





SELECT TE.EmployeeID, TE.FirstName, TE.LastName, 

           FB.FringeBenefitID, FB.CraftCode, FB.ClassCode, FB.StepCode, FB.AppPeriod, FB.ConstructionTrade,

           FB.VacationOrHoliday, FB.VacationOrHolidayPercentage, FB.PaidToNameVacationOrHoliday, FB.PaidToAddressVacationOrHoliday,

           FB.HealthAndWelfare, FB.HealthAndWelfarePercentage, FB.PaidToNameHealthAndWelfare, FB.PaidToAddressHealthAndWelfare,

           FB.Pension, FB.PensionPercentage, FB.PaidToNamePension, FB.PaidToAddressPension,

           FB.ApprenticeOrTraining, FB.ApprenticeOrTrainingPercentage, FB.PaidToNameApprenticeOrTraining, FB.PaidToAddressApprenticeOrTraining,

           FB.OtherFringe1, FB.OtherFringe1Percentage, FB.OtherFringe2, FB.OtherFringe3, 

           FB.OtherFringes, FB.OtherFringesPercentage, FB.PaidToNameOtherFringes, FB.PaidToAddressOtherFringes,

           FB.TravelSubsistancePercentage, FB.TravelSubsistance, FB.DurationFrom, FB.DurationTo, FB.OverRideIndividual, FB.RateID, 

           R.AppBasicHourlyRatePercentage, R.VersionCode, CR.CraftDescription, CL.ClassDescription, IsNull(ST.StepDescription,'''') AS StepDescription

    FROM  @tblFringeBenefit  TE LEFT JOIN FringeBenefits FB ON FB.FringeBenefitID = TE.FringeBenefitID 

                            LEFT JOIN Crafts CR ON FB.CraftCode = CR.CraftCode

                            LEFT JOIN Classes CL ON FB.ClassCode = CL.ClassCode

                            LEFT OUTER JOIN Steps ST ON FB.StepCode = ST.StepCode

                            LEFT OUTER JOIN Rates R ON FB.RateID = R.RateID

    ORDER BY  TE.EmployeeID, CR.CraftDescription,@pSort'

	exec sp_executesql @sql,N'@pCompanyID int,@pProjectID int,@pFilterChar varchar(1),@pSort varchar(50)',@pCompanyID,@pProjectID,@pFilterChar,@pSort

end

go