Looping with temporary tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Looping with temporary tables

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-06-01 : 12:17:04

Hi

I'm new to SP's, although was kindly give a few pointers to get me started. I now need to return a set of results based on a collection of ID's. I've been able to send the SP a string with my ID's separated by a pipe, but I can't seem to get the temp table to return any results. My code does not error, but again, no results. I've put the following together from a collection of stuff that did work, and a little guess work. Any support would really help - I have no idea!

Thanks as always


USE [CB_Gymnastics]
GO
/****** Object:  StoredProcedure [dbo].[usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber]    Script Date: 06/01/2012 16:51:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber]
@i_params varchar(100)

  as

declare @sClubID as nvarchar(36)
declare @uClubID as uniqueidentifier
declare @iCounter int
declare @iCounterEnd int
declare @iMemberID int

select @sClubID = value from dbo.fnSplit(@i_params, '|') where idx = 2
select @iCounterEnd = cast(value as int) from dbo.fnSplit(@i_params, '|') where idx = 3

--SET @uClubID = CAST(@sClubID AS uniqueidentifier)

CREATE TABLE #TotalSelectedMemebersWithtValidSmsNumber(
MemberID int,
Member_Sms_Mobile varchar(30) )

SET @iCounter = 1

WHILE @iCounter <= @iCounterEnd
BEGIN
	select @iMemberID = cast(value as int) from dbo.fnSplit(@i_params, '|') where idx = @iCounter + 3

	INSERT INTO #TotalSelectedMemebersWithtValidSmsNumber (MemberID, Member_Sms_Mobile)
	SELECT MemberID, Member_Sms_Mobile
	FROM dbo.tbl_Members tbl_Members
	WHERE tbl_Members.MemberID = @iMemberID

	SET @iCounter = @iCounter + 1
END

SELECT MemberID, Member_Sms_Mobile
FROM #TotalSelectedMemebersWithtValidSmsNumber

drop table #TotalSelectedMemebersWithtValidSmsNumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:24:29

actually you dont need loop at all. this would be enough


USE [CB_Gymnastics]
GO
/****** Object:  StoredProcedure [dbo].[usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber]    Script Date: 06/01/2012 16:51:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber]
@i_params varchar(100)

  as
	
	SELECT MemberID, Member_Sms_Mobile
	FROM dbo.tbl_Members tbl_Members
        JOIN dbo.fnSplit(@i_params, '|')  f
	ON tbl_Members.MemberID = f.value
        WHERE f.idx % 4 = 0
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-06-01 : 12:32:30

Thanks, although when I try to run it with:

exec dbo.usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber 'ExecSP|dbo.usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber|8|15|16|17|18|19|20|21|22'

I get the following error:

Msg 245, Level 16, State 1, Procedure usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber, Line 6
Conversion failed when converting the varchar value 'ExecSP' to data type int.

Remember, the ID's start from position 3 within the string - does that make a difference?

Thanks again

Richard Law

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:37:22

why do you pass SP name also along with string?

shouldnt it be just


Exec dbo.usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber '8|15|16|17|18|19|20|21|22'

anyways if you plan to call it like what you've posted it should be


USE [CB_Gymnastics]
GO
/****** Object:  StoredProcedure [dbo].[usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber]    Script Date: 06/01/2012 16:51:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Members_GetTotalSelectedMemebersWithtValidSmsNumber]
@i_params varchar(100)

  as
	
	SELECT MemberID, Member_Sms_Mobile
	FROM dbo.tbl_Members tbl_Members
        JOIN dbo.fnSplit(@i_params, '|')  f
	ON tbl_Members.MemberID = f.value
        WHERE f.idx >= 3
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-06-01 : 12:41:12

Perfect! Thank you so much.

Richard Law

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:42:44

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources