April 5, 2005 at 1:33 am
@@CURSOR_ROWS does not return the exact number of rows fetched in a cursor if fetched row count is =1. My code is
DECLARE @SQLString NVARCHAR(1000)
SET @SQLString = 'DECLARE curCBPiecesID CURSOR FOR ' + CHAR(13)
SET @SQLString = @SQLString + ' SELECT TOP 100 PERCENT iBarId FROM Cooling' + CHAR(13)
SET @SQLString = @SQLString + ' WHERE iBarId IN (' + @sBarIds + ')' + ' AND (bMoved=''N'' )'
SET @SQLString = @SQLString + ' AND ( iNys= ' + @sNys + ' )' + CHAR(13) + ' ORDER by dtscantime ASC '
EXEC sp_executesql @SQLString
OPEN curCBPiecesID
set @sShape = 'Cur:' + CONVERT(varchar,@@CURSOR_ROWS)
@sBarIds is a input parameter with id's as comma seperator.
Is any body can help me. Thanks in advance
April 6, 2005 at 7:42 am
Instead of a cursor and dynamic SQL (both of which should be avoided whenever possible), see my response to another post at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=143488
As in that post, I would create a UDF that returns a table with one column, one row per item in your delimited list.
Then, your code would look like this:
SELECT TOP 100 PERCENT iBarId
FROM Cooling
JOIN dbo.fGetTable(@codelist, ',') c ON Cooling.iBarId = c.code
WHERE bMoved='N'
AND iNys = @sNys
ORDER by dtscantime ASC
Here are the two UDFs, dbo.fGetToken() and dbo.fGetTable(). In this case, you only need to call dbo.fGetTable()
--------------------------------------------------------------
DROP FUNCTION dbo.fGetTable
DROP FUNCTION dbo.fGetToken
GO
CREATE FUNCTION dbo.fGetToken
(
@parm varchar(8000),
@delim varchar(100),
@whichOccur smallint
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @occur int, @spos int
DECLARE @token varchar(8000)
SET @occur = 0
WHILE @occur < @whichOccur AND @parm <> '' AND @parm IS NOT NULL
BEGIN
SET @spos = CHARINDEX( @delim , @parm, 1 )
IF @spos = 0
BEGIN
SET @token = @parm
SET @parm = ''
END
ELSE
BEGIN
SET @token = SubString( @parm, 1, @spos - 1)
SET @parm = Substring( @parm, @spos + Len(@delim), Len(@parm) - @spos )
END
SET @occur = @occur + 1
END
IF @occur <> @whichOccur
SET @token = '' -- or NULL
RETURN @token
END
GO
CREATE FUNCTION dbo.fGetTable
(
@codelist varchar(100),
@delim varchar(10)
)
RETURNS @tbl TABLE (code varchar(10))
AS
BEGIN
DECLARE @code varchar(10), @occur int
SET @occur = 1
SET @code = dbo.fGetToken(@codeList, @delim , @occur)
WHILE @code <> ''
BEGIN
INSERT @tbl (code) VALUES (@code)
SET @occur = @occur + 1
SET @code = dbo.fGetToken(@codeList, @delim , @occur)
END
RETURN
END
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply