更新时间:2023-01-24 20:39:14
USE [DBNAME]
GO
/ * *****对象:StoredProcedure [dbo]。[sp3_loop]脚本日期:02/17/2014 20:03:13 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CRWEATE PROCEDURE [dbo]。[sp3_loop]
@ param1 varchar ( 1000 )= ' 1,2,3,4'
AS
SET NOCOUNT ON
DECLARE @ Prm_id varchar ( 10 ), @Pos int
DECLARE @ Str1 varchar ( 1000 )
SET @ param1 = LTRIM(RTRIM( @ param1 ))+ ' ,'
SET @ Pos an> = CHARINDEX(' ,', @ param1 , 1 )
IF REPLACE( @ param1 ,' ,',' ')<> ' '
BEGIN
WHILE @ Pos > 0
BEGIN
SET @ Prm_id = LTRIM(RTRIM( LEFT ( @ param1 , @ Pos - 1 )))
IF @ prm_id <> ' '
IF ( EXISTS (选择 1 来自 gblTempTable 其中 clasi_rootid = @ prm_id ))
BEGIN
选择 * 来自 gblTempTable 其中 clasi_rootid = @ prm_id 订单 by clasi_clasid
END
SET @ param1 = RIGHT ( @ param1 ,LEN( @ par am1 ) - @ Pos )
SET @Pos = CHARINDEX(' ,', @ param1 , 1 )
END
END
我尝试过SQL程序,但无法创建多个表,所以我生成了vb .net代码请使用它正常工作..公共 oCn As 新 System.Data.SqlClient.SqlConnection( 数据源=(本地);初始目录= MSTMDB; Uid = sa)
私有 Sub Button2_Cl ick( ByVal sender As System。 Object , ByVal e As System.EventArgs) Handles Button2.Click
Dim ds,ds1 As 新 DataSet
Dim root_param 作为 字符串 =
Dim i As 整数 = 0
Dim j 作为 整数 = 0
ds = sel ect_data( 通过clasi_rootid按clasi_rootid从) clasi_rootid)。ToString& (level1,clasi_clasid,clas_name,clasi_parentid,clasi_rootid)值(& ds1.Tables( 0 ) .Rows(j).Item( level1)。ToString& ,& ds1.Tables( 0 )。行( j).Item( clasi _clasid)。ToString& ,'& ds1.Tables( 0 )。行(j).Item( clas_name)。ToString& ',& Val(ds1.Tables( 0 )。行(j).Item( clasi_parentid)。ToString)& ,& ds1.Tables( 0 )。行(j).Item( clasi_rootid)。ToString& ))
如果 ds.Tables( 0 )。Rows.Count> 0 然后
( i<> ds.Tables( 0 )。Rows.Count)
Get_ValueByQuery( CREATE TABLE [dbo]。[gblTempTable& ds.Tables( 0 )。行(i ).Item( clasi_rootid)。ToString& ]([level1] [int] NULL,[clasi_clasid] [int] NULL,[clas_name] [varchar](50)NULL,[clasi_parentid] [int] NULL,[clasi_rootid] [int] NULL)ON [PRIMARY])
ds1 = select_data( 从gblTempTable中选择level1,clasi_clasid,clas_name,clasi_parentid,clasi_rootid,其中clasi_rootid =& ds.Tables( 0 )。行(i) .Item( clasi_rootid)。ToString)
如果 ds1.Tables( 0 )。Rows.Count> 0 然后
( j<> ds1.Tables( 0 )。Rows.Count)
Get_ValueByQuery( 插入gblTempTable& ds.Tables( 0 )。行(i).Item(
j = j + 1
结束 虽然
结束 如果
ds1.Clear()
j = 0
i = i + 1
结束 while
结束 如果
结束 Sub
功能 select_data( ByVal str As String ) As DataSe t
如果 oCn.State = ConnectionState.Closed 那么
oCn.Open( )
结束 如果
Dim cmd 作为 新 SqlClient.SqlCommand(str,oCn)
Dim da As 新 SqlClient.SqlDataAdapter(cmd)
Dim ds As 新 DataSet ( bpl)
尝试
da.Fill(ds, bpl)
Catch ex As 例外
MessageBox.Show(ex.Message)
最后
oCn.Close()
结束 尝试
返回 ds
结束 功能
功能 Get_ValueByQuery( ByVal 查询作为 String ) as String
Dim temp 作为 字符串
Dim ocom 作为 新 SqlClient.SqlCommand
暗淡 oRead As SqlClient.SqlDataReader
If oCn.State = ConnectionState.Closed 然后
oCn.Open()
结束 如果
ocom.Connection = oCn
ocom.CommandText = Query
oRead = ocom.ExecuteReader
如果 oRead.HasRows = True 那么
oRead .Read()
如果 IsDBNull(oRead( 0 ))= True 然后
temp = 0
否则
temp = oRead( 0 )
结束 如果
oRead .Close()
Else
temp = 0
oRead.Close()
结束 如果
返回 temp
结束 功能跨度>
I have a table in a hierarchy order. Now i want to split into multiple tables where only root-id=1 data are stored. Then in same manner, root-id=2 should be stored in 2nd table.It should be repeated for for n number of roots. The table looks like this:
##gblTempTable
level clasi_clasid clasi_name clasi_parentid clasi_rootid 1 1 Main Classification1 NULL 1 1 3 Main Classification1 NULL 1 1 4 Main Classification2 NULL 1 1 5 Main Classification3 NULL 1 2 8 Sub Classification1 5 2 3 11 Next Sub Classification1 8 3 2 7 Sub Classification1 4 2 3 10 Next Sub Classification1 7 3 2 6 Sub Classification1 3 2 2 2 Sub Classification1 1 2
The desired output:
##gblTempTable1
level clasi_clasid clasi_name clasi_parentid clasi_rootid 1 1 Main Classification 1 NULL 1 1 3 Main Classification 1 NULL 1 1 4 Main Classification 2 NULL 1 1 5 Main Classification 3 NULL 1
##gblTempTable2
level clasi_clasid clasi_name clasi_parentid clasi_rootid 2 8 Sub Classification 1 5 2 2 7 Sub Classification 1 4 2 2 6 Sub Classification 1 3 2 2 2 Sub Classification 1 1 2
In this way, i want output for n number of root id. The ##gblTempTable should be split into multiple temp tables in a loop manner. How can i achieve this?
The mai intention is to produce book oreder indexing like 1->1.1->1.1.1, 2->2.1,2->2.2,2.1.1 etc.
USE [DBNAME] GO /****** Object: StoredProcedure [dbo].[sp3_loop] Script Date: 02/17/2014 20:03:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CRWEATE PROCEDURE [dbo].[sp3_loop] @param1 varchar(1000)='1,2,3,4' AS SET NOCOUNT ON DECLARE @Prm_id varchar(10), @Pos int DECLARE @Str1 varchar(1000) SET @param1 = LTRIM(RTRIM(@param1)) + ',' SET @Pos = CHARINDEX(',', @param1, 1) IF REPLACE(@param1, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @Prm_id = LTRIM(RTRIM(LEFT(@param1, @Pos - 1))) IF @prm_id <> '' IF(EXISTS(select 1 from gblTempTable where clasi_rootid = @prm_id)) BEGIN select * from gblTempTable where clasi_rootid = @prm_id order by clasi_clasid END SET @param1 = RIGHT(@param1, LEN(@param1) - @Pos) SET @Pos = CHARINDEX(',', @param1, 1) END END
I have tried through SQL procedures but unable to create multiple tables so I have generate vb.net code please use this it is working properly..Public oCn As New System.Data.SqlClient.SqlConnection("Data Source=(local);Initial Catalog=MSTMDB;Uid=sa") Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim ds, ds1 As New DataSet Dim root_param As String = "" Dim i As Integer = 0 Dim j As Integer = 0 ds = select_data("select clasi_rootid from gblTempTable group by clasi_rootid order by clasi_rootid") If ds.Tables(0).Rows.Count > 0 Then While (i <> ds.Tables(0).Rows.Count) Get_ValueByQuery("CREATE TABLE [dbo].[gblTempTable" & ds.Tables(0).Rows(i).Item("clasi_rootid").ToString & "]([level1] [int] NULL, [clasi_clasid] [int] NULL, [clas_name] [varchar](50) NULL, [clasi_parentid] [int] NULL,[clasi_rootid] [int] NULL) ON [PRIMARY]") ds1 = select_data("select level1,clasi_clasid,clas_name,clasi_parentid,clasi_rootid from gblTempTable where clasi_rootid =" & ds.Tables(0).Rows(i).Item("clasi_rootid").ToString) If ds1.Tables(0).Rows.Count > 0 Then While (j <> ds1.Tables(0).Rows.Count) Get_ValueByQuery("Insert into gblTempTable" & ds.Tables(0).Rows(i).Item("clasi_rootid").ToString & "(level1,clasi_clasid,clas_name,clasi_parentid,clasi_rootid) values(" & ds1.Tables(0).Rows(j).Item("level1").ToString & "," & ds1.Tables(0).Rows(j).Item("clasi_clasid").ToString & ",'" & ds1.Tables(0).Rows(j).Item("clas_name").ToString & "'," & Val(ds1.Tables(0).Rows(j).Item("clasi_parentid").ToString) & "," & ds1.Tables(0).Rows(j).Item("clasi_rootid").ToString & ")") j = j + 1 End While End If ds1.Clear() j = 0 i = i + 1 End While End If End Sub Function select_data(ByVal str As String) As DataSet If oCn.State = ConnectionState.Closed Then oCn.Open() End If Dim cmd As New SqlClient.SqlCommand(str, oCn) Dim da As New SqlClient.SqlDataAdapter(cmd) Dim ds As New DataSet("bpl") Try da.Fill(ds, "bpl") Catch ex As Exception MessageBox.Show(ex.Message) Finally oCn.Close() End Try Return ds End Function Function Get_ValueByQuery(ByVal Query As String) As String Dim temp As String Dim ocom As New SqlClient.SqlCommand Dim oRead As SqlClient.SqlDataReader If oCn.State = ConnectionState.Closed Then oCn.Open() End If ocom.Connection = oCn ocom.CommandText = Query oRead = ocom.ExecuteReader If oRead.HasRows = True Then oRead.Read() If IsDBNull(oRead(0)) = True Then temp = "0" Else temp = oRead(0) End If oRead.Close() Else temp = "0" oRead.Close() End If Return temp End Function