如何编写带有日期范围和emptype作为输入的存储过程来检索.netchart上的日期？ [英] How to write stored procedure with date range and emptype as input to retrieve date on .netchart?

问题描述

我有表格emp，其中有包括临时，烫发和季节性的空白。

我需要找到关于所有空白或临时，烫发和季节性下拉菜单的信息。 />
我该怎么办？我在这个项目中使用C＃与sql server 2014

这是web应用程序

I have table emp in which there is emptype which includes temp, perm and seasonal.
I am in need of find the information regarding all emptype or temp, perm and seasonal in dropdown.
how can I do that? I am using C# in this project with sql server 2014
And this is web application

推荐答案

你应该有一个参考完整性的转换表，然后你会查询那个表，而不是员工表。

You should have a translation table for referential integrity, you would then query that table, not the employee table.

EmpType
ID Name
1  Permanent
2  Temporary
3  Seasonal

Employee
ID EmpType Name ...
1  1       Bob
2  1       Joe
3  2       Bill

在开始之前，请申请更改解决方案1中建议 PIEBALDconsult [ ^ ]。



你可以通过将emptype提取到新表来实现这一点：

Before you start, please apply changes suggested in solution1 by PIEBALDconsult[^].

You can achieve that by extracting emptype to new table:

--create new table
CREATE TABLE EmpTypes
(
    EmpTypeID INT IDENTITY(1,1)
    TypeDescription NVARCHAR(50)
)

--insert empTypes
INSERT INTO EmpTypes(TypeDescription)
SELECT DISTINCT EmpType
FROM Emp

--update emp table
ALTER TABLE Emp ADD COLUMN EmpTypeID INT REFERENCES EmpTypes(EmpTypeID)

UPDATE t1 SET t1.EmpTypeID = t2.EmpTypeID
FROM Emp AS t1 INNER JOIN EmpTypes AS t2 ON t1.EmpType = t2.TypeDescription 

ALTER TABLE Emp DROP COLUMN EmpType

注意：上面的查询可能包含错误，我无法测试它。



最后，创建程序：

Note: above query could contain errors, i couldn't test it.

Finally, create procedure:

CREATE PROCEDURE GetObviousData
    @startdate DATETIME,
    @enddate DATETIME,
    @emptype INT
AS
BEGIN
    --SELECT statement
    SELECT t1.<Field_List2>, t2.<Field_List>
    FROM Emp AS t1 INNER JOIN EmpTypes AS t2 ON t1.EmpTypeID = t2.EmpTypeID
    WHERE t1.EmpTypeId = @emptype AND t1.HireDate BETWEEN @startdate AND @enddate
END

MSDN文档：

ALTER TABLE [ ^ ]

更新 [<小时ef =http://msdn.microsoft.com/en-us/library/ms177523.aspxtarget =_ blanktitle =新窗口> ^ ]

< a href =http://msdn.microsoft.com/en-us/library/ms187926.aspx>创建程序 [ ^ ]

创建表 [ ^ ]

MSDN documentation:
ALTER TABLE[^]
UPDATE[^]
CREATE PROCEDURE[^]
CREATE TABLE[^]

我有store proc，它有3个参数，其中有startdate enddate和emptype这三个变量或参数利用来自.NETCHART的数据网格或饼图和饼图有点击事件，导致datagriid



非常感谢

I have store proc which has 3 parameter in which there is startdate enddate and emptype which this three variable or parameter utilize populate datagrid or piechart from .NETCHART and piechart has click event which result in datagriid

Thank you very much

这篇关于如何编写带有日期范围和emptype作为输入的存储过程来检索.netchart上的日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！