Using SP result as a variable - 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)

Using SP result as a variable

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-08-14 : 11:32:16

I would like to call a stored procedure and assign it's results to a variable to be used in a select statement. Can someone help me out? Below is what I am using but "Incorrect syntax near the keyword 'exec'."

Thanks

declare @test varchar(3)
set @test = exec dbo.stp_ReturnBranchesFix '4'

select Occ.Company_Name, @test as Branches
from aTable

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 11:38:58

you can't do that.

If the stored proc returns a result set you can do an INSERT EXEC. You have to create a temp table that matches the output of the stored proc exactly something like


CREATE TABLE #foo (
    [Id] INT
    , [val] NVARCHAR(255)
    )

INSERT #foo
EXEC yourStoredProc

Where yourStoredProc returns a result set of [ID] INT and [val] NVARCHAR(255)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 11:40:31

What does the stored proc return?

You know the drill!
Sample data and expected output!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-08-14 : 11:48:01

My SP returns a single varchar row....
Below is essentially what I am trying to do (I know it will not work, but it may give you a better idea).

select
Company_Name,
'Brancehs' = exec dbo.stp_ReturnBranchesFix home_office,
from
LINKEDSERVER.DB.Company

Where I am passing a single home office to the SP.
The SP would return a single row of concatinated data.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 12:02:07

can you turn it into a function? (or does it use dynamic sql

You could use an OUTPUT variable if you wanted to.

Declare a variable in the paramater list with the OUTPUT keyword and then pass it the containser variable with the OUTPUT keyword

like this


IF OBJECT_ID ('CG_exampleProc') IS NOT NULL DROP PROCEDURE CG_exampleProc
GO

CREATE PROCEDURE CG_exampleProc
	@outputVar NVARCHAR(MAX) OUTPUT
AS BEGIN
	SELECT TOP 1 @outPutVar = [name] FROM sys.databases ORDER BY [name] DESC
END
GO

-- Example use

DECLARE @foo NVARCHAR(MAX)

EXEC dbo.CG_exampleProc @foo OUTPUT

SELECT @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

shalagur
Starting Member

0 Posts

Posted - 2009-08-15 : 04:11:03

as charlie said better to make it a function so that you can call it for each value in your resultset

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources