problems with out param while executing procedure

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)

problems with out param while executing procedure

Author

Topic

sqlstudentap
Starting Member

1 Post

Posted - 2009-04-15 : 07:19:52

I have written this procedure
and while compiling i have not get any errors

CREATE PROCEDURE loginp1 (@p1 NVARCHAR(50),@p2 nvarchar(20),@p3 int output)
AS return
begin
SELECT @p3=count(*)
FROM logintb
WHERE uname = @p1 and pwd=@p2
return @p3
print @p3
end

But when i executing this pprocedure

with command

exec loginp1 'gopal','1234'

i got an error

Procedure or function 'loginp1' expects parameter '@p3', which was not supplied.

please let me know how to over come this error

sqlstudent

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-15 : 07:28:16

Well firstly you've set up your SP to expect three parameters (i.e. 3 peices of information to work with) :
(@p1 NVARCHAR(50),@p2 nvarchar(20),@p3 int output)
. . and only passed two parameters to it.

However, on closer inspection, you don't actually need to pass three parameters as p3 is created and processed within the SP. Therefore remove the ',@p3 int output' from your declaration and you should be okay then.

You may need to declare your parameter within the SP actually, something like DECLARE @p3 int should do the trick.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-15 : 07:29:18

Whatever you are expecting this sp to do it isn't going to do it.

OUTPUT paramaters work like this


IF OBJECT_ID('dbo.foo') IS NOT NULL DROP PROCEDURE dbo.foo
GO

CREATE PROCEDURE dbo.foo
	@a INT
	, @b INT
	, @c INT OUTPUT
AS BEGIN
	SET @c = @a + @b
END
GO

DECLARE @a1 INT, @b1 INT, @c1 INT

SELECT @a1 = 1, @b1 = 2

EXEC dbo.foo @a1, @b1, @c1 OUTPUT

SELECT @c1

WHERE you pass a variable name as each OUTPUT param.

Also -- your RETURN statements abort the stored proc -- they don't do what you think they do.

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

svicky9
Posting Yak Master

232 Posts

Posted - 2009-04-15 : 07:29:20

You need to declare the Output Parameter variable

declare @op int
exec loginp 'gopal','1234',@op

select @op

http://www.sqlserver007.com

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-15 : 07:38:22

On second thoughts, you perhaps should consider doing this in a functin rather than in an SP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources