Post
Ask Question

Call Store Procedure In Select Statement By Using OpenRowSet

Enable below configuration in server level,

sp_configure 'Show Advanced Options ',1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
go

Create & Insert Records 


CREATE TABLE [dbo].[Tbl_SysPara](
	[Sys_ID] [bigint] IDENTITY(2,2) NOT NULL,
	[Sys_ParaName] [varchar](max) NULL,
	[Sys_ParaVal2] [varchar](max) NULL,
	[Sys_Isdelete] [int] NULL,
	[Sys_ParaExpireDatetime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[Sys_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Tbl_SysPara] ON 
GO
INSERT [dbo].[Tbl_SysPara] ([Sys_ID], [Sys_ParaName], [Sys_ParaVal2], [Sys_Isdelete], [Sys_ParaExpireDatetime]) VALUES (2, N'Transaction Email', N'
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
    <!--<img src="http://www.aspsnippets.com/images/Blue/Logo.png" /><br />-->
    <br />
    <div style="border-top: 3px solid #22BCE5">
        &nbsp;
    </div>
    <span style="font-family: Arial; font-size: 10pt">
        Dear <b>{CustName}</b>,<br />
        <br />
        Youre Account No : {AC_NO} Has Been {Type} Rs : {AMOUNT}

        <br />
        Paid Amount To : <b>{TranName}</b>

        <br/>

        Avalaible Balance is: {Bal}
        
        <br />
        <br />
        Sender TranID & Name : {TranName}
        <br />
        <br />
        <a style="color: #22BCE5" href="{Url}">{Title}</a><br />
        {Description}
        <br />
        <br />
		   <br />
        <br />
		<br />
        <a style="color: #22BCE5" href="https://drive.google.com/file/d/1j3UziQJzV7DuB8pxODAil13oNJIAqDNl/view?usp=sharing">Click me Download Sbank Android App</a><br />
        
        <br />
        <br />
        Thanks<br />
        Sai Bank Online Solution<br />
        Mo No : +91-9673178777
    </span>
</body>
</html>



', 0, CAST(N'2027-04-11T15:06:23.320' AS DateTime))
GO
INSERT [dbo].[Tbl_SysPara] ([Sys_ID], [Sys_ParaName], [Sys_ParaVal2], [Sys_Isdelete], [Sys_ParaExpireDatetime]) VALUES (4, N'AccountOp', N'
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
    <!--<img src="http://www.aspsnippets.com/images/Blue/Logo.png" /><br />--
    <br />
    <div style="border-top: 3px solid #22BCE5">
        &nbsp;
    </div>
    <span style="font-family: Arial; font-size: 10pt">
        Dear &nbsp;<b>{CustName}</b>,<br />
       
        {msg}
        <br />
        <br />
        <b>Account Details </b>
        <br />
        <br />
        Youre Account No : {AC_NO} 
        <br />
        Account Type : {Type}
        <br />
        Account Opening Date : {AC_Opdate}

        <br />

        Debit Card No : {AC_DebitCardNo}
        <br />
        CVV No : {AC_CVV}

        <br />
        <br />
       Account Op. Balance : {Bal}
        <br />
        <br />
        <b>Login Details :</b>
        <a style="color: blue" href="{Link}">Click Here To Login</a><br />
        <br />
        User ID : {CEmail}<br />
        Password: {Pass}
        <br />
        <a style="color: #22BCE5" href="{Url}">{Title}</a><br />
        {Description}
        <br />
        <br />
		<br />
        <a style="color: #22BCE5" href="https://drive.google.com/file/d/1j3UziQJzV7DuB8pxODAil13oNJIAqDNl/view?usp=sharing">Click me Download Sbank Android App</a><br />
        
        <br />
        <br />
        Thanks<br />
        Sai Bank Online Solution<br />
        Mo No : +91-9673178777
    </span>
</body>
</html>
', 0, CAST(N'2027-04-11T15:07:34.760' AS DateTime))
GO
INSERT [dbo].[Tbl_SysPara] ([Sys_ID], [Sys_ParaName], [Sys_ParaVal2], [Sys_Isdelete], [Sys_ParaExpireDatetime]) VALUES (20004, N'BackupDate', N'0', 0, CAST(N'2022-09-04T12:37:57.880' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Tbl_SysPara] OFF
GO
ALTER TABLE [dbo].[Tbl_SysPara] ADD  DEFAULT (dateadd(year,(7),getdate())) FOR [Sys_ParaExpireDatetime]
GO

Create Procedure

CREATE Proc  [dbo].[Proc_GetSysPara_v1]
(@Sys_ID bigint
)
as

Select Sys_ParaName, Sys_ParaVal2 , Sys_ParaExpireDatetime
from Tbl_SysPara with(nolock) WHERE Sys_ID=@Sys_ID
Return

Execute Select Statement

DECLARE @Sys_ID bigint=2;
DECLARE @sqlCommand Nvarchar(1000)
SET @sqlCommand=
'select * from openrowset(''SQLNCLI'',''server=.;trusted_connection=yes;''
,''exec  SqlBank. [dbo].[Proc_GetSysPara_V1] ' +CAST(@Sys_ID as varchar(max))+''')'
exec sp_executesql @sqlCommand

Output
Next Recommended Reading How To Call A Function In SQL Server Stored procedure