如何使用带有传递参数的存储过程从数据库检索列值的多行 [英] how can retreive multiple rows of a column values from DB using a stored procedure with passing parameters

问题描述

如何使用带有传递参数的存储过程从数据库中检索列值的多行.

sp_getcolumnsdata(传递输入参数和输出参数)
基于此，我想返回一列的多行

请帮助我

how can retrieve multiple rows of a column values from DB using a stored procedure with passing parameters.

sp_getcolumnsdata( pass an input parameter and output parameters)
based on it i want to return a multiple rows of a column

Please help me

推荐答案

根据您的问题，我的理解如下:
您需要在多行中返回列值(逗号分隔或制表符分隔).像下面的例子:
列值是:"AB，CD，EF，GH"
返回值是:
AB
CD
EF
GH

请参见下面的

Based on you question my understanding is as below :
You need to return column values (comma separated or tab separated) in multiple rows. Like below example :
column value is : "AB,CD,EF,GH"
return value is :
AB
CD
EF
GH

Please see below

create table Testdata(SomeID int, OtherID int, Data varchar(max))
insert Testdata select 1, 9, '18,20,22'
insert Testdata select 2, 8, '17,19'
insert Testdata select 3, 7, '13,19,20'
insert Testdata select 4, 6, ''


;with tmp(SomeID, OtherID, DataItem, Data) as (
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from Testdata
union all
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select SomeID, OtherID, DataItem
from tmp
order by SomeID

这是什么问题?您不需要输出参数即可获取输出行.您可以简单地选择它并使用DataReader在ADO.NET中检索它.试试看:

What is the problem with this? you do not need output parameters to get the output rows. You can simply select it and retrieve it in ADO.NET using DataReader. Try this up:

CREATE PROCEDURE sp_getcolumnsdata
(
@Username varchar(30),
@Password varchar(30)
)
BEGIN

SELECT firstname, lastname, age, address, city -- will return multiple rows and columns
FROM   [TableName]

END

问候，
爱德华

Regards,
Eduard

如果只需要检索多行和多列，则不需要输出参数.只有您需要过滤条件作为输入参数.

谢谢
-amit.

Hi,

if you need to retrieve multiple rows and column only, you do not need output parameters. only you need filter condition as input parameter.

thanks
-amit.

这篇关于如何使用带有传递参数的存储过程从数据库检索列值的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！