SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 can u help in creating sp which return the results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

212 Posts

Posted - 04/11/2013 :  09:22:21  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
need to create sp which return the resultset in table variable and also no of rows count

Create Procedure [dbo].[usp_get_data_count]
@deptId int,
@count_insert INT OUTPUT

declare @tablevariable (empid int ,empname varchar(20))

select * from dim_employee where deptId = @deptId

return @tablevariable

return @count_insert

end

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 04/11/2013 :  09:29:39  Show Profile  Reply with Quote
Create Procedure [dbo].[usp_get_data_count]
@deptId int,
@count_insert INT OUTPUT
AS
BEGIN
	select * from dim_employee where deptId = @deptId 
	SET @count_insert = @@ROWCOUNT

end
GO
--Test Procedure
DECLARE @cnt INT
EXEC [dbo].[usp_get_data_count] 20, @cnt OUT
SELECT  @cnt
Go to Top of Page

shanmugaraj
Posting Yak Master

212 Posts

Posted - 04/11/2013 :  09:44:25  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
I have @count_insert, @count_error in my Procedure
could you suggest some other command instead of
SET @count_insert = @@ROWCOUNT

like..

Create Procedure [dbo].[usp_get_data_count]
@deptId int,
@count_insert INT OUTPUT,
@count_delete INT OUTPUT,
@count_update INT OUTPUT
AS
BEGIN
select * from dim_employee where deptId = @deptId
SET @count_insert = @@ROWCOUNT

end
GO

Edited by - shanmugaraj on 04/11/2013 09:45:56
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 04/11/2013 :  09:53:29  Show Profile  Reply with Quote
what is the problem with @@ROWCOUNT?

--
Chandu
Go to Top of Page

shanmugaraj
Posting Yak Master

212 Posts

Posted - 04/11/2013 :  09:55:19  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
I want to return to .net team mentioning the sp resultsset of insert , update, delete rows and the rows with error records in table variable

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 04/12/2013 :  00:51:07  Show Profile  Reply with Quote
Do you have INSERT , UPDATE, DELETE statements in the procedure?
--May be this?
Create Procedure [dbo].[usp_get_data_count]
@deptId int, 
.   -- Other params for insert/update/delete values 
.
.,
@count_insert INT OUTPUT,
@count_delete INT OUTPUT,
@count_update INT OUTPUT
@count_error INT OUTPUT
AS
BEGIN
 BEGIN TRY

   Your code for INSERT 
    SET @count_insert = @@ROWCOUNT


   Your code for UPDATE 
    SET @count_update = @@ROWCOUNT


   Your code for DELETE
    SET @count_delete = @@ROWCOUNT

 END TRY
 BEGIN CATCH
    SET @Count_Error = @@ERROR
 END CATCH
END
Go to Top of Page

shanmugaraj
Posting Yak Master

212 Posts

Posted - 04/12/2013 :  01:22:16  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
Hi Bandi, Thanks.


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 04/12/2013 :  01:52:43  Show Profile  Reply with Quote
quote:
Originally posted by shanmugaraj

Hi Bandi, Thanks.

Did you get the solution?

Anyway, welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000