problem in SP execution - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

problem in SP execution

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2011-04-19 : 02:54:47

Hi,

I am working in sqlserver2008

When i try to execute my stored procedure which has single select statement it provides two result sets.

1st resultset will be output of my query written in the procedure.

2nd wil be return value shows as 0.

how to avoid the second result set . if i excute my procedure it should give the result set for the query wriiten under that . So please help me in this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-19 : 03:00:50

can you show us how your stored procedure looks like and how do you execute it

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2011-04-19 : 03:12:39

Here is my procedure :

CREATE PROCEDURE SampleProc

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT * from sample
END

When i execute this using execute option in sqlserver IDE it produces two results sets with the follwoing code

USE [Check]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[SampleProc]

SELECT 'Return Value' = @return_value

GO

Exec

So how to avoid the second result set . please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-19 : 03:16:22

quote:
So how to avoid the second result set . please help me

the 1st result is from the Stored Procedure, the 2nd result is from the SELECT statement of "Return Value"


USE [Check]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[SampleProc]

SELECT 'Return Value' = @return_value

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2011-04-19 : 03:18:53

The below code is auto generated code when i try to excecute the produre using Execute stored procedure option in the IDE.

USE [Check]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[SampleProc]

SELECT 'Return Value' = @return_value

How to supress the return value. please help me

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-19 : 03:28:28

Hey if this is having only one column then try this

Declare @return_Value int
Declare @t Table (col int)
insert into @t exec SampleProc --This is your sp
Select @return_Value=col from @t
select @return_Value

Raghu' S

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2011-04-19 : 03:34:25

Hi

Is there any option to supress the return value when executing the procedure using "Execute stored procedure" option in the object explorer of sqlserver

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-19 : 03:42:43

quote:
Originally posted by sqllover

Hi

Is there any option to supress the return value when executing the procedure using "Execute stored procedure" option in the object explorer of sqlserver

Why is this even a concern or issue ? This is just how SSMS behave

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2011-04-19 : 04:42:03

Thanks Kthan. But i am just wondering is there any option to supress.If you have any idea please share with me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources