trying to make 2 functions work in sproc

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

SQL Server 2005 Forums

Transact-SQL (2005)

trying to make 2 functions work in sproc

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-11 : 16:29:41

in a nutshell i want to pass a param to my SP and have the matchtext function strip it then the matchtext fn selects some table data to compare to.. i want to use the comparetext fn to compare the single value param1 to all the values returned by the select dbo.tblLanxLocs


SET @@param1 = 'textstringinfo' --going to pass param1 in from app
SET @@param2 
 
--i have a function i use to select these records 
--it removes all nonalphacharacters and all sound letters
--do I use a cursor?
  SELECT [QDINC\UserName].[MatchText](locName + '' + City) FROM  dbo.tblLanxLocs



--how can i get the result of this into a variable?
--or can I just use it as a subselect since only 1 result?
  SELECT [QDINC\USERNAME].[MatchText](@@param1)


--one more function that takes the param1 and compares it to param2
--i need to iterate through the results of the first Select??cursor?
--something like this?
        fetch next from myCursor
        into @@param2
	SELECT [QDINC\UserName].[CompareText](@@param1, @@param2)

I need help making this sproc

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-11 : 18:27:20

This is what I came up with so far.. it does compare but seems like compares with only 1 record..

can anyone see what I have wrong and why the function would not compare to each row?

USE [GP_DB]
GO
/****** Object: StoredProcedure [dbo].[sp_Test] Script Date: 06/11/2009 16:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Test]
@@param1 nvarchar(max)
as
SET NOCOUNT ON
--DECLARE @colA nvarchar(10)
--DECLARE @colB nvarchar(10)
DECLARE @MyCursor CURSOR
--Declare @@param1 nvarchar(max)
Declare @@param2 nvarchar(max)
SET @@param1 = 'CMMRCLWHSCMPNYLBQRQ'--(SELECT [QDINC\user].[MatchText](@@param1))
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT [QDINC\user].[MatchText](locName + '' + City) FROM dbo.tblLanxLocs
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @@param2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT [QDINC\user].[CompareText](@@param1, @@param2)
FETCH NEXT FROM @MyCursor
END

CLOSE @MyCursor
DEALLOCATE @MyCursor

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-12 : 09:30:57

I resolved my issue

USE [GP_Custom]
GO
/****** Object: StoredProcedure [dbo].[sp_Test] Script Date: 06/12/2009 07:35:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Test]
@@param1 nvarchar(max)
as
SET NOCOUNT ON
--DECLARE @colA nvarchar(10)
--DECLARE @colB nvarchar(10)
Declare @@param2 nvarchar(max)
Declare @@counter int
Set @@counter = 0
SET @@param1 = 'CMMRCLWHSCMPNYLBQRQ'--(SELECT [QDINC\user].[MatchText](@@param1))
--Declare @@param1 nvarchar(max)
DECLARE MyCursor CURSOR FAST_FORWARD
FOR
SELECT [QDINC\user].[MatchText](locName + '' + City) FROM dbo.tblLanxLocs
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @@param2
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM MyCursor
INTO @@param2
SELECT [QDINC\user].[CompareText](@@param1, @@param2)
set @@counter=@@counter+1
END

CLOSE MyCursor
DEALLOCATE MyCursor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources