Dynamic Sql

Question

Dynamic Sql

mithun.gite

SSCrazy

Points: 2333
More actions
December 30, 2008 at 1:18 am

#217030

Dear All,
I have one issue with my stored proc,
My sp is having dynamic sql like at the end of the sp i execute
exec(@sql)
but size of my @sql is more then 8200 bytes so not able to execute it it is giving error , i cant use text datatype in sp then what to do i dnt know. I have one solution of breaking this string into 2 varibales and executing botht the variables but before that i want to try with one variable onyl..
so now can u suggest the way out for me?
thanks
mithun

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90313 More actions · Answer 1

You can use VARCHAR(MAX) - but I have to ask why you are using dynamic SQL in the first place, and especially a dynamic SQL string that exceeds 8000 characters.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mithun.gite SSCrazy Points: 2333 More actions · Answer 2

sir, thanks for the reply

its a long sp of balance sheet and earlier it is using dynamic sql now we need to do some modification and needs to add two line to the exising

@sql dynamic sql....

and i have put that @sql st the end of the sp..

i tried with varchar(max) but giving the sma eerror.....

pls if any other solution is there?

thanks

mithun

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

mithun.gite (12/30/2008)
sir, thanks for the reply
its a long sp of balance sheet and earlier it is using dynamic sql now we need to do some modification and needs to add two line to the exising
@sql dynamic sql....
and i have put that @sql st the end of the sp..
i tried with varchar(max) but giving the sma eerror.....
pls if any other solution is there?
thanks
mithun

What is the error?

Madhivanan

Failing to plan is Planning to fail

mithun.gite SSCrazy Points: 2333 More actions · Answer 4

hi,

The Error comes bcoz , see

my @STR varibale is having more then 8000 bytes characters ok,

now when i executes it excutes upto 8000 bytes only so it is somewhere in the middle bcoz my query is upto 9000 bytes.

so it gives error like incorrect syntax near where , somethign like that bocz my 8000 bytes are getting over near where.

i hope i m able to explain u

thanks

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

Can u tell us the specific error that you are getting. Did you try declaring the variable as Varchar(MAX). Incorrect syntax does not always mean variable size issue, it could be something to do the query.

Print the @STR variable and see whether the query is written correctly.

"Keep Trying"

krayknot SSCertifiable Points: 7998 More actions · Answer 6

mithun.gite (12/30/2008)
Dear All,
I have one issue with my stored proc,
My sp is having dynamic sql like at the end of the sp i execute
exec(@sql)
but size of my @sql is more then 8200 bytes so not able to execute it it is giving error , i cant use text datatype in sp then what to do i dnt know. I have one solution of breaking this string into 2 varibales and executing botht the variables but before that i want to try with one variable onyl..
so now can u suggest the way out for me?
thanks
mithun

optimize the dynamic query inside the variable

like

store the scalar values outseide the dynamic query and use those variables in the dyamic query

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

mithun.gite SSCrazy Points: 2333 More actions · Answer 7

Below is the exact error i m getting , here i m printing my dynamic SQL.

and i have calculated its lenght its more then 8000 bytes. and with this

i m attacing the sp also....

SELECT * FROM (

SELECT UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,TRANS_ID,SNO,POSTING_DATE,DOCUMENT_TYPE,DOCUMENT_NO,DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,DR_AMT,CR_AMT,BALANCE,ACT_DR_AMT,ACT_CR_AMT,INST_NO,INST_DATE,NARRATION,REMARKS,CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,TRANS_TYPE,HEADER_AC_ID,HEADER_AC_CODE,HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE Between '2007-11-24' and '2008-01-30'

UNION ALL

SELECT 'OpenBal' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'OPENTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'OpenBal'DOCUMENT_TYPE,'OpenBal'DOCUMENT_NO,NULL DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,SUM(DR_AMT),SUM(CR_AMT),SUM(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,GROUP_CODE,GROUP_NAME,ACCOUNT_TYPE,TRANS_TYPE,POPULATED_DATE

UNION ALL

SELECT UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,TRANS_ID,SNO,POSTING_DATE,DOCUMENT_TYPE,DOCUMENT_NO,DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,DR_AMT,CR_AMT,BALANCE,ACT_DR_AMT,ACT_CR_AMT,INST_NO,INST_DATE,NARRATION,REMARKS,CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,TRANS_TYPE,HEADER_AC_ID,HEADER_AC_CODE,HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE Between '2007-11-24' and '2008-01-30'

UNION ALL

SELECT 'OpenBal' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'OPENTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'OpenBal'DOCUMENT_TYPE,'OpenBal'DOCUMENT_NO,NULL DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,SUM(DR_AMT),SUM(CR_AMT),SUM(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,GROUP_CODE,GROUP_NAME,ACCOUNT_TYPE,TRANS_TYPE,POPULATED_DATE

UNION ALL

SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P&LTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'P&L'DOCUMENT_TYPE,'P&L'DOCUMENT_NO,NULL DOCUMENT_DATE,'P&L'ACCOUNT_ID,'P&L'ACCOUNT_CODE,'Profit and Loss'ACCOUNT_NAME,CASE WHEN SUM(BALANCE)>0 THEN ABS(SUM(BALANCE)) ELSE 0 END DR_AMT,CASE WHEN SUM(BALANCE)>0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE

UNION ALL

SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P&LTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'P&L'DOCUMENT_TYPE,'P&L'DOCUMENT_NO,NULL DOCUMENT_DATE,'P&L'ACCOUNT_ID,'P&L'ACCOUNT_CODE,'Profit and Loss'ACCOUNT_NAME,CASE WHEN SUM(BALANCE)>0 THEN ABS(SUM(BALANCE)) ELSE 0 END DR_AMT,CASE WHEN SUM(BALANCE)>0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT, Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE Between '2007-11-24' and '2008-01-30' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE

UNION ALL

SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P&LTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'P&L'DOCUMENT_TYPE,'P&L'DOCUMENT_NO,NULL DOCUMENT_DATE,'P&L'ACCOUNT_ID,'P&L'ACCOUNT_CODE,'Profit and Loss'ACCOUNT_NAME,CASE WHEN SUM(BALANCE)>0 THEN ABS(SUM(BALANCE)) ELSE 0 END DR_AMT,CASE WHEN SUM(BALANCE)>0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE Between '2007-11-24' and '2008-01-30' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE

UNION ALL

SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P&LTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'P&L'DOCUMENT_TYPE,'P&L'DOCUMENT_NO,NULL DOCUMENT_DATE,'P&L'ACCOUNT_ID,'P&L'ACCOUNT_CODE,'Profit and Loss'ACCOUNT_NAME,CASE WHEN SUM(BALANCE)>0 THEN ABS(SUM(BALANCE)) ELSE 0 END DR_AMT,CASE WHEN SUM(BALANCE)>0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACC

Msg 102, Level 15, State 1, Line 16

Incorrect syntax near 'ACC'.[/b]

yogesh.balasubramanian SSC-Addicted Points: 474 More actions · Answer 8

hi, I dont know the permenant or a good solution. I think you can split the sql into two vars. and try to execute them like.

declare @sql varchar(max)

set @sql='somequery'

declare @sql1 varchar(max)

set @sql1='somequery'

exec(@sql+' union '+ @sql1)

Hope its useful.

mithun.gite SSCrazy Points: 2333 More actions · Answer 9

hi,

as i have said in my first post i have already used two paramateters and its working fine with it , but i want to knwo if any good solution is there , but thatnks for this sollution, thanks

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

Check if this works...

Declare @sql VARCHAR(MAX)

SET @sql = 'SELECT * FROM (

SELECT UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,TRANS_ID,SNO,POSTING_DATE,DOCUMENT_TYPE,DOCUMENT_NO,DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,DR_AMT,CR_AMT,BALANCE,ACT_DR_AMT,ACT_CR_AMT,INST_NO,INST_DATE,NARRATION,REMARKS,CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,TRANS_TYPE,HEADER_AC_ID,HEADER_AC_CODE,HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE Between '2007-11-24' and '2008-01-30'

UNION ALL

SELECT 'OpenBal' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'OPENTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'OpenBal'DOCUMENT_TYPE,'OpenBal'DOCUMENT_NO,NULL DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,SUM(DR_AMT),SUM(CR_AMT),SUM(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,GROUP_CODE,GROUP_NAME,ACCOUNT_TYPE,TRANS_TYPE,POPULATED_DATE

UNION ALL

SELECT UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,TRANS_ID,SNO,POSTING_DATE,DOCUMENT_TYPE,DOCUMENT_NO,DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,DR_AMT,CR_AMT,BALANCE,ACT_DR_AMT,ACT_CR_AMT,INST_NO,INST_DATE,NARRATION,REMARKS,CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,TRANS_TYPE,HEADER_AC_ID,HEADER_AC_CODE,HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE Between '2007-11-24' and '2008-01-30'

UNION ALL

SELECT 'OpenBal' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'OPENTRANSID'TRANS_ID,NULL SNO,NULL POSTING_DATE,'OpenBal'DOCUMENT_TYPE,'OpenBal'DOCUMENT_NO,NULL DOCUMENT_DATE,CONTROL_ID ACCOUNT_ID,CONTROL_CODE ACCOUNT_CODE,CONTROL_NAME ACCOUNT_NAME,SUM(DR_AMT),SUM(CR_AMT),SUM(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,GROUP_CODE,GROUP_NAME,(CASE ACCOUNT_TYPE WHEN 'E'THEN 'EXPENSES'WHEN 'I'THEN 'INCOME' WHEN 'Assets'THEN 'APPLICATION OF FUNDS'WHEN 'Funds Transfer'THEN 'APPLICATION OF FUNDS'WHEN 'Intra Branch'THEN 'APPLICATION OF FUNDS'WHEN 'Ownership'THEN 'SOURCES OF FUNDS'WHEN 'Liability'THEN 'SOURCES OF FUNDS' END) ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Liability','Assets','Ownership','Funds Transfer','Intra Branch') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,CONTROL_ID,CONTROL_CODE,CONTROL_NAME,GROUP_CODE,GROUP_NAME,ACCOUNT_TYPE,TRANS_TYPE,POPULATED_DATE

UNION ALL

SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P 0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE<'2007-11-24' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE

UNION ALL

SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P 0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT, Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM OATABLE200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE Between '2007-11-24' and '2008-01-30' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE

UNION ALL

SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P 0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACCOUNT_TYPE,NULL CONTROL_ID,NULL CONTROL_CODE,NULL CONTROL_NAME,1 TRANS_TYPE,NULL HEADER_AC_ID,NULL HEADER_AC_CODE,NULL HEADER_AC_NAME,POPULATED_DATE FROM #FINANCEDATA200 INNER JOIN #TBLBRANCH ON BRANCHID=BRANCH_ID WHERE ACCOUNT_TYPE IN ('Expense','Income') AND POSTING_DATE Between '2007-11-24' and '2008-01-30' GROUP BY COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,ACCOUNT_ID,ACCOUNT_CODE,ACCOUNT_NAME,POPULATED_DATE

UNION ALL

SELECT 'P&L' UNIQ_ID,COMPANY_ID,COMPANY_CODE,COMPANY_NAME,BRANCH_ID,BRANCH_CODE,BRANCH_NAME,'P 0 THEN 0 ELSE ABS(SUM(BALANCE)) END CR_AMT,Sum(BALANCE),NULL ACT_DR_AMT,NULL ACT_CR_AMT,NULL INST_NO,NULL INST_DATE,NULL NARRATION,NULL REMARKS,NULL CURRENCY,'P&L' GROUP_CODE,'P&L' GROUP_NAME,'SOURCES OF FUNDS'ACC'

Execute (@sql)

"Keep Trying"

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 11

EXEC (@SQL1+@SQL2+@SQL3...+@SQLn)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mithun.gite SSCrazy Points: 2333 More actions · Answer 12

Dear All,

Its Really Nice of u guysssssss for replying me some fruitful solutions...

First i tried with Varchar(max) but giving same problem as it can store upto 8060 bytes and finally i had opted for exec(@sql1+@sql2+@sql3) and its working very fine so .......

Thanks for all ur time and knowledge u givne me thanksssss again to all(Jeff Moden,Chirag,Jeffrey Williams,Madhivanan,krayknot ,yogesh.balasubramanian

)......

And Wish U all a very Happy New Year !!!!!!!

Take Care

Mithun

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

I suspect that database is running in Comptibility 80 mode, because I've run scripts substantially larger than 8K in a nvarchar(max) variable with NO issues (130K was the biggest one I can recall).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?