SQL 为什么我不能将唯一标识符/GUID 传递给存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3029748/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't I pass in a uniqueidentifier/GUID to a stored procedure
提问by chobo2
I have this SP
我有这个SP
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[sp_test] Script Date: 06/12/2010 11:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_test]
@id uniqueidentifier
AS
BEGIN
select * from TestTbl where ProductId= @id
END
I then went to the SP with ms sql 2005 and clicked execute. It comes up with a box where I entered in the GUID. I copied and pasted it straight from my test database.
然后我使用 ms sql 2005 转到 SP 并单击执行。它出现了一个我在 GUID 中输入的框。我直接从我的测试数据库中复制并粘贴了它。
I get this error.
我收到这个错误。
Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'cac671b'.
消息 102，级别 15，状态 1，第 5 行“cac671b”附近的语法不正确。
So why can't I sent in GUIDs? even ones that are copied right from the database and must be valid as they where allowed into the db.
那么为什么我不能发送 GUID？即使是直接从数据库复制的，并且必须在允许进入数据库时有效。
回答by marc_s
Two hints:
两个提示：
- first of all, do notcall your stored procedures
sp_(something)- Microsoft specifically warns against that
- 首先，你不打电话给你的存储过程
sp_(something)-微软专门警告对抗
We recommend that you do not create any stored procedures using sp_as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures.The name you choose may conflict with some future system procedure.
我们建议您不要创建任何使用sp_作为前缀的存储过程。SQL Server 使用 sp_ 前缀来指定系统存储过程。您选择的名称可能会与某些未来的系统程序冲突。
secondly: I have no trouble calling your stored proc like this:
EXEC proc_test 'B551F2C8-8380-491B-A51F-436E51CDD08F'
其次：我可以像这样调用您的存储过程：
EXEC proc_test 'B551F2C8-8380-491B-A51F-436E51CDD08F'
How are youcalling your stored proc?? Show us!
如何你叫你的存储过程？给我们看一看！
回答by Martin Smith
The message
消息
Incorrect syntax near 'cac671b'.
'cac671b' 附近的语法不正确。
Must mean that it is trying to parse the GUID itself. Try delimiting it in single quotes.
必须意味着它正在尝试解析 GUID 本身。尝试用单引号分隔它。
回答by chryss
I imagine you're copying-and-pasting a string. Can you declare and use it like this?
我想你正在复制和粘贴一个字符串。你能像这样声明和使用它吗？
CREATE PROCEDURE [dbo].[sp_test]
@guidstr varchar(37)
AS
DECLARE @guid uniqueidentifier
SET @guid = CONVERT(uniqueidentifier, @guidstr)
...
