将字符串转换为 uniqueidentifier 时失败解决方案

将字符串转换为 uniqueidentifier 时失败
例如有这样一个存储过程

SQL code


CREATE PROCEDURE [dbo].[prtest]    
    @StrId varchar(max),        --公文ID字符串
    @count        int out
AS 
select @count=count(name) from tbA where id in (@StrId)
go

在代码中传入这样的参数，其中strDocID为 'abaa3baa-7338-4ff8-bd74-ac82986c6968','a2251aa2-d760-4728-adf4-4775decd7b9f'

VB.NET code


arrParams(0) = New SqlClient.SqlParameter("@StrId", SqlDbType.VarChar)
        arrParams(0).Value = strDocID

运行程序就会报错：将字符串转换为 uniqueidentifier 时失败
请教下怎么把strDocID转换传过去

------解决方案--------------------

SQL code


--创建测试数据
create table tbA(id uniqueidentifier,name varchar(20))
insert into tbA
select 'abaa3baa-7338-4ff8-bd74-ac82986c6968','a' union all
select 'a2251aa2-d760-4728-adf4-4775decd7b9f','b' union all
select '398FB3BA-4CDE-4CD1-BA78-5F8E173838C6','c'

--查看
select * from tbA
/*
id                                   name
------------------------------------ --------------------
ABAA3BAA-7338-4FF8-BD74-AC82986C6968 a
A2251AA2-D760-4728-ADF4-4775DECD7B9F b
398FB3BA-4CDE-4CD1-BA78-5F8E173838C6 c
*/

--创建存储过程
create procedure [dbo].[prtest]    
    @StrId varchar(max), --公文ID字符串
    @count int out
as 
select @count=count(name) from tbA where charindex(','+ltrim(id)+',',','+@StrId+',')>0
go

--测试存储过程
declare @c int
exec [prtest] 'abaa3baa-7338-4ff8-bd74-ac82986c6968,a2251aa2-d760-4728-adf4-4775decd7b9f',@c out
select @c as cou
/*
cou
-----------
2
*/

将字符串转换为 uniqueidentifier 时失败解决方案

相关推荐