Sql从storedprocedure中选择id [英] Sql select id from storedprocedure

问题描述

我收到EmployeeID时遇到问题。我有一个存储过程，它返回一个文本框名称+姓氏+ docNo，如'Richarson Michael 674UK'。下面的Stroed程序：

I have problem with receiving EmployeeID. I have stored procedure which returns on one textbox name + surname + docNo like 'Richarson Michael 674UK'. Stroed procedure below:

CREATE PROCEDURE [dbo].[SearchEmployee]
	@employeeSurname varchar(80)

AS
	SELECT (surname+ ' ' + name+ ' ' + DocNo) as surname, EmployeeId
	FROM Employee
	WHERE surname LIKE @employeeSurname+ '%'
RETURN

我还有第二种方法应该从上面的TexBox返回员工ID。我的方法总是重新运行0.我的方法如下：

I have also second method which should return Employees ID from TexBox above. My method reruns always 0. My method is below:

public int returnEmployeeId (string text) {
int employeeId = 0;
string query = "SELECT EmployeeId"
+ " FROM Employee WHERE surname LIKE'%" + text + "%'";
(...) 

return employeeId;
}

我的尝试：



我试图以不同的方式创建一个名为returnEmployeeId的方法。我想使用我的strored过程，其中SqlDataReader返回employeeId，但它总是返回0.我也试图创建不同的查询，但始终相同。显示数据的TextBox已将subBmited AutoPostBAck设为true。有人可以给我一个提示或线索如何解决我的问题。

提前谢谢。

What I have tried:

I have tried to create method called returnEmployeeId for different ways. I wanted to use my strored procedure where SqlDataReader returns employeeId, but it always returns 0. I was also trying to create different query but always the same. TextBox where the data is shown has subbmited AutoPostBAck for true. Can someone give me a tip or a clue how to solve my problem.
Thank you in advance.

推荐答案

而不是使用存储procdure你应该使用一个表值函数返回一个表



例如

Rather than using a stored procdure you should use a table valued function that returns a table

eg

CREATE FUNCTION [dbo].[SearchEmployee]
(@employeeSurname varchar(80))
RETURNS TABLE 
AS
RETURN 
(SELECT (surname+ ' ' + name+ ' ' + DocNo) as surname, EmployeeId
	FROM Employee
	WHERE surname LIKE @employeeSurname+ '%'
)

因此你可以像任何普通餐桌一样对待它



ie

Thus you can treat it like any normal table

ie

Select * from Searchemployee ('Bob')

这篇关于Sql从storedprocedure中选择id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！