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

In one of the difference 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 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 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 "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.



Goto 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 select statement. Le's see how to do it.

This can be achieved using 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 it's own server"

Hope, you all agree with my statement.

Cheers,

Venkatesan Prabu .J