如何使用sql server通过存储过程返回表结果 [英] How to return table results through stored Procedure using sql server

问题描述

大家好，

请帮我解决我的SQL问题，我是SQL服务器的新手。



请找到我的下图这表明我的实际原始表数据和我在下面写了查询到所需的输出。

Hi All,
Please help me with my SQL problem, I ma new to SQL server.

Please find my below image which shows that my actual raw table data and I have written below query to to the required output.

CREATE PROCEDURE GetImmediateManager
   @OSType varchar(10)
 
AS
BEGIN

DECLARE @columns VARCHAR(8000)
declare @str varchar(100)

SELECT @columns = COALESCE(@columns + ',[' + cast(osversion as varchar) + ']',
'[' + cast(osversion as varchar)+ ']')
FROM dbo.Table_1 where Type = '@OSType'
GROUP BY osversion

DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *
FROM (select * from dbo.Table_1 where Type = ''' + '@OSType'+''' ) as t
PIVOT
(
MAX(Type)
FOR [OSVersion]
IN (' + @columns + ')
) AS p'

EXECUTE(@query)
END

你能帮我解决一下如何从存储过程中返回多个值。一旦我获得SP数据，在我需要绑定到数据网格视图之后。



您的快速响应将对我的开发团队有很大帮助，

Could you please help me how do I get multiple values returned from stored procedure. Once I get the SP data ,after I need to bind to Data Grid View.

Your quick response will be helps a lot for my development team,

推荐答案

您可以将查询结果存储在临时表中。

然后在该表上选择结果并返回给调用者。

You can store the results of your query in a temporary table.
Then select results over that table and return to the caller.

非常感谢Abhinav的快速回复。

我用下面提到的查询解决了我的问题

Thanks a lot Abhinav for your quick response.
I have resolved my problem with the below mentioned query

DECLARE @columns VARCHAR(8000)
DECLARE @strIn VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(OS_version as varchar) + ']',
'[' + cast(OS_version as varchar)+ ']')
FROM dbo.sp_MACPOSInformation1 where OS_Name = 'IOS'
GROUP BY OS_version

DECLARE @query VARCHAR(8000)

select @query =  'SELECT *
FROM (select * from dbo.sp_MACPOSInformation1 where OS_Name = '''+'IOS'+''' ) as t
PIVOT
(
MAX(OS_Name)
FOR [OS_version]
IN (' + @columns + ')
) AS p'

execute(@query)

这篇关于如何使用sql server通过存储过程返回表结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！