Sql从storedprocedure中选择id

本文介绍了Sql从storedprocedure中选择id的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我收到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')