使用存储过程查找结果集无序的原因
我在 sql 存储过程中遇到问题。我有一个存储过程，其中包含动态 SQL 查询、创建临时表、将数据插入到该临时表中。在我的本地，这个存储过程的结果显示良好。但在生产环境中，结果的顺序发生了变化。我只有该生产的选择权限，因此我无法在生产数据库中运行该 sp。任何人都可以提出任何解决方案。是否有可能运行该 SP 并在生产 SP 中查看结果。谢谢
i m facing a problem in sql stored procedure. i have a stored procedure which contains a dynamic sql query, creation of temp tables, inserting datas into this temp tables. in my local the result of this stored procedure displays fine. but in Production environment the ordering of result is changed. i have only the select permission for that production, so i am not able to run that sp in Production database. can any one suggests any solution. is there any possibility to run that SP and view the result in production sp. thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
恐怕您的问题不是很具体，但是如果您依赖于结果的顺序并且您没有在返回结果的查询上使用
ORDER BY（在您的情况下，这可能是最后一句话）那么你应该这样做。除非您使用ORDER BY，否则无法保证结果的顺序。如果您已经是，也许您可以发布查询的相关部分。
I'm afraid your question isn't very specific, but if you are relying on the order of results and you're not using an
ORDER BYon the query that returns the results (in your case, it's likely the last statement) then you should. The order of results is not guaranteed unless you useORDER BY.If you already are, perhaps you could post the relevant parts of your query.
我会检查两个数据库的排序规则是否相同。假设使用 MS Sql Server，右键单击 Mgmt Studio 中的数据库节点并查看“属性”。排序规则类似于
SQL_Latin1_General_CP1_CI_AS。如果它们不匹配，“自然”排序顺序可能会有所不同，除非您指定显式的
ORDER BY子句。I'd check the Collation of the two databases is the same. Assuming MS Sql Server, right-click the database node in Mgmt Studio and view Properties. The collation will be something like
SQL_Latin1_General_CP1_CI_AS.If they don't match, it's possible that the 'natural' sort order will be different unless you specify an explicit
ORDER BYclause.我可以立即想到两种可能性，无需更多信息：
1）生产环境和本地环境的配置不同，特别是在 tempdb 数据库的排序规则方面（这将是临时表使用的默认排序规则，除非您可以在其 CREATE 语句中另外指定）。因此，字符字段的任何顺序都会有不同的行为。
2）您根本没有进行任何特定的排序，在这种情况下，“默认”顺序由许多因素决定，并且服务器之间不太可能相同。
I can think of two possibilities off the top of my head without more info :
1) The production and your local environment are configured differently, especially with regard to the collation of the tempdb database (which will be the default collation used by temporary tables unless you specify otherwise in their CREATE statement). Therefore any ordering of character fields will behave differently.
2) You're not doing any specific ordering at all, in which case the 'default' order is dictated by many things and is unlikely to be the same between servers.