Problem with execution of sp with Outparameter

Question

Post reply

Problem with execution of sp with Outparameter

RockingAdmin

SSCrazy

Points: 2828
More actions
November 5, 2009 at 11:47 pm

#156379

Hi
i created a test proc, i am getting error while executing
ALTER PROC TESTPROC
(@VAR NVARCHAR(50) OUTPUT)
AS
BEGIN
SELECT @var= 'test'
END
-- execute TESTPROC @var output
error: Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@VAR".
can any one guide me on this one
Thanks
Rock..

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Error was generous enough to tell you what the problem was 🙂

declare @VAR varchar(10)

execute TESTPROC @VAR output

PRINT @var

---------------------------------------------------------------------------------

RockingAdmin SSCrazy Points: 2828 More actions · Answer 2

how can i pass multiple values into a variable

ex:

ALTER PROC TESTPROC

(@VAR NVARCHAR(max) OUTPUT)

AS

BEGIN

SELECT @var= empname from emp where sal >10000;

END

/*declare @var NVARCHAR(MAX)

execute TESTPROC @var output

*/

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

You cannot store multiple values in one variable. Use a SELECT within your procedure and a temp table (or table variable) to receive the values.

CREATE PROCEDURE selectMany

AS

SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3;

GO

DECLARE @destination TABLE

(

SomeInt INT

);

INSERT INTO @destination

EXECUTE selectMany;

SELECT * FROM @destination;

Greets

Flo

Paul White SSC Guru Points: 150468 More actions · Answer 4

You could also use XML:

CREATE PROCEDURE dbo.SelectMany

@Data XML OUTPUT

AS

BEGIN

SELECT

@Data =

(

SELECT data

FROM (

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3

) T1 (data)

FOR XML PATH, ROOT('root'), TYPE

);

END

GO

DECLARE @Data XML;

EXECUTE dbo.SelectMany @Data OUTPUT;

SELECT data = Data.value('(./data)[1]', 'INTEGER')

FROM @Data.nodes('./root/row') Nodes (Data);

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

RockingAdmin SSCrazy Points: 2828 More actions · Answer 5

actually i need to use two procedures

procedure-1:

ALTER PROC TESTPROC

(@VAR NVARCHAR(max) OUTPUT)

AS

BEGIN

declare @TAB table( data nvarchar(max)

insert into @TAB SELECT empno from emp where sal >10000;

select @var= data from @TAB

END

/*declare @var NVARCHAR(MAX)

execute TESTPROC @var output

*/

procedure-2:

ALTER PROC DELPROC

AS

BEGIN

declare @var NVARCHAR(MAX)

EXECUTE testdb.dbo.TESTPROC @var OUTPUT

-- HERE I NEED TO GET ALL THE LIST OF EMPNO FROM PROCEDURE1

DELETE FROM tbl_test where empno in (@VAR)

END

I think u got my point..

Thanks

Rock.....

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

rockingadmin (11/6/2009)
I think u got my point..

Yep, we got your point 😉

As I showed you, use a SELECT to return the data back to the caller of your TESTPROC.

Use a joined DELETE to delete the returned employees from your tbl_test.

Greets

Flo

RockingAdmin SSCrazy Points: 2828 More actions · Answer 7

sorry, i didn't get your point

can you give me an example..

Thanks

Rock...

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Hi

Er.. my first post contains a complete sample how to create a procedure, return several rows and catch the result from calling side.

Which part is missing?

Greets

Flo