SQL Server Performance Forum – Threads Archive

Concatenation in a sproc

Hello, I am having a strange problem when trying to execute this command: SET @chari = cast(@i as varchar(4))
SET @PriceBeforeCommission = (SELECT CONVERT(decimal(10,2), HistoryData.value(‘(/row/@PriceBeforeCommission)[‘ + @chari + ‘]’, ‘nvarchar(MAX)’))
FROM ReservationDetails
WHERE [email protected] AND [email protected])
The problem occurs because of the variable @chari that I am trying to concatenate. The error I am getting is the following: Msg 8172, Level 16, State 1, Procedure TEST_REVENUE_PROFIT, Line 78
The argument 1 of the xml data type method "value" must be a string literal.
Can anyone help ?
TedManowar

It means you cannot concatenate ‘inside’ the query. You’ll have to check whether you can use a variable to hold the concatenated string, like this … DECLARE @XMLString VARCHAR(100) SET @chari = cast(@i as varchar(4)) SET @XMLString = ‘(/row/@PriceBeforeCommission)[‘ + @chari + ‘]’ SET @PriceBeforeCommission = (SELECT CONVERT(decimal(10,2), HistoryData.value(@XMLString, ‘nvarchar(MAX)’))
FROM ReservationDetails
WHERE [email protected] AND [email protected]) … but there are points where you must supply a fixed string, like with OPENQUERY etc. In that case, you have to resort to dynamic SQL.

Can you give me an example of Dynamic SQL that might be able to help me on this issue… I tried it your way (as above) and I still get the same error… Which simply means that I MUST supply a string literal… TedManowar

Look at this hard, and consult Books Online for sp_ExecuteSQL … The use of the OUTPUT keyword is not explained there, but it works the same as with any call to a stored procedure. DECLARE @SQL NVARCHAR(4000) SET @SQL =
N’SET @p1 =
(SELECT CONVERT(decimal(10,2), HistoryData.value(/row/@PriceBeforeCommission)[‘
+ cast(@i as varchar(1000))
+ ‘], ”nvarchar(MAX)”)) FROM ReservationDetails
WHERE fk_HotelID=’ + @HotelID
+ ‘ AND fk_ReservationID=’ + @ReservationID + ‘)’ EXEC dbo.sp_ExecuteSQL @SQL, N’@p1 DECIMAL(10,2) OUTPUT’, @PriceBeforeCommission OUTPUT … and you probably have to cast @HotelID and @ReservationID as well.

Thank you Adriaan, You ‘ve helped me yet another time… [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] However the command had a few erros in it but, I managed to resolve them and the code looks like this now: DECLARE @SQL NVARCHAR(4000) SET @SQL = N’SET @p1 = (SELECT CONVERT(decimal(10,2), HistoryData.value(”(/row/@PriceBeforeCommission)[‘+ cast(@i as varchar(1000)) + ‘]”, ”nvarchar(MAX)”)) FROM ReservationDetails WHERE fk_HotelID=’ + cast(@HotelID as varchar(10)) + ‘ AND fk_ReservationID=’ + cast(@ReservationID as varchar(10)) + ‘AND ReservationDetailID=’ + cast(@ReservationDetailID as varchar(10)) + ‘)' EXEC dbo.sp_ExecuteSQL @SQL, N’@p1 DECIMAL(10,2) OUTPUT’, @PriceBeforeCommission OUTPUT Eventhough the SELECT command is executed as Dynamic SQL, the 2 parameters of the xml.value function still need to be string literals, so I added the 2 (‘) wherever needed. Thanks a milion. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

Ah, you’re learning fast! Excellent. I should have looked closer at those single quotes myself, but you appear to have grasped the concepts pretty well.

]]>