tell me: my procedure not returng proper value

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

SQL Server Development (2000)

tell me: my procedure not returng proper value

Author

Topic

senthilramtrs
Starting Member

29 Posts

Posted - 2008-05-23 : 03:12:30

This procedure returns -1 always?
but i want 0 or greater than 0

--

CREATE PROCEDURE CheckAny

AS

set nocount off

Exec ( 'select * from Entry where Status="Entry"')

Return @@ROWCOUNT

GO

--

i'm calling this procedure like this :

SqlConnection Con = new SqlConnection(ConfigurationManager.AppSettings["Connection_String"]);

Con.Open();
SqlCommand Cmd = new SqlCommand("CheckAny", Con);
Cmd.CommandType = CommandType.StoredProcedure;

int ret = Cmd.ExecuteNonQuery();

--

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 03:24:40

Do not use dynamic SQL unless abolutely and totally needed!

CREATE PROCEDURE CheckAny 
AS
set nocount off

select * from Entry where Status = 'Entry'

Return @@ROWCOUNT

E 12°55'05.25"
N 56°04'39.16"

senthilramtrs
Starting Member

29 Posts

Posted - 2008-05-23 : 04:11:23

CREATE PROCEDURE CheckAny

AS

set nocount off

select * from Entry where Status='Entry'

print @@Rowcount

Return @@ROWCOUNT

GO

--

this too returns -1 ....
but prints number of records if i executes like "Exec CheckAny" in query analizer ...

...

Regards,
Senthil Ram TRS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 05:06:07

Why did you include PRINT now?
Don't you know @@ROWCOUNT is highly volatile?

CREATE PROCEDURE CheckAny
AS
set nocount off

declare @rc int

select * from Entry where Status = 'Entry'

set @rc = @@rowcount
print @rc
Return @rc

E 12°55'05.25"
N 56°04'39.16"

senthilramtrs
Starting Member

29 Posts

Posted - 2008-05-24 : 02:16:37

tell me since it is volatile.... whether it will affect the result(return value)... while after deployment.... if more threads on Sql server if it runs? at Server Machine ?

Regards,
Senthil Ram TRS

senthilramtrs
Starting Member

29 Posts

Posted - 2008-05-24 : 02:27:38

Hi Peso,

now what i did is just copy pasted ur code and did like ...

CREATE PROCEDURE CheckAny1

AS

set nocount off

declare @rc int

select * from Entry where Status = 'Entry'

set @rc = @@rowcount

print @rc

Return @rc

--
codebehind is :

SqlCommand sCmd = new SqlCommand("CheckAny1", Con);
sCmd.CommandType = CommandType.StoredProcedure;
int rt = sCmd.ExecuteNonQuery();

--

but now too rt value is -1
any more suggestion?

Regards,
Senthil Ram TRS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 03:51:09

quote:
Originally posted by senthilramtrs

Hi Peso,

now what i did is just copy pasted ur code and did like ...

CREATE PROCEDURE CheckAny1

AS

set nocount off

declare @rc int

select * from Entry where Status = 'Entry'

set @rc = @@rowcount

print @rc

Return @rc

--
codebehind is :

SqlCommand sCmd = new SqlCommand("CheckAny1", Con);
sCmd.CommandType = CommandType.StoredProcedure;
int rt = sCmd.ExecuteNonQuery();

--

but now too rt value is -1
any more suggestion?

Regards,
Senthil Ram TRS

Make a OUPUT parameter in your procedure and return the value through it. Something like:-

CREATE PROCEDURE CheckAny1
@rc int OUTPUT 
AS

set nocount off


select * from Entry where Status = 'Entry'

set @rc = @@rowcount

print @rc

go

And see how you get the return value in your code

[url]http://www.sqlteam.com/article/stored-procedures-returning-data[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources