SQL Server Performance Forum – Threads Archive

Dynamic Cursor Declaration in Function

Hi! I’m a Oracle PL/SQL developer. I’m finding some problem while converting a Oracle function into SQL Server function. I have created a function for retrieving some Description by passing Code. Here, there is a set-up table for the generating the Code so it the query to fetch the description I need to be build dynamic SQL. I tried to pass dynamic sql to declare a cursor but its giving error. So can you tell me how to declare a cursor with dynamic sql, which can easily be done with Oracle PL/SQL… I have this function in Oracle.. CREATE OR REPLACE FUNCTION FUNC_GETDIVDESCR (p_DIVCODE IN VARCHAR2) RETURN VARCHAR2 AS v_Div_NoOfLevel INT :=0; v_DivLevel INT :=0; v_Div_Width INT :=0; v_DivDescr VARCHAR2(100) :=”; v_Counter INT :=0; v_SqlString VARCHAR2(2000):=”; TYPE TmpCursorType IS REF CURSOR; DIV_Cursor TmpCursorType; BEGIN v_DivDescr :=”; v_Div_NoOfLevel :=0; SELECT FAST_DIV_NOOFLVL INTO v_Div_NoOfLevel FROM M_DIVISIONSETUP; v_Counter :=1; v_Div_Width :=0; WHILE v_Counter <= v_Div_NoOfLevel LOOP v_SqlString := ”; v_SqlString := ‘SELECT FAST_DIV_LVL’ || CAST(v_Counter AS VARCHAR) || ‘ FROM M_DIVISIONSETUP’; OPEN DIV_Cursor FOR v_SqlString; FETCH DIV_Cursor INTO v_Div_Width; SELECT DESCR INTO v_DivDescr FROM M_DIVISION WHERE SUBSTR(P_DIVCODE ,v_Counter,v_Div_Width ) = CODE; v_Counter := v_Counter + 1; END LOOP; RETURN v_DivDescr; END; This is the SQL Version :: [ which is giving error ] CREATE FUNCTION FUNC_GETDIVDESCR (@P_DIVCODE AS VARCHAR(50)) RETURNS VARCHAR(100) AS BEGIN DECLARE @v_Div_NoOfLevel INT DECLARE @v_DivLevel INT DECLARE @v_Div_Width INT DECLARE @v_DivDescr VARCHAR(100) DECLARE @v_Counter INT DECLARE @v_SqlString NVARCHAR(2000) SET @v_DivDescr =” SET @v_Div_NoOfLevel =0 SELECT @v_Div_NoOfLevel = cast (FAST_DIV_NOOFLVL as varchar) FROM M_DIVISIONSETUP SET @v_Counter =1 SET @v_Div_Width =0 WHILE @v_Counter <= @v_Div_NoOfLevel BEGIN SET @v_SqlString = ” SET @v_SqlString = ‘SELECT FAST_DIV_LVL’ + CAST(@v_Counter AS VARCHAR) + ‘ FROM M_DIVISIONSETUP' DECLARE c_Division CURSOR FOR @v_SqlString OPEN c_Division FETCH NEXT FROM c_Division INTO @v_Div_Width SELECT @v_DivDescr = @v_DivDescr + DESCR + ‘ ‘ FROM M_DIVISION WHERE SUBSTRING (@P_DIVCODE ,@v_Counter,@v_Div_Width ) = CODE SET @v_Counter = @v_Counter + 1 IF @v_Counter > @v_Div_NoOfLevel BREAK ELSE CONTINUE END RETURN @v_DivDescr END Can you help me..plz!![<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

See if this helps:http://www.sommarskog.se/dynamic_sql.html That’s the "usual" reference when dealing with dynamic sql. Can you post what your function should return? To me it seems you don’t need a cursor at all. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Cursor solution is not efficient in SQL Server. Post some sample data and the result you want Madhivanan Failing to plan is Planning to fail

The function will return like this : Total Division charecter should be 50; Code of Division Level 1 : ‘D’
Description : ‘Direct’ Code of Division Level 2 : ‘DIV’
Description : ‘Division’ Code of Division Level 2 : ‘2’
Description : ‘2’ So the Division Combination is ‘DDIV2——————————————-‘ When Fuction is executed : FUNC_GETDIVDESCR(‘DDIV2——————————————-‘)
It should return : ‘DIRECT DIVISION 2’ I hope I did made you understand.. And Thank You for considering my situation.. Bye!

What about all those hyphens ("-") – it looks like an non-normalized coding system. What can they have been thinking when they designed this database? Clever programming does not a high-performance system make. If at least each of the parts of the coding has a fixed length, so you can say: character 1 is Level 1, characters 2-4 are Level 2, character 5 is Level 3 – oooops no, that was a Level 2 code again … … anyway, then you could JOIN on SUBSTRING expressions – which seems to be essentially what the cursor is doing – looking up the description for matching substrings. But it looks like one fine mess to me.

For this if you use delimiter in between values
like [email protected]@[email protected]@2 then you can into one table variable with while statement.
Return as a table variable.
Try to avoid using cursors. Ganesan B.
Well defined problem is half way solved.

Hi! The hyphens is just used to replacing the blank spaces.. more over i’m using SUBSTRING while joining the code with the master.. But my problem is not with the logic of retriving or displaying the string. But with dynamic building of sql to get the position index, that I’ll use in the SUBSTRING .. For e.g The table structure of M_DIVISIONSETUP is: FAST_DIV_NOOFLVLdecimal(5,5) FAST_DIV_LVL1 decimal(5,5) FAST_DIV_LVL2 decimal(5,5) FAST_DIV_LVL3 decimal(5,5) FAST_DIV_LVL4 decimal(5,5) FAST_DIV_LVL5 decimal(5,5) … So when I use this query : SELECT @v_Div_NoOfLevel = cast (FAST_DIV_NOOFLVL as varchar) FROM M_DIVISIONSETUP I get the number of level from setup then, i have to build the sql like: SET @v_SqlString = ‘SELECT FAST_DIV_LVL’ + CAST(@v_Div_NoOfLevel AS VARCHAR) + ‘ FROM M_DIVISIONSETUP' From this query I can get the Position stored in FAST_DIV_LVL1 or FAST_DIV_LVL2 or FAST_DIV_LVL3.. which I have to decide which fields to take according to FAST_DIV_NOOFLVL field in the Division Setup. This is the reason i want to use a dynamic sql in a cursor. Can anyone suggest if there is any alternative option other than using a cursor.. Thanks every one for looking into my problem.. SouravM <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

The table structure is not normalised. Read about Normalisation
http://www.datamodel.org/NormalizationRules.html Madhivanan Failing to plan is Planning to fail

Can you give some sample data … (1) a DIV_CODE string such as it is fed to the function
(2) how you match the codes from DIV_CODE with the stuff that you find in M_DIVISIONSETUP …in other words: the parameters that take you from "DDIV2" to "DIRECT DIVISION 2". From what I imagine, M_DIVISIONSETUP should say:
(1) the first part is only 1 character wide,
(2) the second part is 3 characters wide,
(3) the third part is again 1 character wide,
etc. From the design of M_DIVISIONSETUP I would guess that there is also a separate table listing the valid options for each part. I have worked with composite codes in the past, and there are a couple of tricks to do stuff in-line without having to iterate. But I need more details to get the whole picture.

You can use the table variables instead of using cursors. we can stop usage of cursors with while loop.
Ganesan B.
Well defined problem is half way solved.

]]>