Dynamic SQL Statements For Cursor

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

SQL Server Development (2000)

Dynamic SQL Statements For Cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-07 : 11:56:52

Shyam Kumar N writes "I am unable to pass the dynamic table name for the cursor

-----Code Begins

If Exists(SELECT name From sysobjects Where Name = 'VoucherTrans')
Drop Procedure VoucherTrans
Go

CREATE PROCEDURE VoucherTrans
@FinYear varchar(4), @DivName varchar(2),
@VDate datetime
AS
Declare @JoinString varchar(99)
Declare @SQLString varchar(999)
Declare @GDebit varchar(15)
Declare @GCredit varchar(15)
Declare @VStr varchar(15)
Declare @PlFlag varchar(1)
Declare @MonNo varchar(2)
Declare @VocNo varchar(10)
Declare @VNo varchar(15)
Declare @DD varchar(2)
Declare @MM varchar(2)
Declare @YY varchar(4)

Set @JoinString = @FinYear + @DivName
Set @DD = Cast(Day(@VDate) As Varchar)
If Cast(@DD as numeric) < 10
Begin
Set @DD = "0" + Cast(@DD As Varchar)
End
Set @MM = Cast(Month(@VDate) As Varchar)
If Cast(@MM as numeric) < 10
Begin
Set @MM = "0" + Cast(@MM As Varchar)
End
Set @YY = Cast(Year(@VDate) As Varchar)
Set @VStr = @YY + @MM + @DD
Print @VStr
Declare @MaxNo varchar(15)
Set @MaxNo = @VStr + "101"
Set @SQLString = "SELECT ISNULL(MAX(VNO)," + @MaxNo + ") FROM FA_JV" + @JoinString
+ " WHERE CAST(VNO AS VARCHAR) LIKE '" + @VStr + "%'"
Print(@SQLString)

DECLARE VNo_Cursor CURSOR FOR Exec @SQLString
OPEN VNo_Cursor
FETCH NEXT FROM Vno_Cursor INTO @VocNo
CLOSE VNo_Cursor
DEALLOCATE VNo_Cursor
Print @VocNo

Execute VoucherTrans '2002','TM','5-Apr-2002'
---- Code Ends
Exec @SQLString Does not work in this context.
If you have got a solution pls reply.

Thank You

Shyam Kumar N"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-07 : 12:22:53

A. When you DECLARE a cursor, the SELECT statement that defines that cursor must be explicitly stated, it cannot be part of a dynamic SQL string. Therefore, the entire DECLARE CURSOR statement MUST be made into a dynamic SQL string and executed.

B. Even if you did that, because dynamic SQL executes in its own scope, the cursor will NOT be available to the rest of the procedure. Therefore, the ENTIRE procedure must be built as one large SQL string and executed...which is going to be pretty complicated.

C. You absolutely DON'T require a cursor for this at all, and half of the variables you declared ar not necessary either. The following should work:

CREATE PROCEDURE VoucherTrans
@FinYear varchar(4), @DivName varchar(2), @VDate datetime AS
Declare @JoinString varchar(99)
Declare @SQLString varchar(999)
Declare @GDebit varchar(15)
Declare @GCredit varchar(15)
Declare @PlFlag varchar(1)
Declare @MonNo varchar(2)
Declare @VocNo varchar(10)

Set @SQLString = 'SELECT ISNULL(MAX(VNO),' + Convert(char(6), @Vdate, 12) + '101) FROM FA_JV' + @FinYear + @DivName + ' WHERE CAST(VNO AS VARCHAR) LIKE ''' + Convert(char(6), @Vdate, 12) + '%'''

Exec @SQLString

This will return the same results your posted code returns. I believe, however, that your code does more than this, but you did not post all of it. If that's true, and this solution does not work, please post ALL of your code so that we can modify it to work.

Also, please post your table definitions (CREATE TABLE statements). It appears that you are storing voucher details in separate tables based on date and department/division. It may be far more efficient to store all voucher data in a single table, with the proper dates and department stored in columns. This not only consolidates similar data into one place, but you can write much more efficient queries that will perform much faster, and will not require dynamic SQL at all.

Edited by - robvolk on 04/07/2002 12:23:35

shyam
Starting Member

1 Post

Posted - 2002-04-09 : 05:41:10

The entire procedure is given as under.
I could not combine all the entries into a single table,b'cos.
The companies belong to the same group but are treated as different enties.

'--------------------- Proc Starts Here
CREATE PROCEDURE VoucherTransTM
@FinYear varchar(4),@VDate varchar(15),
@Credit varchar(15), @Debit varchar(15),
@Amount varchar(20), @Narration varchar(100),
@RefNo varchar(30),@VType varchar(1)
AS

Declare @JoinString varchar(99)
Declare @SQLString varchar(999)
Declare @GDebit varchar(15)
Declare @GCredit varchar(15)
Declare @VStr varchar(15)
Declare @PlFlag varchar(1)
Declare @DivName varchar(2)
Declare @MonNo varchar(2)
Declare @VocNo varchar(10)
Declare @VNo varchar(15)
Declare @DD varchar(2)
Declare @MM varchar(2)
Declare @YY varchar(4)
Set @DivName = "TM"
Set @GDebit = Left(@Debit,10)
Set @GCredit = Left(@Credit,10)
Set @MonNo = Month(@Vdate)
Set @DD = Cast(Day(@VDate) As Varchar)
If Cast(@DD as numeric) < 10
Begin
Set @DD = "0" + Cast(@DD As Varchar)
End
Set @MM = Cast(Month(@VDate) As Varchar)
If Cast(@MM as numeric) < 10
Begin
Set @MM = "0" + Cast(@MM As Varchar)
End
Set @YY = Cast(Year(@VDate) As Varchar)
Set @VStr = @YY + @MM + @DD
Declare @MaxNo varchar(15)
Set @MaxNo = @VStr + "100"
Declare @TMonth varchar(10)
Set @TMonth = @YY + @MM + "%"
Set @VStr = @VStr + "%"
Print "MaxNo :" + @MaxNo
Print "VStr :" + @VStr
If @FinYear = '2001'
Begin
DECLARE VNo_Cursor CURSOR FOR SELECT ISNULL(MAX(VNO), Cast(@MaxNo As Numeric))
FROM FA_JV2001TM WHERE CAST(VNO AS VARCHAR) LIKE @VStr

DECLARE VocNo_Cursor CURSOR FOR SELECT ISNULL(MAX(VOCNO), 100)
FROM FA_JV2001TM WHERE Cast(VNO As Varchar) LIKE @TMonth
End
Else If @FinYear = '2002'
Begin
DECLARE VNo_Cursor CURSOR FOR SELECT ISNULL(MAX(VNO), Cast(@MaxNo As Numeric))
FROM FA_JV2002TM WHERE CAST(VNO AS VARCHAR) LIKE @VStr

DECLARE VocNo_Cursor CURSOR FOR SELECT ISNULL(MAX(VOCNO), 100)
FROM FA_JV2002TM WHERE Cast(VNO As Varchar) LIKE @TMonth
End
Else If @FinYear = '2003'
Begin
DECLARE VNo_Cursor CURSOR FOR SELECT ISNULL(MAX(VNO), Cast(@MaxNo As Numeric))
FROM FA_JV2003TM WHERE CAST(VNO AS VARCHAR) LIKE @VStr

DECLARE VocNo_Cursor CURSOR FOR SELECT ISNULL(MAX(VOCNO), 100)
FROM FA_JV2003TM WHERE Cast(VNO As Varchar) LIKE @TMonth
End
OPEN VNo_Cursor
FETCH NEXT FROM Vno_Cursor INTO @VNo
CLOSE VNo_Cursor
DEALLOCATE VNo_Cursor

OPEN VocNo_Cursor
FETCH NEXT FROM VocNo_Cursor INTO @VocNo
CLOSE VocNo_Cursor
DEALLOCATE VocNo_Cursor

Set @JoinString = @FinYear + @DivName
Set @VocNo = Cast(@VocNo As Numeric) + 1
Print @VocNo
Print @VNo
Set @VNo = Cast(@VNo As Numeric) + 1
Print @VNo
Begin Transaction
Set @SQLString = "INSERT INTO FA_JV" + @JoinString +
" (VNO,REFNO,VDATE,DEBIT,CREDIT,AMOUNT,NARRATION,VOCNO,VTYPE) VALUES("+
@VNo + ",'" + @RefNo +"','" + @VDate + "','" + @Debit + "','" +
@Credit +"',"+ @Amount +",'"+ @Narration +"',"+ @VocNo +",'"+ @VType +"')"
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -100
End
Set @SQLString = "UPDATE FA_LEDBAL" + @JoinString +
" SET DEBIT = DEBIT+" + @Amount + " WHERE LCODE = '" +
@Debit + "' AND MONNAME = " + @MonNo
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -101
End

Set @SQLString = "UPDATE FA_LEDBAL" + @JoinString +
" SET CREDIT = CREDIT+" + @Amount + " WHERE LCODE = '" +
@Credit + "' AND MONNAME = " + @MonNo
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -102
End

Set @SQLString = "UPDATE FA_LEDGER" + @JoinString +
" SET CURBAL = CURBAL-" + @Amount + " WHERE LCODE = '" +
@Credit + "'"
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -103
End

Set @SQLString = "UPDATE FA_LEDGER" + @JoinString +
" SET CURBAL = CURBAL+" + @Amount + " WHERE LCODE = '" +
@Debit + "'"
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -104
End

Set @SQLString = "UPDATE FA_GRPBAL" + @JoinString +
" SET DEBIT = DEBIT+" + @Amount + " WHERE GCODE = '" +
@GDebit + "' AND MONNAME = " + @MonNo
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -105
End

Set @SQLString = "UPDATE FA_GRPBAL" + @JoinString +
" SET CREDIT = CREDIT+" + @Amount + " WHERE GCODE = '" +
@GCredit + "' AND MONNAME = " + @MonNo
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -106
End

Set @SQLString = "UPDATE FA_GRP" + @JoinString +
" SET CURBAL = CURBAL-" + @Amount + " WHERE GCODE = '" +
@GCredit + "'"
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -107
End

Set @SQLString = "UPDATE FA_GRP" + @JoinString +
" SET CURBAL = CURBAL+" + @Amount + " WHERE GCODE = '" +
@GDebit + "'"
Print @SQLString
Exec(@SQLString)
If @@ERROR <> 0
Begin
RollBack Transaction
Return -108
End

DECLARE PL_Cursor CURSOR FOR
SELECT PLFLAG FROM FA_PLFLAG WHERE DIVNAME = @DivName AND FINYEAR = @FinYear
OPEN PL_Cursor
FETCH NEXT FROM PL_Cursor INTO @PlFlag
CLOSE PL_Cursor
DEALLOCATE PL_Cursor
If @PlFlag = 'Y'
Begin
Set @SQLString = "UPDATE FA_PLFLAG SET PLFLAG = 'Y' WHERE DIVNAME = '" + @DivName + "' AND FINYEAR = " + @FinYear
Print @SQLString
Exec(@SQLString)
End
Else
Begin
Set @SQLString = "INSERT INTO FA_PLFLAG (DIVNAME,FINYEAR,PLFLAG) VALUES ('" + @DivName + "'," + @FinYear + ",'Y')"
Print @SQLString
Exec(@SQLString)
End
Commit Transaction

'-----------Table Structures
CREATE TABLE [dbo].[FA_GRP2002TM] (
[GCODE] [varchar] (15) NOT NULL ,
[GNAME] [varchar] (50) NOT NULL ,
[UNDER] [varchar] (15) NULL ,
[OPBAL] [numeric](20, 2) NOT NULL ,
[CURBAL] [numeric](20, 2) NOT NULL
)
GO

CREATE TABLE [dbo].[FA_GRPBAL2002TM] (
[GCODE] [varchar] (15) NOT NULL ,
[MONNAME] [smallint] NOT NULL ,
[CREDIT] [numeric](20, 2) NOT NULL ,
[DEBIT] [numeric](20, 2) NOT NULL
)
GO

CREATE TABLE [dbo].[FA_JV2002TM] (
[VNO] [numeric](15, 0) NOT NULL ,
[VOCNO] [numeric](18, 0) NULL ,
[VDATE] [datetime] NOT NULL ,
[VTYPE] [char] (1) NULL ,
[DEBIT] [varchar] (15) NOT NULL ,
[CREDIT] [varchar] (15) NOT NULL ,
[AMOUNT] [numeric](20, 2) NOT NULL ,
[REFNO] [varchar] (30) NULL ,
[NARRATION] [varchar] (100) NULL
)
GO

CREATE TABLE [dbo].[FA_LEDBAL2002TM] (
[LCODE] [varchar] (15) NOT NULL ,
[MONNAME] [smallint] NOT NULL ,
[DEBIT] [numeric](20, 2) NOT NULL ,
[CREDIT] [numeric](20, 2) NOT NULL
)
GO

CREATE TABLE [dbo].[FA_LEDGER2002TM] (
[LCODE] [varchar] (15) NOT NULL ,
[GCODE] [varchar] (15) NOT NULL ,
[LNAME] [varchar] (50) NOT NULL ,
[OPBAL] [numeric](20, 2) NOT NULL ,
[CURBAL] [numeric](20, 2) NOT NULL ,
[SLCODE] [varchar] (6) NULL
)
GO

CREATE TABLE [dbo].[FA_PLFLAG] (
[DIVNAME] [varchar] (2) NOT NULL ,
[FINYEAR] [numeric](4, 0) NOT NULL ,
[PLFLAG] [char] (1) NOT NULL
) ON [PRIMARY]
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources