Can I Open Cursor with Dynamic SQL?
-
I have a need to open a cursor with dynamic sql. I want to pass in the database name and table name. But can I do this?
select @SQL = 'SELECT distinct o.name Table_Name FROM ' + @DBName + '.dbo.sysobjects o where o.type=''U'' and o.name not like ''dt%'' and right(o.name, 5) not like ''_Hist'
select @SQL = @SQL + 'and o.name not in (SELECT left(o.name, len(o.name)-5) Table_Name FROM sysobjects o where o.type=''U'' or and o.name not like ''dt%'' and right(o.name, 5) like ''_Hist'')'
/* Loop Through Tables */
DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
select @SQL
-- I have tried all three syntax options to get this to work.
--exec sp_executesql @SQL
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @TableName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
So far I am not having any luck.
Thanks for the help...
-
The short answer is yes. Here is an code example i just wrote using the Northwind DB:
CREATE PROC test_dyn_w_cur (@TableName as varchar(50))
AS
declare @SQL as nvarchar(2000)
Select @SQL = 'SELECT * FROM ' + @TableName
declare @CurString as nvarchar(2000)
Select @CurString = 'DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
select' + @SQL +
'OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @TableName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
CLOSE tables_cursor
DEALLOCATE tables_cursor'
exec sp_executesql @SQL
-
http://www.sommarskog.se/dynamic_sql.html#cursor
While the short answer is yes, the correct answer is:
Stay away from both dynamic sql and cursor if you can.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I heard from many SQL programmers and DBA's to stay away cursors. What other way can you run something like this:
/* Variables that table info is read into. */
DECLARE @StartDate datetime
DECLARE @NbrOfPeriods int
DECLARE @DateDiff char(8)
DECLARE @StepPmtAmt Money
/* Calculation Variables */
DECLARE @TotPmt Money
DECLARE @PeriodCount int, @DDCount int, @DDPeriodType char(2)
DECLARE @CalcDate Datetime
DECLARE @YearMonthIn char(6)
DECLARE @CaseNo char(7)
DECLARE @YearIn char(4)
DECLARE @MonthIn char(2)
SET @CaseNo = '0023048'
SET @YearIn = '2004'
SET @MonthIn = '05'
SET @YearMonthIn = CONVERT(char(4),@YearIn)+CONVERT(char(2),@MonthIn)
SET @CalcDate = @StartDate
DECLARE StepCursor Cursor for
SELECT tblCaseSteps.StartDate, tblCaseSteps.NumberOfPeriods, tblCasePayScheduleTypes.[DateDiff], tblCaseSteps.PaymentAmount
FROM tblCasePayScheduleTypes INNER JOIN
tblCaseSteps ON tblCasePayScheduleTypes.CasePaySchedCodeID = tblCaseSteps.CasePaySchedCodeID INNER JOIN
vwMainCase ON tblCaseSteps.CaseID = vwMainCase.CaseID
WHERE (vwMainCase.CaseNumber = @CaseNo) and
(@YearMonthIn >= CONVERT(char(4),YEAR(tblCaseSteps.StartDate))+
CONVERT(char(2),MONTH(tblCaseSteps.StartDate)))
OPEN StepCursor
FETCH NEXT FROM StepCursor into @StartDate, @NbrOfPeriods, @DateDiff, @StepPmtAmt WHILE @@FETCH_STATUS = 0
BEGIN
SET @PeriodCount = @NbrOfPeriods -1
SET @CalcDate = @StartDate
IF (@MonthIn = CONVERT(char(2),MONTH(@CalcDate))) AND (@YearIn = CONVERT(char(4),YEAR(@CalcDate)))
SET @TotPmt = @TotPmt + @StepPmtAmt
SET @DDPeriodType = rtrim(LEFT(@DateDiff,2))
select @ddperiodtype, @periodCount, @nbrofperiods
WHILE @PeriodCount > 0
BEGIN
SET @DDCount = CONVERT(int,rtrim(SUBSTRING(@datediff,3,8)))
WHILE @DDCount > 0
BEGIN
EXEC ('SET @CalcDate = DATEADD(' + @DDPeriodType + ',1,@CalcDate)')
SET @DDCount = @DDCount - 1
END
IF (@MonthIn = CONVERT(char(2),MONTH(@CalcDate))) AND (@YearIn = CONVERT(char(4),YEAR(@CalcDate)))
SET @TotPmt = @TotPmt + @StepPmtAmt
IF @YearMonthIn < CONVERT(char(4),YEAR(@CalcDate))+CONVERT(char(2),MONTH(@CalcDate))
SET @PeriodCount = 0
ELSE SET @PeriodCount = @PeriodCount - 1
END
FETCH NEXT FROM StepCursor into @StartDate, @NbrOfPeriods, @DateDiff, @StepPmtAmt END close StepCursor deallocate StepCursor Select @TotPmt
Thank you in advance!
-
Thanks for the responses.
While I normaly I would not use a cursor or dynamic SQL it is required in this case. I am looping through tables and creating code base on the properties of the tables. I will try to get around the dynamic problem by creating a stored procedure to preform all of the actions that I wanted to do in the cursor.
What I would like to have when I am done is a code generator that I can install in a temporary database for developement purposes. Then I can run the required code on the target table from here. The object and code that I want to be generated will be created and then I will drop the development DB. Because this will only reside on a development server I am not overly conserned with the resource issues involved with dynamic SQL and cursors.
However, for those that are not aware you should not use dynamic SQL because it does not make use of the stored execution plans that stored procedures, triggers and views do. Thus slower execution. Cursors are also resource intensive. Problems can be induced by locked tables and cursor left open that can be avoided by just not using cursors. That said there are times when the only way to do something is via a cursor.
- Those times are few and far between...with user-defined functions, there are few if any times that you cannot convert your conditional processing via cursors to set based SQL statements.As for the third post, lookup DATEDIFF, DATEADD and DATEPART in BOL, I see no reason why you are converting your dates back in forth as from char's to int's...use a datetime or better yet a smalldatetime type for date calculations.
-
cmartinz,
start a new thread on this. You should also add what your code is supposed to do.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You can open a cursor with dynamic SQL. Please refer the following code for it:
[It is a lengthy code, please refer to the section of th cursor]
CREATE PROCEDURE sptdsgen(@tdstype varchar(1),
@fin_year varchar(9),
@tmonth varchar(2),@location varchar(3),
@supplier nvarchar(1024),
@issue_date varchar(10),
@company_name varchar(60))
AS
BEGIN
set dateformat dmy
DECLARE
@sqlstring nvarchar(2048),
@cnt int,
@cnt1 int,
@vyear varchar(9),
@vmonth varchar(2),
@loc_code varchar(3),
@sup_code int,
@sup_name varchar(1000),
@sname varchar(100),
@rrentfrom datetime, -- added for rental periods in phase II
@rrentto datetime, -- added for rental periods in phase II
@s1 varchar(100),
@s2 varchar(60),
@s3 varchar(60),
@s4 varchar(60),
@s5 varchar(60),
@s6 varchar(60),
@S7 varchar(60),
@s8 varchar(20),
@s9 varchar(6),
@sasl1 varchar(100),
@sasl2 varchar(60),
@sasl3 varchar(60),
@sasl4 varchar(60),
@sasl5 varchar(60),
@sasl6 varchar(60),
@sasl7 varchar(60),
@sasl8 varchar(20),
@sasl9 varchar(6),
@sup_pan varchar(30),
@sup_tan varchar(30),
@nop_code varchar(2), --c
@deduc_date varchar(10),
@tds_period varchar(30), -- a
@cyear varchar(4),
@toyear varchar(4),
@chal_no varchar(30),
@tds_no int,
@cmonth varchar(2),
@cert_no varchar(30),
@nop_name varchar(30),
@sasl_name varchar(1000),
@sasl_pan varchar(30),
@sasl_tan varchar(30),
@chal_det varchar(30),
@printhdr varchar(1),
@printdet varchar(1)
SET @cnt = 0
SET @cnt1 = 0
/** Pick Schlumberger details **/
SELECT @sasl1 = sup_name,@sasl2 = sup_address,
@sasl3 = sup_flatno,@sasl4 = sup_building,
@sasl5 = sup_lane,@sasl6 = sup_area,
@sasl7 = sup_city,@sasl8 = sup_state,@sasl9 = sup_pin,
@sasl_tan = sup_tan,@sasl_pan=sup_pan
FROM tdsupmst
WHERE sup_code = '9999'
BEGIN
IF len(@sasl1) > 0
set @sasl_name = @sasl1
IF len(@sasl2) > 0
set @sasl_name = @sasl_name + char(13) + @sasl2
IF len(@sasl3) > 0
set @sasl_name = @sasl_name + char(13) + @sasl3
IF len(@sasl4) > 0
set @sasl_name = @sasl_name + ' ' + @sasl4
IF len(@sasl5) > 0
set @sasl_name = @sasl_name + char(13) + @sasl5
IF len(@sasl6) > 0
set @sasl_name = @sasl_name + ' ' + @sasl6
IF len(@sasl7) > 0
set @sasl_name = @sasl_name + char(13) + @sasl7
IF len(@sasl9) > 0
set @sasl_name = @sasl_name + '-' + @sasl9
IF len(@sasl8) > 0
set @sasl_name = @sasl_name + char(13) + @sasl8
END
IF @tdstype = 'M'
BEGIN
IF @tmonth >= '4' AND @tmonth <= '12'
SET @cyear = left(@fin_year,4)
ELSE
SET @cyear = right(@fin_year,4)
SET @tds_period = datename(month, cast('01.' + @tmonth+ '.'+@cyear as varchar(10))) + ',' + @cyear
set @sqlstring = N'DECLARE crtdsno CURSOR'+ char(13)
set @sqlstring = @sqlstring + N'FOR' + char(13)
set @sqlstring = @sqlstring + N'Select a.vyear,a.vmonth,a.cloc_code,a.sup_code,c.sup_name,a.nop_code' + char(13)
set @sqlstring = @sqlstring + N'FROM tdvouchr a,tdchamst b,tdsupmst c' +char(13)
set @sqlstring = @sqlstring + N'WHERE a.vtype = ''1''' + char(13)
set @sqlstring = @sqlstring + N'AND a.rowid = b.rowid' + char(13)
set @sqlstring = @sqlstring + N'AND a.sup_code = c.sup_code' + char(13)
set @sqlstring = @sqlstring + N'AND a.cloc_code = c.loc_code' + char(13)
set @sqlstring = @sqlstring +N'AND a.vyear = '''+ @fin_year + '''' + char(13)
set @sqlstring = @sqlstring +N'AND a.vmonth = '''+ @tmonth + '''' + char(13)
set @sqlstring = @sqlstring +N'AND a.cloc_code = '''+ @location + '''' + char(13)
set @sqlstring = @sqlstring +N'AND a.sup_code IN ('+ @supplier + ')' + char(13)
set @sqlstring = @sqlstring +N'AND b.tds_no = ''0''' + char(13)
set @sqlstring = @sqlstring +N'group by a.vyear,a.vmonth,a.cloc_code,a.sup_code,c.sup_name,a.nop_code' + char(13)
set @sqlstring = @sqlstring +N'Order By cloc_code,sup_name,nop_code' + char(13)
set @sqlstring = @sqlstring +N'For read only'
/*Print @sqlstring*/
exec sp_executesql @sqlstring
OPEN crtdsno
FETCH NEXT FROM crtdsno INTO @vyear,@vmonth,@loc_code,@sup_code,@sup_name,
@nop_code--,@deduc_date,@chal_no
Select @cmonth = DATEPART(mm, getdate())
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
Begin Transaction
/** to check type of tds generation **/
SELECT sup_code from tdsuptds
WHERE fin_year = @fin_year
AND loc_code = @loc_code
AND sup_code = @sup_code
IF @@rowcount = 0
INSERT INTO tdsuptds(fin_year,loc_code,sup_code,tds_type)
VALUES
(@vyear,@loc_code,@sup_code,@tdstype)
/** Generate tds no **/
Select @tds_no = last_value + 1
from tdcodgen
WHERE keyflds = 'tds_no'
/**masked for reprinting query to work **/
--IF convert(int, @tmonth) >=1 AND convert(int, @tmonth) 0
set @sup_name = @s1
IF len(@s2) > 0
set @sup_name = @sup_name + char(13) + @s2
IF len(@s3) > 0
set @sup_name = @sup_name + char(13) + @s3
IF len(@s4) > 0
set @sup_name = @sup_name + char(13) + @s4
IF len(@s5) > 0
set @sup_name = @sup_name + char(13) + @s5
IF len(@s6) > 0
set @sup_name = @sup_name + char(13) + @s6
IF len(@s7) > 0
set @sup_name = @sup_name + char(13) + @S7
IF len(@s8) > 0
set @sup_name = @sup_name + char(13) + @s8
IF len(@s9) > 0
set @sup_name = @sup_name + char(13) + @s9
END
/** Insert into tds header table **/
INSERT into tdtdshdr
(loc_code,sup_code,sup_name,sup_pan,nop_name,sasl_name,sasl_tan,sasl_pan,tds_no,tds_period,fin_year,issue_date,orderBy)
Values
(@loc_code,@sup_code,@sup_name,@sup_pan,@nop_name,@sasl_name,@sasl_tan,@sasl_pan,
@cert_no,@tds_period,@vyear,@issue_date,@tds_no)
IF @@RowCount > 0
SET @cnt = 1
/** Insert into tds detail table **/
Insert into tdtdsdet
(tds_no,payment_date,gross_amount,tds_deducted,tax_rate,challan_no,challan_date,challan_bank,fin_year)
Select @cert_no,a.tdeduction_date,
--sum(b.gross_amt - (b.taxable_amt * a.tax_rate/100)) as gross,
--Changed by Samit Yadav only Gross amount to be reflected in Report not Net
sum(b.gross_amt) as gross,
sum(b.taxable_amt * a.tax_rate/100) as tax,
a.tax_rate,d.challan_no,d.challan_date,c.bank_name+','+c.city,@vyear
From tdvouchr a,tdinvdet b, tdbnkmst c,tdchamst d
WHERE a.vtype = '1'
AND a.rowid = d.rowid
AND a.vyear = b.vyear
AND a.vmonth = b.vmonth
AND a.cloc_code = b.cloc_code
AND a.sup_code = b.sup_code
AND a.vcode = b.vcode
AND a.vtype = b.vtype
AND d.challan_bank = c.bank_code
AND a.cloc_code = @loc_code
AND a.sup_code = @sup_code
AND a.vyear = @fin_year
AND a.vmonth = @tmonth
--AND convert(varchar(10), a.tdeduction_date,103) = @deduc_date
AND d.tds_no = @cert_no
group by a.tdeduction_date,a.tax_rate,d.challan_no,d.challan_date,c.bank_name,c.city
IF @@rowcount > 0
SET @cnt1 = 1
If @@error = 0
COMMIT Transaction
ELSE
ROLLBACK Transaction
END
FETCH NEXT FROM crtdsno INTO @vyear,@vmonth,@loc_code,@sup_code,@sup_name,
@nop_code--,@deduc_date,@chal_no
END
/*If @@error = 0
COMMIT Transaction
ELSE
ROLLBACK Transaction*/
END
IF @tdstype = 'Y'
BEGIN
SET @cyear = left(@fin_year,4)
SET @toyear = right(@fin_year,4)
--C
--SET @tds_period = '01.04.' + @cyear + ' ' + 'to' +' ' + '30.03.' + @toyear
SET @tds_period = '01.04.' + @cyear + ' ' + 'to' +' ' + '31.03.' + @toyear
--C END
--print @tds_period
set @sqlstring = N'DECLARE crtdsno CURSOR'+ char(13)
set @sqlstring = @sqlstring + N'FOR' + char(13)
--set @sqlstring = @sqlstring + N'Select a.vyear,a.cloc_code,a.sup_code,a.nop_code, b.challan_no' + char(13)
--C
set @sqlstring = @sqlstring + N'Select a.vyear,a.cloc_code,a.sup_code,c.sup_name,a.nop_code,a.rentfrom,a.rentto' + char(13)
set @sqlstring = @sqlstring + N'FROM tdvouchr a,tdchamst b, tdsupmst c' + char(13)
set @sqlstring = @sqlstring + N'WHERE a.vtype = ''1''' + char(13)
set @sqlstring = @sqlstring + N'AND a.rowid = b.rowid' + char(13)
set @sqlstring = @sqlstring + N'AND a.sup_code = c.sup_code' + char(13)
set @sqlstring = @sqlstring + N'AND a.cloc_code = c.loc_code' + char(13)
set @sqlstring = @sqlstring + N'AND a.vyear = '''+ @fin_year + '''' + char(13)
set @sqlstring = @sqlstring + N'AND a.cloc_code = '''+ @location + '''' + char(13)
set @sqlstring = @sqlstring + N'AND a.sup_code IN ('+ @supplier + ')' + char(13)
set @sqlstring = @sqlstring + N'AND tds_no = ''0''' + char(13)
--set @sqlstring = @sqlstring +N'group by a.vyear,a.cloc_code,a.sup_code,a.nop_code,b.challan_no' + char(13)
--Changed by Samit
set @sqlstring = @sqlstring +N'group by a.vyear,a.cloc_code,a.sup_code,c.sup_name,a.nop_code,a.rentfrom,a.rentto' + char(13)
set @sqlstring = @sqlstring +N'Order By cloc_code,sup_name,nop_code,rentfrom' + char(13)
set @sqlstring = @sqlstring +N'For read only'
exec sp_executesql @sqlstring
OPEN crtdsno
--FETCH NEXT FROM crtdsno INTO @vyear,@loc_code,@sup_code,@nop_code,@chal_no
--C
--Challan Number was droped from the Query
FETCH NEXT FROM crtdsno INTO @vyear,@loc_code,@sup_code,@sname,@nop_code,@rrentfrom,@rrentto
Select @cmonth = DATEPART(mm, getdate())
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
Begin Transaction
SELECT sup_code from tdsuptds
WHERE fin_year = @fin_year
AND loc_code = @loc_code
AND sup_code = @sup_code
IF @@RowCount = 0
INSERT INTO tdsuptds(fin_year,loc_code,sup_code,tds_type)
VALUES(@fin_year,@loc_code,@sup_code,@tdstype)
/** Generate tds no **/
Select @tds_no = last_value + 1
from tdcodgen
WHERE keyflds = 'tds_no'
--SET @cert_no = 'SASL'+ '/' + cast(datepart(mm, getdate()) as varchar(2)) + '/'+ cast(@tds_no as varchar(4))
/** included financial year in (yearly) certificate genr.;company name is passed parameter - phase II **/
SET @cert_no = @company_name + '/' + @fin_year + '/' + cast(@tds_no as varchar(4))
/*
--The Query needs to be changed Since the Challan Number was droped from the Cursor
UPDATE tdchamst
SET tds_no = @cert_no
WHERE challan_no = @chal_no
AND EXISTS(select sup_code from tdvouchr
WHERE tdchamst.rowid = tdvouchr.rowid
AND tdvouchr.cloc_code = @loc_code
AND tdvouchr.sup_code = @sup_code
AND tdvouchr.nop_code = @nop_code
AND tdvouchr.vyear = @vyear)
*/
UPDATE tdchamst
SET tds_no = @cert_no
WHERE EXISTS(select sup_code from tdvouchr
WHERE tdchamst.rowid = tdvouchr.rowid
AND tdvouchr.cloc_code = @loc_code
AND tdvouchr.sup_code = @sup_code
AND tdvouchr.nop_code = @nop_code
AND tdvouchr.vyear = @vyear)
UPDATE tdcodgen
SET last_value = @tds_no
WHERE keyflds = 'tds_no'
--C
/*
SELECT @nop_name = nop_name
FROM tdratmst
WHERE nop_code = @nop_code
*/
--This change was made to pick up Rental in case of Rental Company/Non-Company
SELECT @nop_name = nop_name FROM tdratmst
WHERE nop_code=(SELECT nop_parent FROM tdratmst WHERE nop_code =@nop_code )
--C ---END
/** Pick supplier details **/
SELECT @s1 = sup_name,@s2=sup_address,
@s3 = sup_flatno,@s4 = sup_building,@s5 = sup_lane,
@s6 = sup_area,@s7 = sup_city,
@s8 = sup_state ,@s9 = sup_pin,
@sup_pan = sup_pan
FROM tdsupmst
WHERE sup_code = @sup_code
AND loc_code = @loc_code
Begin
If len(@s1) > 0
set @sup_name = @s1
IF len(@s2) > 0
set @sup_name = @sup_name + char(13) + @s2
IF len(@s3) > 0
set @sup_name = @sup_name + char(13) + @s3
IF len(@s4) > 0
set @sup_name = @sup_name + char(13) + @s4
IF len(@s5) > 0
set @sup_name = @sup_name + char(13) + @s5
IF len(@s6) > 0
set @sup_name = @sup_name + char(13) + @s6
IF len(@s7) > 0
set @sup_name = @sup_name + char(13) + @S7
IF len(@s8) > 0
set @sup_name = @sup_name + char(13) + @s8
IF len(@s9) > 0
set @sup_name = @sup_name + char(13) + @s9
END
IF @rrentfrom ''
BEGIN
SET @cyear = DATEPART(yyyy,@rrentfrom)
SET @toyear = DATEPART(yyyy,@rrentto)
SET @tds_period = '01.04.' + @cyear + ' ' + 'to' +' ' + '31.03.' + @toyear
END
/** Insert into tds header table **/
INSERT into tdtdshdr
(loc_code,sup_code,sup_name,sup_pan,nop_name,sasl_name,sasl_tan,sasl_pan,tds_no,tds_period,fin_year,
issue_date,orderBy)
Values
(@loc_code,@sup_code,@sup_name,@sup_pan,@nop_name,@sasl_name,@sasl_tan,@sasl_pan,
@cert_no,@tds_period,@vyear,@issue_date,@tds_no)
IF @@RowCount > 0
SET @cnt = 1
/** Insert into tds detail table **/
Insert into tdtdsdet
(tds_no,payment_date,gross_amount,tds_deducted,tax_rate,challan_no,challan_date,challan_bank,fin_year)
Select @cert_no,a.tdeduction_date,
--sum(b.gross_amt - (b.taxable_amt * a.tax_rate/100)) as gross,
--C only Gross amount to be reflected in Report not Net
--C Gross amount Changed to Taxable
sum(b.taxable_amt) as gross,
sum(b.taxable_amt * a.tax_rate/100) as tax,
a.tax_rate,d.challan_no,d.challan_date,c.bank_name+','+c.city,@vyear
From tdvouchr a,tdinvdet b, tdbnkmst c, tdchamst d
WHERE a.vtype = '1'
AND a.rowid = d.rowid
AND a.vyear = b.vyear
AND a.vmonth = b.vmonth
AND a.cloc_code = b.cloc_code
AND a.sup_code = b.sup_code
AND a.vcode = b.vcode
AND a.vtype = b.vtype
AND d.challan_bank = c.bank_code
AND a.cloc_code = @loc_code
AND a.sup_code = @sup_code
AND a.nop_code = @nop_code
AND a.vyear = @fin_year
AND d.tds_no = @cert_no
group by a.tdeduction_date,a.tax_rate,d.challan_no,d.challan_date,c.bank_name,c.city
IF @@RowCount > 0
SET @cnt1 = 1
/** Updating issue dates for all certificates which have all transactions against same challan **/
UPDATE tdtdshdr
SET issue_date = CASE
WHEN DATEPART(dw, c.challan_date + 7) = 1 THEN c.challan_date + 8
WHEN DATEPART(dw, c.challan_date + 7) 1 THEN c.challan_date + 7
END
FROM tdtdsdet c
WHERE tdtdshdr.tds_no = c.tds_no
AND tdtdshdr.tds_no = @cert_no
AND exists(select a.tds_no from tdtdsdet a
WHERE 1 = ( SELECT COUNT(DISTINCT challan_no ) FROM tdtdsdet b
WHERE a.tds_no = b.tds_no)
AND tdtdshdr.tds_no = a.tds_no)
IF @@error = 0
COMMIT Transaction
ELSE
ROLLBACK Transaction
END
--FETCH NEXT FROM crtdsno INTO @vyear,@loc_code,@sup_code,@nop_code,@chal_no
--Changed by Samit
--Challan Number was droped from the Query
FETCH NEXT FROM crtdsno INTO @vyear,@loc_code,@sup_code,@sname,@nop_code,@rrentfrom,@rrentto
END
END
CLOSE crtdsno
deallocate crtdsno
IF @@error = 0
BEGIN
IF @cnt > 0 AND @cnt1 > 0
RETURN(1) -- with rows
ELSE
RETURN(2) --without rows
END
ELSE
RETURN (3) --with errors
END
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply