如何使用存储过程在数据库中追加记录? [英] how can i append records in database with store procedure?

问题描述

嗨 我有一个带有此记录的表(GroupId，ParentGroupId，GroupName).
我想展示一个具有完整路径的小组，从主要父母到自我

像这样:红樱桃:红樱桃>>"樱桃>>水果


我写了一个程序，给我它的父母的名字，但是我想有一个程序，将父母的名字附加给我，并给我一个字符串

我想对该表中的所有记录执行此操作

thanx for u answers

解决方案

将程序的恢复数据保存到脾气暴躁的表中.
写功能设置父母heighrachichy.
那么您可以插入数据.

首先，您必须在数据库中编写类似的函数

  set   ANSI_NULLS   ON 
设置  QUOTED_IDENTIFIER  >关闭
转到
 ALTER  功能 [dbo].[funcGenerate_TreeViewDescsNEW]
(
     @ Code   as   Varchar ( 15 )，
     @ TreeViewType   as   varchar ( 15 )
)返回  Varchar ( 255 )

目标
开始
 DECLARE   @ vcReturnValue   varchar ( 255 )
声明  @ ParentIDLevel1   varchar ( 255 )
声明  @ ParentIDLevel2   varchar ( 255 )
声明  @ ParentIDLevel3   varchar ( 255 )
声明  @ ParentIDLevel4   varchar ( 255 )
声明  @ SubcodeLevel2   varchar ( 255 )
声明  @ SubcodeLevel3   varchar ( 255 )


 IF  @ TreeViewType = ' 位置'
开始
声明  @ intLocLevel   int 
声明  @ numLocID  数字( 18 ， 0 )
声明  @ numRefLocID  数字( 18 ， 0 )
声明  @ vcLocationCode   varchar ( 15 )
声明  @ vcLocationDescription   varchar ( 255 )
声明  @ vcLocDescLevel1   varchar ( 255 )
声明  @ vcLocDescLevel2   varchar ( 255 )
声明  @ vcLocDescLevel3   varchar ( 255 )
声明  @ vcLocDescLevel4   varchar ( 255 )


设置  @ vcLocationCode  =  @ Code 
选择  @ vcLocationCode  = numLocID， @ numRefLocID  = ParentID 来自 LocationMaster 其中 numLocID = @ vcLocationCode
设置  @ intLocLevel  =  0 

如果  @ numRefLocID  =  0 
开始
选择  @ vcLocationDescription  = vcLocationDescription  from  LocationMaster 其中 numLocID = @ vcLocationCode
 set  @ vcReturnValue =  @ vcLocationDescription 
 END 

同时( @ numRefLocID <>  0 )
开始
如果( @ intLocLevel  =  1 )
开始
设置  @ vcLocationDescription  =  @ vcLocationDescription  +( 选择 vcLocationDescription 来自 LocationMaster 其中 numLocID = @ vcLocationCode)+ ' >'
 END 
其他
开始
设置  @ intLocLevel  =  1 
设置  @ vcLocationDescription  =(>选择 vcLocationDescription 来自 LocationMaster 其中 numLocID = @ vcLocationCode)+ ' >'
 END 


选择 @ vcLocationCode = numLocID，@ numRefLocID = ParentID 来自 LocationMaster 其中 numLocID = @ numRefLocID

 IF ( @ numRefLocID  =  0 )
开始
选择  @ vcLocDescLevel1  = vcLocationDescription  from  LocationMaster 其中 numLocID = @ vcLocationCode

设置  @ vcLocationDescription  =  @ vcLocationDescription  +  @ vcLocDescLevel1 
 END 
 END 

 set  @ vcReturnValue =  @ vcLocationDescription 
 END  

 返回  @ vcReturnValue 
结束 

之后，通过传递GroupID在您的过程中调用它
这样的

< pre lang = " > SELECT [dbo] .[funcGenerate_TreeViewDescsNEW]( 250 ，'  LOCATION' span>) as  '  vcLocationDescription'，
            numLocID
         FROM  LocationMaster 

您的输出将是这样.......

Bb-Chennai-Venus Millenium Square-Royapuram> Chennai> Tamilnadu

，您可以添加"n"个子组正常工作

hi I have a table with this records(GroupId,ParentGroupId,GroupName).
i want to show a group with its full path, from main parent to it self

like this: red cherry: red cherry>>cherry>>fruit


i write a Procedure that give me its parents names,but i want to have a prosedure that append the parents name and give me one string

i want to do this for all records in that table

thanx for u answers

解决方案

Hi,
take retival data of procedure to tempery table.
write funtion to set the parent heighrachichy.
then you can insert data.

first you have to write a function like that in your database

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER Function [dbo].[funcGenerate_TreeViewDescsNEW]
(
    @Code as Varchar(15),
    @TreeViewType as varchar(15)
)Returns Varchar(255)

As
Begin
	DECLARE @vcReturnValue varchar(255)
	declare @ParentIDLevel1 varchar(255)
	declare @ParentIDLevel2 varchar(255)
	declare @ParentIDLevel3 varchar(255)
	declare @ParentIDLevel4 varchar(255)
	declare @SubcodeLevel2 varchar(255)
	declare @SubcodeLevel3 varchar(255)
	

	IF @TreeViewType='LOCATION'
		BEGIN
			declare @intLocLevel int
			declare @numLocID numeric(18,0)
			declare @numRefLocID numeric(18,0)
			declare @vcLocationCode varchar(15)
			declare @vcLocationDescription varchar(255)
			declare @vcLocDescLevel1 varchar(255)
			declare @vcLocDescLevel2 varchar(255)
			declare @vcLocDescLevel3 varchar(255)
			declare @vcLocDescLevel4 varchar(255)

			
			set @vcLocationCode = @Code
			select @vcLocationCode = numLocID,@numRefLocID = ParentID from LocationMaster where numLocID=@vcLocationCode
			Set @intLocLevel = 0
			
			if @numRefLocID = 0
				BEGIN
					Select @vcLocationDescription =vcLocationDescription from LocationMaster where numLocID=@vcLocationCode
					set @vcReturnValue= @vcLocationDescription
				END

			While (@numRefLocID <> 0)
				BEGIN
					if (@intLocLevel = 1 )
						BEGIN
							Set @vcLocationDescription = @vcLocationDescription + (Select vcLocationDescription from LocationMaster where numLocID=@vcLocationCode) + '>'
						END
					Else
						BEGIN
							Set @intLocLevel = 1
							Set @vcLocationDescription =(Select vcLocationDescription from LocationMaster where numLocID=@vcLocationCode) + '>'
						END
						

					select @vcLocationCode=numLocID,@numRefLocID=ParentID from LocationMaster where numLocID=@numRefLocID
					
					IF (@numRefLocID = 0)
						BEGIN
							Select @vcLocDescLevel1 =vcLocationDescription from LocationMaster where numLocID=@vcLocationCode

							Set @vcLocationDescription = @vcLocationDescription + @vcLocDescLevel1
						END
				END

				set @vcReturnValue= @vcLocationDescription
		END

RETURN @vcReturnValue
End

after that call it in your procedure by passing the GroupID
like that

<pre lang="sql">SELECT [dbo].[funcGenerate_TreeViewDescsNEW](250,'LOCATION') as 'vcLocationDescription',
            numLocID
        FROM LocationMaster

your output will be like that.......

Bb-Chennai-Venus Millenium Square-Royapuram>Chennai>Tamilnadu

you can add ''n'' number of subgroups it work properly

这篇关于如何使用存储过程在数据库中追加记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！