Stored procedure for swapping two numbers

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 2008 Forums

Transact-SQL (2008)

Stored procedure for swapping two numbers

Author

Topic

chinlax
Starting Member

30 Posts

Posted - 2011-10-10 : 00:43:19

Hi,

I have written the stored procedure for swapping two numbers
CREATE PROCEDURE Usp_Swapping
(@a int=40,
@b int=50,
@c int=0)
AS
Begin
@a=@b
@c=@b
@b=@a
end
GO

Execute Usp_Swapping
--print @a
--print @b;

It was giving the following error.
Msg 102, Level 15, State 1, Procedure Usp_Swapping, Line 112
Incorrect syntax near '@a'.
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'Usp_Swapping'.

Can any one help me out, where i am wrong and whats need to be done in order create the stored procedure for swapping two numbers.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 00:49:18

you will need to use SET or SELECT to assign a variable with another value / variable


SELECT @a = @b

or 

SET @a = @b

KH
[spoiler]Time is always against us[/spoiler]

chinlax
Starting Member

30 Posts

Posted - 2011-10-10 : 00:58:34

Yes now its ok, but where to write the print statement. i want to print the value of a and value of b. where should i write it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 01:37:18

actually ...
1. you should use OUTPUT parameter
2. you don't need the @c as a input paramter
3. maybe i misunderstood your requirement but your swapping logic is wrong

your stored procedure should be


CREATE PROCEDURE Usp_Swapping
	@a int	OUTPUT,
	@b int	OUTPUT
AS
Begin
	declare	@c	int

	select	@c = @a
	select	@a = @b
	select	@b = @c
end
GO

below is an example how you will execute it


declare @a 	int,
	@b	int

select	@a	= 11,
	@b	= 22

exec	Usp_Swapping @a = @a OUTPUT, @b = @b OUTPUT

select	@a, @b

KH
[spoiler]Time is always against us[/spoiler]

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-10 : 02:31:15

Hi,

Please use SET to assign value to variable.

CREATE PROCEDURE Usp_Swapping
(@a int=40,
@b int=50,
@c int=0)
AS
Begin
set @a=@b
set @c=@b
set @b=@a
end
GO

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources