SQL Server: get top xx blog record from Umbraco by parameter

Question

Following SQL get what I need:

 SELECT TOP (50) [nodeId] 
 FROM [dbo].[cmsContentXml] 
 WHERE [xml] like '%creatorID="29"%'    
   AND [xml] like '%nodeType="1086"%' 
 ORDER BY [nodeId] DESC

I need to pass in the numbers as parameters, so I have follows:

exec sp_executesql N'SELECT TOP (@max) [nodeId] FROM [dbo].[cmsContentXml] WHERE [xml] like ''%creatorID="@creatorID"%''    AND [xml] like ''%nodeType="@nodeType"%'' ORDER BY [nodeId] DESC',N'@max int,@creatorID int,@nodeType int',@max=50,@creatorID=29,@nodeType=1086

which however, returns no record, any idea?

score 1 · Accepted Answer · answered May 26 '10 at 10:23

1

Try amending your SQL statement so that you are building the statement by adding the parameters as you are sending them as part of the statement e.g.

'SELECT TOP ' + @max + ' [nodeId] '.....

answered May 26 '10 at 10:23

Neil Knight

47,437
25
129
188

this works, however what is the difference, parameter and string concat? – ccppjava May 26 '10 at 10:25
You are adding a parameter as part of a string concatenation. A parameter is used for Stored Procedures, and in your example, you are using String concatenation. – Neil Knight May 26 '10 at 10:36
2

WARNING: using the string building approach is vulnerable to SQL Injection attacks. See: http://msdn.microsoft.com/en-us/library/ms161953.aspx – Daniel Renshaw May 26 '10 at 10:36
I cannot find other ways around, but I know it is only used in a internal function, which all three paramters are System.Int32, so kind of safe. – ccppjava May 26 '10 at 10:39
You could create a Stored Procedure and pass in the Parameters. – Neil Knight May 26 '10 at 10:50

score 0 · Answer 2 · answered May 26 '10 at 10:39

The problem is because of the way you are trying to use the parameters in the LIKE clauses.

i.e. the values of @creatorID and @nodeType are not actually being used in the LIKE conditions - you're actually searching for xml where (e.g.) it's LITERALLY like '%creatorID="@creatorID"'

You'd need to make sure your query does not come out as:

SELECT TOP (@max) [nodeId] 
FROM [dbo].[cmsContentXml] 
WHERE [xml] like '%creatorID="@creatorID"%'
    AND [xml] like '%nodeType="@nodeType"%' 
ORDER BY [nodeId] DESC

But instead:

SELECT TOP (@max) [nodeId] 
FROM [dbo].[cmsContentXml] 
WHERE [xml] like '%creatorID="' + CAST(@creatorID AS VARCHAR(50)) + '"%'
    AND [xml] like '%nodeType="' + CAST(@nodeType AS VARCHAR(50)) + '"%' 
ORDER BY [nodeId] DESC

So something like:

DECLARE @SQL NVARCHAR(1000)
SET @SQL = '
    SELECT TOP (@max) [nodeId] 
    FROM [dbo].[cmsContentXml] 
    WHERE [xml] like ''%creatorID="'' + CAST(@creatorID AS VARCHAR(50)) + ''"%''    
       AND [xml] like ''%nodeType="'' + CAST(@nodeType AS VARCHAR(50)) + ''"%'' 
    ORDER BY [nodeId] DESC'

exec sp_executesql @SQL,
    N'@max int,@creatorID int,@nodeType int',
    @max=50,@creatorID=29,@nodeType=1086

SQL Server: get top xx blog record from Umbraco by parameter

2 Answers2