can u help in creating sp which return the results

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

can u help in creating sp which return the results

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-04-11 : 09:22:21

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 09:29:39

[code]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
[/code]

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-04-11 : 09:44:25

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 09:53:29

what is the problem with @@ROWCOUNT?

--
Chandu

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2013-04-11 : 09:55:19

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 00:51:07

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