如何根据传递给Sp的动态参数循环数据 [英] How Can I Loop Thru Data Based On Dynamic Parameter Passed To Sp

问题描述

我有一个sp和传递值，如'xxx~yyy'，'10~12~15'，'1~2~7'等...我必须通过数据循环才能得到结果集

i需要循环，例如

1. xxx-10-1

2. xxx-10-2

3. xxx-10-7

4. xxx-12-1

5. xxx-12-2

6. xxx-12 -7

...................

............. ......

yyy-15-1

yyy-15-2

yyy-15-7



如何在sp中实现这一点

i have an sp and passing values like 'xxx~yyy', '10~12~15', '1~2~7' ,etc... and i have to loop thru the data to get the resultset
i need to loop thru like
1. xxx-10-1
2. xxx-10-2
3. xxx-10-7
4. xxx-12-1
5. xxx-12-2
6. xxx-12-7
...................
...................
yyy-15-1
yyy-15-2
yyy-15-7

how to acheive this with in sp

推荐答案

它正在工作......

< br $>

It is working...

USE [DBNAME]
GO
/****** Object:  StoredProcedure [dbo].[ohms_sp_add_services_list]    Script Date: 02/16/2014 17:51:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp2_loop]
	@param1 varchar(1000)='xxx~yyy'
	AS

SET NOCOUNT ON

DECLARE @Prm_id varchar(10), @Pos int
	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 Table_2 where substring(Test1,1,3) = @prm_id))

			BEGIN
			select * from Table_2 where substring(Test1,1,3) = @prm_id
			END

			SET @param1 = RIGHT(@param1, LEN(@param1) - @Pos)
			SET @Pos = CHARINDEX('~', @param1, 1)
			END
			END

这篇关于如何根据传递给Sp的动态参数循环数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！