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

Development Tools

ASP.NET

Cursors in Dynamic Sql

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-12-03 : 04:01:48

The Sql Snippet as follow
DECLARE @Result TABLE --Declaration of temporary table
(
CustomerName NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
BillingWater FLOAT NULL,
BillingOther FLOAT NULL,
ReceiptWater FLOAT NULL,
ReceiptOther FLOAT NULL,
ArrearsWater FLOAT NULL,
ArrearsOther FLOAT NULL,
Total FLOAT NULL

)

Declare @mCustomerId NVARCHAR(36)
Declare @mCustomerName NVARCHAR(36)

Declare @mTypeOfTransaction NVARCHAR(36)

DECLARE curBill CURSOR FOR
SELECT TBMIDC_Head.CUSTOMERID ,CustomerName,TypeOfTransaction from TBMIDC_Head,TBMIDC_Customer,TBMIDC_Transaction where TBMIDC_Head.CUSTOMERID=TBMIDC_Customer.CUSTOMERID and TBMIDC_Head.CUSTOMERID=TBMIDC_Transaction.CUSTOMERID

OPEN curBill
FETCH next FROM curBill INTO @mCustomerId , @mCustomerName, @mTypeOfTransaction

WHILE @@FETCH_STATUS = 0
BEGIN
Select @BillingWater=select Other from TBMIDC_Transaction where transactiontype='Bill'
Where this column Other is the dynamic column means it can
be any column from table TBMIDC_Transaction depending on condition specified

How to tackle this problem?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-03 : 04:17:45

Make use of sp_executesql with OUTPUT parameter to tackle this problem.

But depending on what condition and how this column will be decided? please show some example.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-12-03 : 05:00:30

quote:
Originally posted by harsh_athalye

Make use of sp_executesql with OUTPUT parameter to tackle this problem.

But depending on what condition and how this column will be decided? please show some example.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Other Column wil be Passed as the Input Parameter

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-12-03 : 05:30:55

quote:
Originally posted by Swati Jain

quote:
Originally posted by harsh_athalye

Make use of sp_executesql with OUTPUT parameter to tackle this problem.

But depending on what condition and how this column will be decided? please show some example.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Other Column wil be Passed as the Input Parameter

(to be put in Cursor Fetch next)
select @BillWater = water from tbmidc_transaction where typeoftransaction='Bill'
select @Receiptwater =water from tbmidc_transaction where typeoftransaction='Receipt'
select @Arrerswater =water from tbmidc_transaction where typeoftransaction='Arrers'

select @other---->Name of column passed as Parameter

case 'DPC'

select @BillOther = DPC from tbmidc_transaction where typeoftransaction='Bill'
select @ReceiptOther =DPC from tbmidc_transaction where typeoftransaction='Receipt'
select @ArrersOther =DPC from tbmidc_transaction where typeoftransaction='Arrers'

case 'Service'

select @BillOther = Service from tbmidc_transaction where typeoftransaction='Bill'
select @ReceiptOther =Service from tbmidc_transaction where typeoftransaction='Receipt'
select @ArrersOther =Service from tbmidc_transaction where typeoftransaction='Arrers'
......................this is upto nth(Service,DPC type of columns in tbmidc_transaction table)

Is there any generic way to this for n number of columns in table?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-03 : 05:41:59

In general its bad idea to pass column name as parameter but here is a way using sp_executesql:

declare @sql nvarchar(3000)
declare @col nvarchar(255)
declare @out varchar(100)

set @col = 'DPC'
set @sql = N'Select @a = ' + @col + ' from SomeTable'

exec sp_executesql @sql, N'@out varchar(100) output', @a output

select @a

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-12-03 : 05:59:05

quote:
Originally posted by harsh_athalye

In general its bad idea to pass column name as parameter but here is a way using sp_executesql:

declare @sql nvarchar(3000)
declare @col nvarchar(255)
declare @out varchar(100)

set @col = 'DPC'
set @sql = N'Select @a = ' + @col + ' from SomeTable'

exec sp_executesql @sql, N'@out varchar(100) output', @a output

select @a

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Not Understood
How I wil get the parameternames like @ReceiptOther........etc. finaly that i have to insert in temporary table.So how above query wil work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources