Parameter output - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Parameter output

Author

Topic

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2008-04-28 : 02:57:33

Can any body help me
CREATE PROCEDURE getRowCount
@where NVARCHAR(500)
,@totalRows INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL = ' SELECT ' + cast(@totalRows as NVARCHAR) + ' = COUNT(*)
FROM Employee E'

IF LEN(@where ) > 0
BEGIN
SET @SQL= @SQL + @Where
END

EXEC (@SQL)
END

It doesn't return a totalRows value.
Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 03:34:29

quote:
Originally posted by nguyenvantruongthang

Can any body help me
CREATE PROCEDURE getRowCount
@where NVARCHAR(500)
,@totalRows INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL = ' SELECT @totalRows = COUNT(*)
FROM Employee E'

IF LEN(@where ) > 0
BEGIN
SET @SQL= @SQL + @Where
END

EXEC (@SQL)
END

It doesn't return a totalRows value.
Thanks in advance.

Why are you casting it to nvarchar. Return the value as int itself. Put it inside string itself.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 04:10:25

b/w i assume this is for learning purposes only. If not, do you really think you need dynamic sql here? Also where do you pass the value of @where variable from?

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2008-04-28 : 04:22:26

Thanks for your help.
I have used dynamic sql here. Because I have queried from database which join many tables and complicated in where condition. I am trying to use two query in the same one store procedure instead I create another store to get only totalRows.
I've put totalRows in string itself. But It doesn't understand the parameter @totalRows of my store.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 04:46:14

quote:
Originally posted by nguyenvantruongthang

Thanks for your help.
I have used dynamic sql here. Because I have queried from database which join many tables and complicated in where condition. I am trying to use two query in the same one store procedure instead I create another store to get only totalRows.
I've put totalRows in string itself. But It doesn't understand the parameter @totalRows of my store.

or try with sp_executesql

CREATE PROCEDURE getRowCount
@where NVARCHAR(500)
,@totalRows INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(2000),@ParmDefinition nvarchar(500);
SET @SQL = N'SELECT @totalRows= COUNT(*)
FROM Employee E'
SET @ParmDefinition=N'@where NVARCHAR(500),@totalRows INT OUTPUT'
IF LEN(@where ) > 0
BEGIN
SET @SQL= @SQL + @Where
END

EXEC sp_executesql @SQL,@ParmDefinition,@where=@where,@totalRows=@totalRows OUT
END

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2008-04-28 : 05:31:16

I've tried . But it returned a null value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 05:46:21

Working for me anyways. How are you invoking sp?

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2008-04-28 : 06:14:29

I make mistake when I am invoking sp.

declare @totalRows int
exec getRowCountTEST ' WHERE 1 = 1',@totalRows OUT
print @totalRows
Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 06:36:00

Dont think it has a problem.Can you post the full query used?

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2008-04-28 : 21:19:43

This is my store using for custom paging with asp.net and SQL 2005
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[SearchPerson]
@Search nvarchar(2000)
,@OrderBy nvarchar (2000)
,@PageSize int
,@PageIndex int
,@TotalRowsNumber int output
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

--Default order by to first column
IF (@OrderBy is null or LEN(@OrderBy) < 1)
BEGIN
SET @OrderBy = 'person.[person_id]'
END

-- SQL Server 2005 Paging
declare @SQL as nvarchar(4000)
declare @SQLCOUNT as nvarchar(4000)
declare @Param as nvarchar(500)

SET @SQL = 'WITH PageIndex AS ('
SET @SQL = @SQL + ' SELECT '
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
END

SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex '
SET @SQL = @SQL + ', person.person_id'
SET @SQL = @SQL + ', person.cat_userType_id'
SET @SQL = @SQL + ', person.fullName'
SET @SQL = @SQL + ', cat_gender.cat_gender_nm'
SET @SQL = @SQL + ', dateOfBirth '
SET @SQL = @SQL + ', experience.positionTitle'
SET @SQL = @SQL + ', experience.salaryAmount'
SET @SQL = @SQL + ', dbo.cat_currency.cat_currency_nm AS salaryCurrency'
SET @SQL = @SQL + ', experience.companyName'
SET @SQL = @SQL + ', experience.company_id'
SET @SQL = @SQL + ', cat_jobSeekingStatus.cat_jobSeekingStatus_nm'
SET @SQL = @SQL + ', (select count(*) as totalHistory from history where person.person_id = history.person_id) as totalHistory'
SET @SQL = @SQL + ', person.isDeleted'

SET @SQL = @SQL + ' FROM person
LEFT JOIN dbo.experience
ON (person.person_id = experience.person_id AND dbo.experience.isCurrent = 1 )
LEFT JOIN dbo.cat_currency
ON dbo.experience.cat_currency_id = dbo.cat_currency.cat_currency_id
LEFT OUTER JOIN dbo.jobSeekingStatus
ON (dbo.person.person_id = dbo.jobSeekingStatus.person_id AND dbo.jobSeekingStatus.isCurrent = 1 )
LEFT OUTER JOIN dbo.cat_jobSeekingStatus
ON dbo.cat_jobSeekingStatus.cat_jobSeekingStatus_id = dbo.jobSeekingStatus.cat_jobSeekingStatus_id
LEFT OUTER JOIN dbo.cat_gender
ON dbo.cat_gender.cat_gender_id = person.cat_gender_id
LEFT OUTER JOIN dbo.cat_userType
ON cat_userType.cat_userType_id = person.cat_userType_id
'
SET @SQL = @SQL + ' WHERE person.person_id in (
Select distinct p_va.[person_id]
FROM dbo.[hr2b_person_view_general] p_va '
IF LEN(@Search) > 0
BEGIN
SET @SQL = @SQL + @Search + ' ) '
END
SET @SQL = @SQL + ' ) SELECT distinct '
SET @SQL = @SQL + ' person_id'
SET @SQL = @SQL + ', cat_userType_id'
SET @SQL = @SQL + ', fullName'
SET @SQL = @SQL + ', cat_gender_nm'
SET @SQL = @SQL + ', (year(getdate()) - year([dateOfBirth])) as [dateOfBirth] '
SET @SQL = @SQL + ', positionTitle'
SET @SQL = @SQL + ', salaryAmount'
SET @SQL = @SQL + ', salaryCurrency'
SET @SQL = @SQL + ', companyName'
SET @SQL = @SQL + ', company_id'
SET @SQL = @SQL + ', cat_jobSeekingStatus_nm'
SET @SQL = @SQL + ', totalHistory'
SET @SQL = @SQL + ', isDeleted'
SET @SQL = @SQL + ' FROM PageIndex '
SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)

IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
END

--Get Row Count
SET @SQLCOUNT = 'SELECT @TotalRowsNumber = count(*)
FROM dbo.[hr2b_person_view_general] p_va '
SET @Param = N'@Search nvarchar(2000),@TotalRowsNumber INT OUTPUT'
IF LEN(@Search) > 0
BEGIN
SET @SQLCOUNT = @SQLCOUNT + @Search
END
--print @SQLCOUNT
exec sp_executesql @SQL
exec sp_executesql @SQLCOUNT, @Param,@Search=@Search,@TotalRowsNumber = @TotalRowsNumber OUT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources