SQL Server：通过参数从 Umbraco 获取 top xx 博客记录

发布于 2024-09-03 04:41:32 字数 559 浏览 7 评论 0原文

下面的 SQL 得到了我需要的：

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

我需要将数字作为参数传递，所以我有以下内容：

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

但是，它不返回任何记录，知道吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

青衫负雪 2024-09-10 04:41:32

尝试修改您的 SQL 语句，以便通过在将参数作为语句的一部分发送时添加参数来构建语句，例如

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

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] '.....

回复收藏 0 原文

等待我真够勒 2024-09-10 04:41:32

问题在于您尝试使用 LIKE 子句中的参数的方式。

即 @creatorID 和 @nodeType 的值实际上并未在 LIKE 条件中使用 - 您实际上正在搜索 xml，其中（例如）它实际上像 '%creatorID="@creatorID"'

您需要确保您的查询的结果不是：

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

而是：

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

所以类似：

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

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

回复收藏 0 原文

~没有更多了~