帮我 看看 这个 存储过程 有啥 我问题
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[proc_GetBrandlist]
(
@sortID int
)
AS
BEGIN
declare @next int
declare @insertCount int --记录成功读取的条数
declare @brandidstr int --要读取的品牌ID
declare @brandnum int --要读取品牌的数量
declare @brandlistid nvarchar(2000) --要读取的brandid字符串
begin transaction
select @brandlistid=brandid from DS_BrandSort where sortid=@sortID
declare @BrandID int,@BrandName nvarchar(50),@logoUrl varchar(50),@logoH int,@logoW int,@BrandOrder int,@IsShow int,@Re nvarchar(200),@brandname2 nvarchar(50),@IsBold int,@TitleColor varchar(50)
create table #BrandList(
BrandID int NULL,
BrandName nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
LogoUrl nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
LogoH int NULL,
LogoW int NULL,
BrandOrder int NULL,
IsShow int NOT NULL,
Re text COLLATE Chinese_PRC_CI_AS NULL,
BrandName2 nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
IsBold int NULL,
TitleColor nvarchar(20) COLLATE Chinese_PRC_CI_AS NULL
)
-- brandlistid='1,2,3,4,5,6'; set @next=1
set @insertCount=0
set @brandnum=dbo.Get_StrArrayLength(@brandlistid,',')
while @next<=@brandnum
begin
set @brandidstr=dbo.Get_StrArrayStrOfIndex(@brandlistid,',',@next)
select @BrandID=BrandID,@BrandName=BrandName,@LogoUrl=LogoUrl,@LogoH=LogoH,@LogoW=LogoW,@BrandOrder=BrandOrder,@IsShow=IsShow,@Re=Re,@BrandName2=BrandName2,@IsBold=IsBold,@TitleColor=TitleColor from DS_BrandInf where BrandID=@brandidstr
--执行自己的插入语句
exec('insert into #BrandList values('+ @BrandID+','+@BrandName+','+@logoUrl+','+@logoH+','+@logoW+','+@BrandOrder+','+@IsShow+','+@Re+','+@brandname2+','+@IsBold+','+@TitleColor+')')
if(@@error<>0) --当插入有误时,插入
begin
rollback transaction
end
else
begin
set @insertCount= @insertCount + 1
end
--插入结束
set @next = @next+1
end
select * from #Brandlist
commit transaction
END
CREATE function Get_StrArrayStrOfIndex
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0
select @location =len(@str)+1
--这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location-@start)
endgo
CREATE function Get_StrArrayLength
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
这一句 出错
exec('insert into #BrandList values('+ @BrandID+','+@BrandName+','+@logoUrl+','+@logoH+','+@logoW+','+@BrandOrder+','+@IsShow+','+@Re+','+@brandname2+','+@IsBold+','+@TitleColor+')')@BrandName 是字符 我想拼出 SQL 语句 怎么 弄啊
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[proc_GetBrandlist]
(
@sortID int
)
AS
BEGIN
declare @next int
declare @insertCount int --记录成功读取的条数
declare @brandidstr int --要读取的品牌ID
declare @brandnum int --要读取品牌的数量
declare @brandlistid nvarchar(2000) --要读取的brandid字符串
begin transaction
select @brandlistid=brandid from DS_BrandSort where sortid=@sortID
declare @BrandID int,@BrandName nvarchar(50),@logoUrl varchar(50),@logoH int,@logoW int,@BrandOrder int,@IsShow int,@Re nvarchar(200),@brandname2 nvarchar(50),@IsBold int,@TitleColor varchar(50)
create table #BrandList(
BrandID int NULL,
BrandName nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
LogoUrl nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
LogoH int NULL,
LogoW int NULL,
BrandOrder int NULL,
IsShow int NOT NULL,
Re text COLLATE Chinese_PRC_CI_AS NULL,
BrandName2 nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
IsBold int NULL,
TitleColor nvarchar(20) COLLATE Chinese_PRC_CI_AS NULL
)
-- brandlistid='1,2,3,4,5,6'; set @next=1
set @insertCount=0
set @brandnum=dbo.Get_StrArrayLength(@brandlistid,',')
while @next<=@brandnum
begin
set @brandidstr=dbo.Get_StrArrayStrOfIndex(@brandlistid,',',@next)
select @BrandID=BrandID,@BrandName=BrandName,@LogoUrl=LogoUrl,@LogoH=LogoH,@LogoW=LogoW,@BrandOrder=BrandOrder,@IsShow=IsShow,@Re=Re,@BrandName2=BrandName2,@IsBold=IsBold,@TitleColor=TitleColor from DS_BrandInf where BrandID=@brandidstr
--执行自己的插入语句
exec('insert into #BrandList values('+ @BrandID+','+@BrandName+','+@logoUrl+','+@logoH+','+@logoW+','+@BrandOrder+','+@IsShow+','+@Re+','+@brandname2+','+@IsBold+','+@TitleColor+')')
if(@@error<>0) --当插入有误时,插入
begin
rollback transaction
end
else
begin
set @insertCount= @insertCount + 1
end
--插入结束
set @next = @next+1
end
select * from #Brandlist
commit transaction
END
CREATE function Get_StrArrayStrOfIndex
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0
select @location =len(@str)+1
--这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location-@start)
endgo
CREATE function Get_StrArrayLength
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
这一句 出错
exec('insert into #BrandList values('+ @BrandID+','+@BrandName+','+@logoUrl+','+@logoH+','+@logoW+','+@BrandOrder+','+@IsShow+','+@Re+','+@brandname2+','+@IsBold+','+@TitleColor+')')@BrandName 是字符 我想拼出 SQL 语句 怎么 弄啊
exec('insert into #BrandList values('+ convert(nvarchar(40),@BrandID)+','''+@BrandName+''','''+@logoUrl+''','''+@logoH+''','''+@logoW+''','+convert(nvarchar(40),@BrandOrder)+','''+@IsShow+''','''+@Re+''','''+@brandname2+''','''+@IsBold+''','''+@TitleColor+''')')
convert 附近 语法错误 这样 不行
insert into #BrandList values( @BrandID,@BrandName,@logoUrl,@logoH,@logoW,@BrandOrder,@IsShow,@Re,@brandname2,@IsBold,@TitleColor)
我这样都出错
消息 208,级别 16,状态 0,第 1 行
对象名 '#BrandList' 无效。
消息 208,级别 16,状态 0,过程 proc_GetBrandlist,第 62 行
对象名 '#Brandlist' 无效。
消息 266,级别 16,状态 2,过程 proc_GetBrandlist,第 62 行
EXECUTE 后的事务计数指示缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。上一计数 = 6,当前计数 = 0。
exec('insert into #BrandList values('+ @BrandID+','''+@BrandName+''','+@logoUrl+','+@logoH+','+@logoW+','+@BrandOrder+','+@IsShow+','+@Re+','+@brandname2+','+@IsBold+','+@TitleColor+')')
还有 虚拟表 有什么问题啊
消息 208,级别 16,状态 0,第 1 行
对象名 '#BrandList' 无效。
消息 208,级别 16,状态 0,过程 proc_GetBrandlist,第 63 行
对象名 '#Brandlist' 无效。