Error in Query?

Question

USE [SiteDemo] GO /****** Object: StoredProcedure [dbo].[sp_Master_SubCategoryInfo_SelectALLByCategoryName] Script Date: 09/15/2014 09:24:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= alter proc [dbo].[sp_Master_ProductInfoFilter] @PageIndex INT = 1 ,@PageSize INT = 10 ,@SubCategoryName nvarchar(max)=null ,@RecordCount INT OUTPUT AS BEGIN declare @Query nvarchar(MAX) SET NOCOUNT ON; set @Query= 'SELECT'+ ROW_NUMBER()OVER +' ( ORDER BY [ID]desc ) AS'+ RowNumber +' ,[ID] ,[StoreName] ,[ProductName] ,[ProductBrand] ,[OldPrice] ,[NewPrice] ,[Discount] ,[ProductDiscription] ,[ProductRatings] ,[ProductFeatures] ,[ProductSiteLink] ,[ProductCategory] ,[ProductSubCategory] ,[Link] ,[ProductBannerImage] ,[ProductHoverImage] ,[ProductVideoLink] ,[ProductOverView] ,[ProductAdditionalInfo] ,[ProductIsActive] ,[ProductIsFeatured] ,[ProductIsNewArrival] ,[ProductIsBestSeller] ,[ProductInStock] ,[IsLatest] ,[AvailableSizes] ,[AvailableColors] ,[CouponCode] ,[CreatedBy] ,[CreatedOn] ,[ModifiedBy] ,[ModifiedOn] INTO #Results FROM [Master_ProductInfo] '+ @SubCategoryName + ' '+ '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

Jignesh Trivedi · Answer

hi, here you are creating dynamic query row number function with single quote like... declare @Query nvarchar(MAX) SET NOCOUNT ON; set @Query= 'SELECT ROW_NUMBER() OVER ( ORDER BY [ID]desc ) AS RowNumber ,[ID] ..... hope this will help you.

Wim Sturkenboom · Answer

The below line gives a red squirly under the last + when copied to SSMS set @Query= 'SELECT'+ ROW_NUMBER()OVER + ' I assume you're trying to create a dynamic query. Further I assume that you want ROW_NUMBER() OVER to be evaluated when the stored procedure is executed, not when you create the stored procedure. The same applies to the end of your query with BETWEEN. From that perspective, the beginning of your query should look like set @Query= 'SELECT ROW_NUMBER() OVER ( ORDER BY [ID]desc ) AS RowNumber ,[ID] Further the way you use it is not correct. You need an additional select. I'll demo further below. Also, I don't trust the end of your query, but that is just a feel; this looks ugly for some reason; probably the highlighted part. ,[ModifiedOn] INTO #Results FROM [Master_ProductInfo] '+ @SubCategoryName + ' '+ 'SELECT @RecordCount = COUNT(*) FROM #Results I suggest that you start with a normal (non-dynamic) stored procedure and try to get that working first. Next you can make it dynamic by placing single ticks at the beginning and the end of the string and assigning it to @Query. A little demo; Non-dynamic stored procedure: CREATE PROCEDURE [dbo].[usp_rownumber] -- Add the parameters for the stored procedure here @pFirst int, @pLast int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; select * from ( select ROW_NUMBER() over ( order by Name ) as RowNumber ,* from tblScores ) as tbldata where tbldata.RowNumber between @pFirst AND @pLast Note the additional select. If it works (it did in my case ;)), it can be made dynamic. Building dynamic stored procedure: We'll start by declaring a avariable to hold the query. I've highlighted in yellow the changes compared to the original ALTER PROCEDURE [dbo].[usp_rownumber] -- Add the parameters for the stored procedure here @pFirst int, @pLast int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @Query nvarchar(MAX) set @Query = ' select * from (select ROW_NUMBER() over ( order by Name ) as RowNumber ,* from tblScores ) as tbldata where tbldata.RowNumber between @pFirst AND @pLast ' Next we need to prepare a 'list' of the parameters that we want to pass. I've placed it after the above. I've also added some debug print statements so when we execute the store procedure we can see what happens (in SSMS) declare @ParamDefinition nvarchar(500) set @ParamDefinition = '@pFirst int, @pLast int' print @Query print @ParamDefinition print @pFirst print @pLast Lastly we'll tell the stored procedure to execute our SQL statement execute sp_executesql @Query, @ParamDefinition, @pFirst, @pLast END The complete stored procedure looks like ALTER PROCEDURE [dbo].[usp_rownumber] -- Add the parameters for the stored procedure here @pFirst int, @pLast int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @Query nvarchar(MAX) set @Query = ' select * from (select ROW_NUMBER() over ( order by Name ) as RowNumber ,* from tblScores ) as tbldata where tbldata.RowNumber between @pFirst AND @pLast ' declare @ParamDefinition nvarchar(500) set @ParamDefinition = '@pFirst int, @pLast int' print @Query print @ParamDefinition print @pFirst print @pLast execute sp_executesql @Query, @ParamDefinition, @pFirst, @pLast END Save the stored procedure and execute it. You can look in the messages tab of the output window to see what is created. I hope this gets you on the right track.

Error in Query?

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)