{"id":195,"date":"2012-05-09T11:11:18","date_gmt":"2012-05-09T11:11:18","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/05\/09\/how-to-call-or-execute-a-stored-procedure-from-inside-a-select-statement\/"},"modified":"2012-05-09T11:11:18","modified_gmt":"2012-05-09T11:11:18","slug":"how-to-call-or-execute-a-stored-procedure-from-inside-a-select-statement","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/05\/09\/how-to-call-or-execute-a-stored-procedure-from-inside-a-select-statement\/","title":{"rendered":"How to call or execute a Stored Procedure from inside a Select Statement"},"content":{"rendered":"

How to call or execute a Stored Procedure from Select Statement in SQL Server?<\/strong><\/p>\n

It is really simple to call a stored procedure from a select statement Using OPENROWSET.<\/p>\n

Below TSQL Query calls the procedure sp_who from the select statement.<\/p>\n

[sql]
\nSELECT * FROM
\n OPENROWSET(‘SQLNCLI’
\n ,’Server=(local);Trusted_Connection=Yes;Database=Master’
\n ,’EXEC dbo.sp_Who’)
\n[\/sql]<\/p>\n

Syntax:
\n[sql]
\nSELECT * FROM
\n OPENROWSET(‘SQLNCLI’
\n ,’Server=(local);Trusted_Connection=Yes;Database=Master’
\n ,’EXEC [procedurename]’)
\n[\/sql]<\/p>\n

Additional Information:<\/strong>
\nThe data that is coming from the procedure can also be filtered in the where clause.<\/p>\n

[sql]
\nSELECT * FROM
\n OPENROWSET(‘SQLNCLI’
\n ,’Server=(local);Trusted_Connection=Yes;Database=Master’
\n ,’EXEC dbo.sp_Who’)
\nwhere dbname = ‘master’
\n[\/sql]<\/p>\n

The data from the execution of a select statement can be directly pushed into a table.<\/p>\n

[sql]
\nSELECT * INTO who2_table FROM
\n OPENROWSET(‘SQLNCLI’
\n ,’Server=(local);Trusted_Connection=Yes;Database=Master’
\n ,’EXEC dbo.sp_Who’)
\nwhere dbname = ‘master’
\n[\/sql]<\/p>\n

If the openrowset is disabled in the system, learn how to enable it using this link enable openrowset<\/a><\/p>\n

Related to SQL Server 2005,SQL Server 2008,SQL Server 2012<\/p>\n","protected":false},"excerpt":{"rendered":"

How to call or execute a Stored Procedure from Select Statement in SQL Server? It is really simple to call a stored procedure from a select statement Using OPENROWSET. Below TSQL Query calls the procedure sp_who from the select statement. [sql] SELECT * FROM OPENROWSET(‘SQLNCLI’ ,’Server=(local);Trusted_Connection=Yes;Database=Master’ ,’EXEC dbo.sp_Who’) [\/sql] Syntax: [sql] SELECT * FROM OPENROWSET(‘SQLNCLI’…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,1921,1789],"tags":[],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/195"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=195"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/195\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}