November 5, 2009 at 11:47 pm
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..
November 6, 2009 at 12:10 am
Error was generous enough to tell you what the problem was 🙂
declare @VAR varchar(10)
execute TESTPROC @VAR output
PRINT @var
---------------------------------------------------------------------------------
November 6, 2009 at 1:43 am
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
*/
November 6, 2009 at 1:51 am
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
November 6, 2009 at 2:11 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 6, 2009 at 3:58 am
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.....
November 6, 2009 at 4:08 am
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
November 6, 2009 at 5:22 am
sorry, i didn't get your point
can you give me an example..
Thanks
Rock...
November 6, 2009 at 5:25 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply