question

rainhardman avatar image
rainhardman 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.
help
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks JohnM, that did the trick!
0 Likes 0 ·

1 Answer

· Write an Answer
JohnM avatar image
JohnM answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.