July 10, 2022 at 3:01 am
Hi
Error - Conversion failed when converting the varchar value '+ cast(@BookId as varchar(Max))+' to data type int.
ALTER PROC [dbo].[USP_Session] --'0','20,21,22'
(
@BookID VARCHAR(500),
@StudentID VARCHAR(500)
)
AS
BEGIN
with ROWCTE as
(
SELECT * from View_Session
where BookID IN('+ cast(@BookId as varchar(Max))+')
)
SELECT * FROM ROWCTE
Thanks
July 10, 2022 at 9:04 am
You can't embed the varchar @BookId directly into your query.
You can make your whole query dynamic and embed @BookId into it. NOTE: This is very dangerous.
You can shred your @BookId into a @TableVariable or #Temptable then join to it.
Or, you can CROSS APPLY directly to an iTVF like DelimitedSplit8K or DelimitedSplit8K_LEAD
SELECT vs.*
FROM View_Session AS vs
CROSS APPLY dbo.DelimitedSplit8K( @BookId, ',' ) AS dsk
WHERE vs.BookID = dsk.Item;
There is no need for the CTE in your code.
July 12, 2022 at 5:44 pm
Regarding the input parameters @BookID and @StudentID, instead of supplying IDs in the form of a delimited string, consider using table-valued parameters instead. This would make referencing the parameters much more straightforward in terms of coding SQL, and also I expect this would prove more reliable.
https://www.sqlshack.com/table-valued-parameters-in-sql-server/
Read the section titled "Using Table-Valued Parameters in Stored Procedures".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 12, 2022 at 6:33 pm
Since SQL Server 2016 you can use the STRING_SPLIT() function, which means that you can do something like this:
SELECT * FROM View_Session WHERE BookID IN(SELECT CAST(value AS INT) FROM STRING_SPLIT(@BookID, ','))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply