Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure

Author

Topic

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-04-12 : 01:28:14

HI,
can any one tel me how to use stored procedure with some return value and how to use that stored procedure in select Statement.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-12 : 01:50:53

The return value's easy. Add RETURN <value to be returned> to the end of the proc. Procedures however can't be used in selects. They have to be EXECUTEd

--
Gail Shaw
SQL Server MVP

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-12 : 02:02:02

Ok to execute sp in select you need to see this.

use master

sp_configure 'Advanced option',1
go
exec sp_configure 'allow updates',1
go
reconfigure with override
go
exec sp_configure 'Ad Hoc Distributed Queries',1
go
reconfigure with override

----
Create procedure sp_ConvProc2View
( @procName varchar(80), @viewName varchar(80))
as
-- -------------------------------------------------------------
-- Procedure name: sp_ConvProc2View
-- Sp Author : Eli Leiba
-- Date 08-2009
-- Description : created a view with same result as the sp
-- the view can be used in a SELECT statement
-- ------------------------------------------------------------
begin
declare @TSQLStmt varchar(500)
set nocount off
-- create the CREATE VIEW tSQL statement.
-- An OPENROWSET operator is used on the local server
-- (. means the local SQL server )
-- using SQLOLEDB provider along with a trusted connection
-- (windows authentication)
-- SET FMTONLY off ensures that the results will be output
-- (not just the metaData)
-- the EXEC storedProcedure finishes the OPENROWSET parameter.
set @TSQLStmt = 'CREATE VIEW ' + @viewName + ' AS SELECT * FROM ' +
'OPENROWSET ( '+ '''' +'SQLOLEDB' + ''''+ ',' +
'''' + 'SERVER=.;Trusted_Connection=yes'+ '''' +',' +
'''' + 'SET FMTONLY OFF EXEC ' + @procName + ''''+ ')'
-- now , we dynamically execute the statement
exec (@TSQLStmt)
set nocount on
end
go
---
exec sp_ConvProc2View @procName = 'sp_Lock', @viewName ='V$LOCK'

--
exec sp_ConvProc2View @procName = 'sp_who', @viewName ='v$Session'

--
--And See how this works using select statement
select * from v$Session where status like '%background%'
select * from V$LOCK
--
-- Example of Join between V$LOCK, V$SESSION and sys.objects to
-- join together to session status, database name , statement type,
-- name of object locked, lock type mode and status in a single query
SELECT S.status,S.dbName,S.cmd,O.name, L.TYPE,L.mode,L.status
FROM V$SESSION S,V$LOCK L, sys.objects O
where L.OBJID = O.object_id and
S.SPID = L.SPID

Raghu' S

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-04-12 : 04:43:46

thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-12 : 06:31:42

quote:
exec sp_configure 'allow updates',1

Why on earth set this? It's just going to cause problems down the line (maint plans will promptly start failing) and it is of no use whatsoever.

p.s. Raghu, ad-hoc distributed queries can be a security risk, in many environments they are disabled and you won't be allowed to re-enable.
Before going that route, maybe explain why you want to do this, there's likely a better solution.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources