Dynamic Sql in Cursors - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Sql in Cursors

Author

Topic

Rauken
Posting Yak Master

108 Posts

Posted - 2002-09-17 : 10:26:22

I create a dynamic sql string based on different search criterias. Now I would like to use this dynamic sql in a cursor, anyone have an idea how?

An example:
DECLARE ok_cur CURSOR FOR
SELECT product_id FROM ....

I would like to change the SELECT part against my string.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-17 : 10:33:43

You would have to write the entire DECLARE CURSOR command as dynamic SQL, and then you'll have problems utilizing the cursor outside of that dynamic SQL.

Dynamic SQL and cursors don't mix well, and in any case you probably don't need a cursor anyway. Can you post your queries, table structures, and some more detail on what you're trying to do? It's quite possible that there's an easier way to do this.

Rauken
Posting Yak Master

108 Posts

Posted - 2002-09-17 : 10:45:29

Thanks for your answer. Here's some background to my problem:
Users can search for products with three search criterias, material, garment type and color. The site has support for 12 languages. My problem is in the result some text might not exist for some languages or specific products. When that happens English is suppose to be shown and if there is no English text available I show nothing.

I have made a query which shows all product_id matching the search critiera. Because of the missing text and my need to check if it's available in other languages I feel that I must use Cursor to check each product_id. I then insert all texts into a temporary table. Am I wrong any other ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-17 : 10:53:14

How do you store text in other languages? Are separate tables kept for each language, or is it all in one table? Can you post a sample query and the output you want?

This thread has some code demonstrating a non-dynamic SQL method for multiple search parameters:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19555

There are other methods too, if you do a forum search using the term "search" or "searching" you'll get a bunch of info...probably too much

Table structures here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19886

Edited by - robvolk on 09/17/2002 10:54:56

Rauken
Posting Yak Master

108 Posts

Posted - 2002-09-17 : 10:55:22

I posted the design of some of the tables in the main thread.. my mistake.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-17 : 10:56:26

No problem, I linked them.

Can you provide a sample of a search request and the result you want?

Rauken
Posting Yak Master

108 Posts

Posted - 2002-09-17 : 11:11:14

Thanks for your help.

Ok here is a sample of how it might look:

 
SELECT	p.product_id,
	p.model,
	de_mat.dictionary_text,
	de_gt.dictionary_text		

FROM	dictionary_entry de_mat,
	product p,
	dictionary_entry de_gt,
	garment_type gt

WHERE	de_mat.dictionary_id = p.material_dictionary_id
AND	de_gt.dictionary_id = gt.dictionary_id
AND	gt.garment_type_id = p.garment_type_id
AND	p.garment_type_id = 'A'

The result would look something like this
23425 0252 250g polyester Jacket

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources