CREATE PROCEDURE [dbo].adduser
@UserID uniqueidentifier,
@UserName nvarchar(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'INSERT INTO userinfo (UserID,UserName) VALUES
(''{77CBE35A-7BAA-42FF-95EB-A02189D19D2C}'','''+@UserName+''')'
EXEC sp_executesql @SQL
END
GO
==================================================================================
上面存储过程能够正常运行,但插入到UserID的数值是{77CBE35A-7BAA-42FF-95EB-A02189D19D2C}并不是由参数@UserID传过来的.
--------------------------------------------
我想实现由参数@UserID传过来,插入到UserID中.类似下面的代码(不用newid(),UserID已在其它表存在对应的值)
==========================================
SET @SQL = 'INSERT INTO userinfo (UserID,UserName) VALUES
('''+@UserID+''','''+@UserName+''')'
==========================================
但上面的代码提示有语法错误,"对数据类型而言运算符无效.运算符为add,类型为uniqueidentifier"究竟应该怎样写???
@UserID uniqueidentifier,
@UserName nvarchar(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'INSERT INTO userinfo (UserID,UserName) VALUES
(''{77CBE35A-7BAA-42FF-95EB-A02189D19D2C}'','''+@UserName+''')'
EXEC sp_executesql @SQL
END
GO
==================================================================================
上面存储过程能够正常运行,但插入到UserID的数值是{77CBE35A-7BAA-42FF-95EB-A02189D19D2C}并不是由参数@UserID传过来的.
--------------------------------------------
我想实现由参数@UserID传过来,插入到UserID中.类似下面的代码(不用newid(),UserID已在其它表存在对应的值)
==========================================
SET @SQL = 'INSERT INTO userinfo (UserID,UserName) VALUES
('''+@UserID+''','''+@UserName+''')'
==========================================
但上面的代码提示有语法错误,"对数据类型而言运算符无效.运算符为add,类型为uniqueidentifier"究竟应该怎样写???
@UserID VARCHAR(40), /*将uniqueidentifier改为VARCHAR类型*/
@UserName nvarchar(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'INSERT INTO userinfo (UserID,UserName) VALUES
(CAST('''+@UserID+''' AS uniqueidentifie),'''+@UserName+''')' /*插入时使用CAST转换为uniqueidentifie类型*/
EXEC sp_executesql @SQL
END
GO
CREATE PROCEDURE [dbo].adduser
@UserID VARCHAR(40), /*将uniqueidentifier改为VARCHAR类型*/
@UserName nvarchar(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'INSERT INTO userinfo (UserID,UserName) VALUES
(CAST('''+@UserID+''' AS uniqueidentifier),'''+@UserName+''')' /*插入时使用CAST转换为uniqueidentifier类型*/
EXEC sp_executesql @SQL
END
GO