hi mudassar
ive been following your tutorials and i like it alot,
got a problem while attemting to try your sample,
here is the sql syntax that i emulated from one of your tutorials
USE [arrestedpersonsdb]
GO
/****** Object: StoredProcedure [dbo].[stnencodedtodisplay] Script Date: 08/10/2013 15:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[stnencodedtodisplay]
(
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
,@id int
,@fname varchar
,@lname varchar
)
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 ROW_NUMBER() OVER
(
ORDER BY [fname] ASC
)AS RowNumber
,[pid]
,[fname]
,[mname]
,[lname]
,[qualifier]
,[alias]
INTO #Results
FROM [todisplay]
where (stnid = @id) and ([type] = 'STN') and (fname = @fname or @fname = '') and (lname = @lname or @lname = '')
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
if i use the first two parameter namely stnid and type it returns as expexted but when i try to add another parameter sample below:
USE [arrestedpersonsdb]
GO
DECLARE @return_value int,
@RecordCount int
EXEC @return_value = [dbo].[stnencodedtodisplay]
@PageIndex = 1,
@PageSize = 10,
@RecordCount = @RecordCount OUTPUT,
@id = 1599,
@fname = 'ALDRIN',
@lname = ''
SELECT @RecordCount as N'@RecordCount'
SELECT 'Return Value' = @return_value
GO
it doesn't return anything is it because the data being returned is a single row or this type of query doesn't allow multiple parameters
thank you and hoping you can give me a solution about my problem
