Selecting into a cursor using a string

Question

Post reply

Selecting into a cursor using a string

kalikal

Old Hand

Points: 303
More actions
July 14, 2005 at 7:20 am

#66684

Hi
I am trying to do an execute on a variable to dump the results into a tempory cursor. This is what I am doing, - it works but not sure if this is the best way.
DECLARE @sql VARCHAR(200)
DECLARE @sec VARCHAR(100)
SET @code = ''X'',''Y'''
SET @sql ='SELECT * INTO ##temp FROM customers WHERE customer_code IN (' + @sec + ') '
EXECUTE(@sql)
code here to use ##temp cursor...
Any ideas would be appreciated...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You don't need dynamic sql to do that. What's the problem you're trying to solve in that proc?

kalikal Old Hand Points: 303 More actions · Answer 2

I have a table called customer_invoices and need to sum the invoice value for a given customer or given set of customers. My stored proc would receive a '' separated list of customer codes [@custcodes] and do the calculation. I need to do some calcs like remove tax etc... but I need to get the relavant records into a cursor first ...

SET @sql ='SELECT * INTO ##temp FROM customer_invoices WHERE customer_code IN (' + @custcodes+ ') '

EXECUTE(@sql)

now use ##temp to do some calcs

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Use this to split the list :

--I use this table for many other string operations as well

CREATE TABLE [Numbers] (

[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,

CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED

(

[PkNumber]

) ON [PRIMARY]

GO

Declare @i as int

set @i = 0

while @i < 8000

begin

Insert into dbo.Numbers Default values

set @i = @i + 1

end

GO

CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))

RETURNS TABLE

WITH SCHEMABINDING

AS

Return

Select dtSplitted.EachID, dtSplitted.Rank from (

SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),

CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID

, (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank

FROM dbo.Numbers N

WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter

AND PkNumber 0

Go

SELECT Calc1, Calc2, FROM dbo.customer_invoices WHERE customer_code IN (Select EachID from dbo.fnSplit_Set (@custcodes, ','))

kalikal Old Hand Points: 303 More actions · Answer 4

Ok, I see what you are doing (I think :ermm Will give it a try.

Many Thanks for your help...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

HTH... don't feel stupid, this one is not easy to get at first .

kalikal Old Hand Points: 303 More actions · Answer 6

Hi Remi

I'm hoping you can help me here.

I am getting the following error when executing the sql

"Cannot resolve collation conflict for equal to operation."

I've checked my table and the collation is set to database default. If I change things so that I don't call dbo.fnSplit_Set() then it's ok. I am guessing I need to change dbo.fnSplit_Set() so that the returned 'cursor' collation is the database default as well.

If this is the case how do I do that ?

Many Thanks