asked
Can someone please help with erring SP?
CREATE PROCEDURE [Catalogs].[usr_sp_GetMasterDataPageSorted] ( @sortExpression nvarchar(50), @between1 int, @between2 int ) AS IF LEN(@sortExpression) = 0 SET @sortExpression = 'MusicDataId' DECLARE @sql nvarchar(4000) SET @sql = 'SELECT [MusicDataId], [ArtistLastName], [ArtistFirstName],[Title], [MediumType], [MediumYear], [BurntoDisc?], [Owner], [DateCreated], RowNum FROM (SELECT [MusicDataId], ArtistLastName, ArtistFirstName, Title, MediumType, MediumYear, [BurntoDisc?], Owner, DateCreated, ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM Catalogs.MusicDataMaster) as MasterRecordInfo where RowNum between' + @between1 + 'and' + @between2 -- Execute the SQL query EXEC sp_executesql @sql GO THE ERROR... Msg 245, Level 16, State 1, Procedure usr_sp_GetMasterDataPageSorted, Line 25 Conversion failed when converting the nvarchar value 'SELECT [MusicDataId], [ArtistLastName], [ArtistFirstName], [Title], [MediumType], [MediumYear], [BurntoDisc?], [Owner], [DateCreated], RowNum FROM (SELECT [MusicDataId], ArtistLastName, ArtistFirstName, Title, MediumType, MediumYear, [BurntoDisc?], Owner, DateCreated, ROW_NUMBER() OVER(ORDER BY MusicDataId) as RowNum FROM Catalogs.MusicDataMaster ) as MasterRecordInfo where RowNum between' to data type int.
You need to convert the two Integer parameters to a character data type. Either you can do that when you declare it or you can do it line with the dynamic SQL. CREATE PROCEDURE [Catalogs].[usr_sp_GetMasterDataPageSorted] ( @sortExpression nvarchar(50), @between1 varchar(10), -- one way @between2 varchar(10) --one way ) AS IF LEN(@sortExpression) = 0 SET @sortExpression = 'MusicDataId' DECLARE @sql nvarchar(4000) SET @sql = 'SELECT [MusicDataId], [ArtistLastName], [ArtistFirstName],[Title], [MediumType], [MediumYear], [BurntoDisc?], [Owner], [DateCreated], RowNum FROM (SELECT [MusicDataId], ArtistLastName, ArtistFirstName, Title, MediumType, MediumYear, [BurntoDisc?], Owner, DateCreated, ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM Catalogs.MusicDataMaster) as MasterRecordInfo -- Or you can do it in line here: where RowNum between' + cast(@between1 as varchar(10)) + 'and' + cast(@between2 as varchar(10)) -- Execute the SQL query EXEC sp_executesql @sql GO Hope that helps!
19 People are following this question.
