Is it Possible to Use the Select Statement to Access Stored Procedures

Introduction

One of the differences between functions and stored procedures is, we can use the "select" statement in functions whereas we can't use the same in stored procedures.

Can we make this statement false????? Not completely but a little bit.

Before that, am trying to create a Linked server.

Goto SSMS -> Your DB Server -> ServerObjects -> Linked Server - > Create a new Linked Server.

You will get a message box to type your Linked server. You need to provide the servername.

Specify whether it's SQL Server or not. If it's not SQL Server, provide the necessary details to connect it.

The second window is authentication, you need to specify the credentials used to access the Linked Server.

The third window specifies the options to be provided for the Linked server. If everything is good. Click the "Ok" button.

Now, your Linked Server is created.

Now, you can see your catalogs listed under the Linked Server.

Now, am trying to access the Linked Server's table,

SELECT * FROM LinkedServer.VENKATDB.dbo.VENKAT_TABLE

Awesome,

Am able to see my data.

Go to your Linked Server query window and create a procedure (use the below code).

DROP TABLE VENKAT_TABLE_LINKED
CREATE TABLE VENKAT_TABLE_LINKED (ID INT)
INSERT INTO VENKAT_TABLE_LINKED VALUES (1)
INSERT INTO VENKAT_TABLE_LINKED VALUES (2)
INSERT INTO VENKAT_TABLE_LINKED VALUES (3)
INSERT INTO VENKAT_TABLE_LINKED VALUES (4)

GO

DROP PROCEDURE VENKAT_PROCEDURE
GO

CREATE PROCEDURE VENKAT_PROCEDURE
AS
BEGIN
SELECT * FROM VENKAT_TABLE_LINKED
END
GO

EXEC VENKAT_PROCEDURE
GO

Now, your procedure is created in the Linked server, and am trying to access that procedure from the actual server using a select statement. Le's see how to do it.

This can be achieved using an OpenQuery statement.

SELECT * FROM OPENQUERY ([LinkedServerName],'MASTER.DBO.VENKAT_PROCEDURE')

So, the statement is wrong :-)

The actual statement is, "Stored procedures can't use Select statement in its own server"

Hope, you all agree with my statement.

Cheers,

Venkatesan Prabu .J