日期转换并选择子查询 [英] date conversion and select subquery
问题描述
改变程序Sp_GetDetailsLeaves_PaySlip
(
@empid varchar(15),
@fromdate date,
@todate date
)
AS
BEGIN
声明@tot int
声明@pd int
声明@Lt int
声明@Ab int
设置@tot =(SELECT SUM(othrs)as totothrs FROM Tbl_Daily_Attendence其中emp_id = @empid和cdfrom @fromdate和@todate)
set @Lt = (SELECT COUNT(cdate)为Leavestaken FROM Tbl_Daily_Attendence,其中emp_id ='@ empid'并在'@fromdate'和'@todate'之间进行cdate,并且注意'='L')
设置@Ab =(SELECT COUNT(cdate)为Leavestaken FROM Tbl_Daily_Attendence,其中emp_id ='@ empid'并在'@fromdate'和'@todate'之间进行cdate,并且注意'='A')
声明@sql varchar(250)
选择@sql =(选择distinct @tot为Othrs,@ pd为Presentdays,@ Lt作为Leavetaken,@ Ab as Absents from Tbl_Daily_Attendence)
Exec(@sql)
print @sql
END
你的SQL构建(使用SET)不行,你应该使用字符串连接设置你的SQL然后最后调用EXECUTE sp_executesql。
您可以在我的下一篇文章(SPGetContactsByFilterPaginated)中看到一个示例:高级ASPX GridView分页和数据实体 [ ^ ]
只删除所有标量的单引号参数
alter PROCEDURE Sp_GetDetailsLeaves_PaySlip
(
@empid varchar(15),
@fromdate date,
@todate date
)
AS
BEGIN
Declare @tot int
Declare @pd int
Declare @Lt int
Declare @Ab int
set @tot =(SELECT SUM(othrs) as totothrs FROM Tbl_Daily_Attendence where emp_id = @empid and cdate between @fromdate and @todate )
set @Lt =( SELECT COUNT(cdate) as Leavestaken FROM Tbl_Daily_Attendence where emp_id='@empid' and cdate between '@fromdate' and '@todate' and attendencetype ='L')
set @Ab =( SELECT COUNT(cdate) as Leavestaken FROM Tbl_Daily_Attendence where emp_id='@empid' and cdate between '@fromdate' and '@todate' and attendencetype ='A')
Declare @sql varchar(250)
select @sql = (select distinct @tot as Othrs,@pd as Presentdays ,@Lt as Leavetaken,@Ab as Absents from Tbl_Daily_Attendence)
Exec(@sql)
print @sql
END
You SQL builds (by using SET) are not OK, you should setup your SQL by using string concatenations then finally invoke "EXECUTE sp_executesql".
You can see an example in my next article (the SP "GetContactsByFilterPaginated" ): Advanced ASPX GridView Pagination and Data Entities[^]
just remove single quotes to all the scalar parameters.
这篇关于日期转换并选择子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!