January 26, 2009 at 5:29 am
Hi all.
I have a stored proc which currently return the top 4 rows from a view. What im looking to find out is if it possible to pass the number of rows to be returned as a parameter to the SP?
Currently i tried:
ALTER Procedure [dbo].[uspSearchProduct]
(
@ParentProductID int = NULL
,@ReturnNumber int = NULL
.
.
.
.
.
.
.
.
SELECT TOP @ReturnNumber feProduct.ProductID, etc etc etc
however it complains about this.
Any ideas guys and girls?
Many thanks.
January 26, 2009 at 5:40 am
You have to put your parameter in side parentheses (e.g. SELECT TOP (@ReturnNumber ) feProduct.ProductID, etc etc etc)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 26, 2009 at 5:43 am
Brilliant stuff.
Thanks for the swift reply. Much appreciated.
January 26, 2009 at 7:12 am
Adi Cohn (1/26/2009)
You have to put your parameter in side parentheses (e.g. SELECT TOP (@ReturnNumber ) feProduct.ProductID, etc etc etc)Adi
hey my friend.
OK i tried that and it generates the proc without error, however when i run it i get the folowing error:
Error : TOP clause contains an invalid value.
Cheers
January 26, 2009 at 3:12 pm
Check if you passed NULL to the procedure.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 26, 2009 at 6:06 pm
... or a negative number or something that won't convert to an INT.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 26, 2009 at 7:34 pm
And that you're using 2005 or above. (Different error message if you're on 2K, but figured it was worth mentioning for anyone else who might read this and try to replicate it.) This won't work on SQL 2K. You need to use SET ROWCOUNT instead.
January 27, 2009 at 5:04 am
Hi guys. Still cant get to the bottom of this. This is how im calling the SP:
exec [uspSearchProduct] @ListID=23,@OrderBy=N'Price',@OrderByDESC=1,@Page=0,@Pages=@p5 output,@RowsPerPage=10,@SearchString=N''
Definitely an integer value being passed.
Thanks again.
January 27, 2009 at 6:20 am
Can't tell... not enough information. You need to prove to yourself that you're passing something besides a null or a negative number for pages, pages, and rows per page. It would be handy if you posted the whole stored procedure.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2009 at 6:40 am
Jeff Moden (1/27/2009)
Can't tell... not enough information. You need to prove to yourself that you're passing something besides a null or a negative number for pages, pages, and rows per page. It would be handy if you posted the whole stored procedure.
Hi there, thanks for the reply. The SP in full is:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE Procedure [dbo].[uspSearchProduct]
-- ****************************************************************************************
-- ** Parameters, All Paramaters are Optional
-- ** The Product.ID will be returned
-- ****************************************************************************************
(
@ParentProductID int = NULL
,@SKU varchar(50) = NULL
,@Name Varchar(150) = NULL
,@Option int = NULL
,@Errors bit = NULL
,@ClassificationID_CSV1 Varchar(300) = NULL
,@ClassificationID_CSV2 Varchar(300) = NULL
,@ClassificationID_CSV3 Varchar(300) = NULL
,@ListID int = NULL
,@ListType int = NULL
,@ReturnNumber int = NULL
--,@Specification int = NULL
--,@SpecificationValue varchar(20) = NULL
,@SearchString Varchar(100) = NULL
,@FullTextSearch bit = 1
,@BundleProduct bit = NULL
,@StockAvailable bit = NULL
,@Currency int = NULL
,@FromPrice money = NULL
,@ToPrice money = NULL
,@OrderBy varchar(20) = 'Name'
,@OrderByDESC bit = 0
,@RowsPerPage int = 20 OUTPUT
,@Page int = 1 OUTPUT
,@Pages int = NULL OUTPUT
,@Rows int = NULL OUTPUT
-- Standard Return Parameters, and LoginID
,@ParentAuditTrailID int = NULL OUTPUT
,@ErrorLogID int = NULL OUTPUT
,@ModuleName Varchar(256) = NULL OUTPUT
,@ModuleVersion Varchar(10) = NULL OUTPUT
,@ErrorLine int = NULL OUTPUT
,@Error int = NULL OUTPUT
,@ErrorMessage nvarchar(4000) = NULL OUTPUT
,@ErrorDisplay Varchar(8000) = NULL OUTPUT
,@ErrorTypeName varchar(60) = NULL OUTPUT
-- Standard @ID Parameter (ID for applicable table)
,@ID int = NULL OUTPUT
-- Standard Row Count Parameters
,@RowsSelected int = 0 OUTPUT
,@RowsUpdated int = 0 OUTPUT
,@RowsDeleted int = 0 OUTPUT
,@RowsInserted int = 0 OUTPUT
-- Standard Performance Parameters
,@Milliseconds int = 0 OUTPUT
-- Standard Login Parameter
,@LoginID int = NULL
)
AS
-- ****************************************************************************************
-- ** Initialise
-- ****************************************************************************************
DECLARE @FirstRow int
DECLARE @LastRow int
DECLARE @ParsedString varchar(2000)
DECLARE @StartTime datetime
DECLARE @CR Char(1)
SET @CR = Char(13)
SET @StartTime = GetDate()
Set @ModuleName=OBJECT_NAME(@@PROCID);
Set @ModuleVersion='2007.03.23'
Set @ID = NULL
Set @Error = 0
If @LoginID IS NULL SET @LoginID = dbo.const_DefaultLogin()Set @Error = 0
If @SearchString IS NOT NULL
BEGIN
EXEC uspFixFullTextSearchString @TheKeywords=@SearchString,
@proximity='near', @ParsedString=@ParsedString OUTPUT
END
--Print (@ParsedString)
If @Currency IS NULL
SET @Currency = dbo.const_Currency_PoundsSterling()
If @ListID IS NULL AND @ListType IS NOT NULL
SELECT Top 1 @ListID = ID
FROM List WHERE ListType = @ListType
BEGIN TRY
-- ****************************************************************************************
-- ** Create Temp Table to hold search result ProductID
-- ****************************************************************************************
CREATE TABLE #SearchResults (ID int IDENTITY(1,1), ProductID int, OrderBy Varchar(20))
CREATE UNIQUE CLUSTERED INDEX [ix_ID] ON #SearchResults (ID ASC)
--CREATE INDEX [ix_ProductID] ON #SearchResults (ProductID ASC)
--SET @Milliseconds = DateDiff(ms, @StartTime, GetDate())
--PRINT ('Created Temp Table : ' + Convert(varchar(20), @Milliseconds) + 'ms')
-- ****************************************************************************************
-- ** Insert the selected Products into the Search results Temp Table
-- ** Build OrderBy colum based on Case Statement
-- ****************************************************************************************
INSERT INTO #SearchResults (ProductID, OrderBy)
SELECT TOP (@ReturnNumber) feProduct.ProductID,
--SELECT TOP 4 feProduct.ProductID,
CASE WHEN @OrderBy LIKE '%Name%' THEN Left(Name, 20)
WHEN @OrderBy LIKE '%Price%' THEN Right('00000000000000000000' + Convert(Varchar(20), ISNULL(feProduct.FromPrice, 0)), 20)
WHEN @OrderBy LIKE '%ID%' THEN Right('00000000000000000000' + Convert(Varchar(20), feProduct.ProductID), 20)
WHEN @OrderBy LIKE '%SKU%' THEN Left(SKU, 20)
WHEN @OrderBy LIKE '%Random%' THEN Left(NewID(), 20)
WHEN @OrderBy LIKE '%Sequence%' THEN Right('00000000000000000000' + Convert(Varchar(20), ISNULL(Sequence,0)), 20)
END as OrderBy
FROM feProduct WITH (READPAST)
left join feProductHREF_BalconyShirts href on feproduct.productid=href.productid AND ((@ClassificationID_CSV1 IS NULL) OR (href.categoryl1 in (SELECT Convert(int, [Value])
FROM dbo.udfSplitCSV (@ClassificationID_CSV1, ','))) AND ((@ClassificationID_CSV2 IS NULL) OR href.categoryl1 in (SELECT Convert(int, [Value])
FROM dbo.udfSplitCSV (@ClassificationID_CSV2, ','))) AND ((@ClassificationID_CSV3 IS NULL) OR href.categoryl1 in (SELECT Convert(int, [Value])
FROM dbo.udfSplitCSV (@ClassificationID_CSV3, ','))))
WHERE (feProduct.LevelType = dbo.const_ProductLevelType_Product() OR feProduct.LevelType = dbo.const_ProductLevelType_GiftVoucher())
AND (@ParentProductID IS NULL OR ParentProductID=@ParentProductID)
AND (@SKU IS NULL OR SKU LIKE '%' + @SKU + '%')
AND (@Name IS NULL OR Name LIKE '%' + @Name + '%')
AND (@Option IS NULL OR Option1=@Option OR Option2=@Option OR Option3=@Option OR Option4=@Option OR Option5=@Option)
AND (@Currency IS NULL OR (feProduct.Currency = @Currency))
AND (@FromPrice IS NULL OR (@FromPrice <= feProduct.FromPrice))
AND (@ToPrice IS NULL OR (@ToPrice >= feProduct.FromPrice))
AND (@BundleProduct IS NULL OR (@BundleProduct = 1 AND BundleItems > 0) OR (@BundleProduct = 0 AND BundleItems = 0))
AND (@StockAvailable IS NULL OR (@StockAvailable = 1 AND StockAvailable > 0) OR (@StockAvailable = 0 AND StockAvailable = 0))
AND ((@ClassificationID_CSV1 IS NULL)
OR (feProduct.ProductID IN
(SELECT vProductClassification.ProductID
FROM vProductClassification WITH (READPAST)
WHERE (@ClassificationID_CSV1 IS NULL OR
ClassificationID IN (SELECT Convert(int, [Value])
FROM dbo.udfSplitCSV (@ClassificationID_CSV1, ','))))))
AND ((@ClassificationID_CSV2 IS NULL)
OR (feProduct.ProductID IN
(SELECT vProductClassification.ProductID
FROM vProductClassification WITH (READPAST)
WHERE (@ClassificationID_CSV2 IS NULL OR
ClassificationID IN (SELECT Convert(int, [Value])
FROM dbo.udfSplitCSV (@ClassificationID_CSV2, ','))))))
AND ((@ClassificationID_CSV3 IS NULL)
OR (feProduct.ProductID IN
(SELECT vProductClassification.ProductID
FROM vProductClassification WITH (READPAST)
WHERE (@ClassificationID_CSV3 IS NULL OR
ClassificationID IN (SELECT Convert(int, [Value])
FROM dbo.udfSplitCSV (@ClassificationID_CSV3, ','))))))
AND ((@ListID IS NULL)
OR (feProduct.ProductID IN
(SELECT ListProduct.ProductID
FROM ListProduct WITH (READPAST)
WHERE @ListID IS NULL OR ListProduct.ListID = @ListID
AND ListProduct.ToDate IS NULL)))
AND ((@SearchString IS NULL OR @FullTextSearch = 0)
OR (feProduct.ProductID IN
(SELECT ProductNote.ProductID
FROM ProductNote WITH (READPAST)
INNER JOIN tblNote ON tblNote.ID = ProductNote.NoteID
AND CONTAINS (NoteText, @ParsedString)
AND tblNote.Deleted = 0
AND (tblNote.ExpiryDate > GetDate() OR tblNote.ExpiryDate IS NULL)))
--OR (feProduct.ProductID IN
--(SELECT ProductClassification.ProductID
--FROM ProductClassification
--INNER JOIN Classification ON Classification.ID = ProductClassification.ClassificationID
--AND Classification.Deleted = 0
--INNER JOIN ClassificationNote ON ClassificationNote.ClassificationID = Classification.ID
--INNER JOIN tblNote ON tblNote.ID = ClassificationNote.NoteID
--AND CONTAINS (NoteText, @ParsedString)
--AND tblNote.Deleted = 0
--AND (tblNote.ExpiryDate > GetDate() OR tblNote.ExpiryDate IS NULL)))
OR (feProduct.ProductID IN
(SELECT vListProduct_Today.ProductID
FROM vListProduct_Today
WHERE vListProduct_Today.ListName LIKE '%' + @SearchString + '%'))
--OR (feProduct.ProductID IN
--(SELECT feProductSpecification.ProductID
--FROM feProductSpecification
--WHERE [Value] LIKE '%' + @SearchString + '%'))
OR ('[' + IsNull(Convert(Varchar(20), feProduct.ProductID), '') + ']['+
IsNull(Convert(Varchar(20), feProduct.ConversionID), '') + ']['+
IsNull(feProduct.SKU, '') + ' ' +
IsNull(feProduct.Name, '') + ' ' +
IsNull(feProduct.BrandName, '') + ' ' +
IsNull(feProduct.ProductHREF, '') + ' ' +
IsNull(feProduct.ProductOption1Name, '') + ' ' +
IsNull(feProduct.ProductOption2Name, '') + ' ' +
IsNull(feProduct.ProductOption3Name, '') + ' ' +
IsNull(feProduct.ProductOption4Name, '') + ' ' +
IsNull(feProduct.ProductOption5Name, '') + ' ' +
IsNull(feProduct.Option1Name, '') + ' ' +
IsNull(feProduct.Option2Name, '') + ' ' +
IsNull(feProduct.Option3Name, '') + ' ' +
IsNull(feProduct.Option4Name, '') + ' ' +
IsNull(feProduct.Option5Name, '') + ' '
LIKE '%' + @SearchString + '%')
)
--ORDER BY 2,1
ORDER BY NewID()
Set @Rows = @@RowCount
Set @RowsSelected = IsNull(@RowsSelected, 0) + @Rows
Set @RowsInserted = IsNull(@RowsInserted, 0) + @Rows
--SET @Milliseconds = DateDiff(ms, @StartTime, GetDate())
--PRINT ('Built Temp Table : ' + Convert(varchar(20), @Milliseconds) + 'ms')
--Select * from #SearchResults
-- ****************************************************************************************
-- ** Get some info from the Temp Search Table, Set Pages, Page etc
-- ****************************************************************************************
If (@RowsPerPage <= 0) OR (@RowsPerPage IS NULL) SET @RowsPerPage = 20
SET @Pages = ((@Rows - 1) / @RowsPerPage) + 1
If @Page < 1 SET @Page = 1
If @Page > @Pages SET @Page = @Pages
If @OrderByDESC = 0
BEGIN
SET @FirstRow = 1 + (@RowsPerPage * (@Page - 1))
SET @LastRow = @FirstRow + @RowsPerPage - 1
END
Else
BEGIN
SET @LastRow = @Rows - (@RowsPerPage * (@Page - 1))
SET @FirstRow = @LastRow - @RowsPerPage + 1
END
-- ****************************************************************************************
-- ** Select from Temp Table, Join with feProductSearch and return page requested
-- ****************************************************************************************
If @OrderByDESC = 0
BEGIN
SELECT ID as RowNumber, feProductSearch.[ProductID],[CategoryID],[Random],[BrandID],[BrandName],[Name],[PageTitle],[Description]
,[CommercialMessage],isnull([ProductHREF],'') as ProductHREF,[SearchImage],[SearchImageWidth]
,[SearchImageHeight],[AltText],[Currency],[CurrencyName],[Price],[WasPrice],[WasSaving]
,[WasPctSaving],[RRP],[RRPSaving],[RRPPctSaving],[VAT],[CostPrice],[TradePrice]
,[TradeVat],[MinItemPrice],[FromFlag],[FromPrice]
FROM #SearchResults
INNER JOIN feProductSearch ON feProductSearch.ProductID = #SearchResults.ProductID
WHERE #SearchResults.ID BETWEEN @FirstRow AND @LastRow
ORDER BY #SearchResults.ID ASC
Set @RowsSelected = IsNull(@RowsSelected, 0) + @@RowCount
END
ELSE
BEGIN
SELECT ID as RowNumber, feProductSearch.[ProductID],[CategoryID],[Random],[BrandID],[BrandName],[Name],[PageTitle],[Description]
,[CommercialMessage],isnull([ProductHREF],'') as ProductHREF,[SearchImage],[SearchImageWidth]
,[SearchImageHeight],[AltText],[Currency],[CurrencyName],[Price],[WasPrice],[WasSaving]
,[WasPctSaving],[RRP],[RRPSaving],[RRPPctSaving],[VAT],[CostPrice],[TradePrice]
,[TradeVat],[MinItemPrice],[FromFlag],[FromPrice]
FROM #SearchResults
INNER JOIN feProductSearch ON feProductSearch.ProductID = #SearchResults.ProductID
WHERE #SearchResults.ID BETWEEN @FirstRow AND @LastRow
ORDER BY #SearchResults.ID DESC
Set @RowsSelected = IsNull(@RowsSelected, 0) + @@RowCount
END
--SET @Milliseconds = DateDiff(ms, @StartTime, GetDate())
--PRINT ('Almost Finished : ' + Convert(varchar(20), @Milliseconds) + 'ms')
DROP TABLE #SearchResults
END TRY
-- ********************************************************************************************************************************************************************
-- ** Catch any Errors and Log them
-- ********************************************************************************************************************************************************************
BEGIN CATCH
SET @Error = @@error
SET @ErrorLine = ERROR_LINE()
SET @ErrorMessage = Replace(ERROR_MESSAGE(), '''', '')
If @@TranCount > 0 ROLLBACK TRANSACTION
EXEC @ErrorLogID=uspLogError
@LoginID=@LoginID,
@ModuleSystem='Stored Procedure',
@ModuleName=@ModuleName,
@ModuleVersion=@ModuleVersion,
@ModuleLineNo=@ErrorLine,
@TableID=@ID,
@ErrorNumber=@Error,
@ErrorMessage=@ErrorMessage,
@ErrorTypeName='Select Error',
@ErrorDisplay=@ErrorDisplay OUTPUT,
@RowsSelected=@RowsSelected OUTPUT,@RowsUpdated=@RowsUpdated OUTPUT,
@RowsDeleted=@RowsDeleted OUTPUT,@RowsInserted=@RowsInserted OUTPUT
SET @Milliseconds = DateDiff(ms, @StartTime, GetDate())
RETURN -1
END CATCH
-- ********************************************************************************************************************************************************************
-- ** Standard Return, Calculate MilliSeconds and return with @Error (Should be 0)
-- ********************************************************************************************************************************************************************
SET @Milliseconds = DateDiff(ms, @StartTime, GetDate())
Return @Error
January 27, 2009 at 7:05 am
NULL is not a valid value for TOP, so you either need to change your parameter initialization, i.e.
,@ReturnNumber int = 1
Or change your SELECT statement, i.e.
SELECT TOP (ISNULL(@ReturnNumber, 1)) feProduct.ProductID,
Of course, you may want to use a different value other than 1 depending on your application.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
January 27, 2009 at 9:31 am
Brilliant. That's fixed it.
Thanks very much 🙂
January 27, 2009 at 9:39 am
No problem, I'm happy I could help! 🙂
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply