March 24, 2009 at 7:35 am
Hi
I am passing a table name as a parameter in stored procedure ?? when I did that, it is not displaying the entire program ie when i use PRINT @sql.....and even if execute the program it is not executing the entire program
Here is what i did
CREATE PROCEDURE [IDCALAIMS]
(
@tablename nvarchar(50)
)
as
DECLARE @sSQL nvarchar(500)
SET @sSQL='-------------THERE ARE 150 LINES OF CODE------------------------------------------------------------------------'
PRINT @sql
It is displaying only 1/4th of the code..........
What should I do?Please help it is bugging me since 4 days.......
March 24, 2009 at 7:50 am
most likely u r running out of size. ie 150 lines of code wouldnt fit into 500 nvarchars..
so u must use a datatype like text or smthng and if u r on sql2005 u can use varchar(max)..
u cant directly assign anything to a text variable..so u might have to pass it as parameter..
Regards,
Raj
March 24, 2009 at 7:57 am
also remember that each @sql in an EXEC(@SQL) will only allow for 4000 charaters to be run.
So if it's long you need to do something like this
EXEC (@SQL + @SQL1) etc
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 24, 2009 at 7:59 am
thanks a lot man.....
March 24, 2009 at 8:01 am
@ Christopher,
exec takes the param nvarhcar which can have max 4000.
So no matter how many '+' u do if ur total is gretaer than 4000 it doesnt work..
Regards,
Raj
March 24, 2009 at 8:03 am
so is there any solution for this......
March 24, 2009 at 8:05 am
may be if possible ask ur front end or pgming guys to pass it to ur sp as parameter..
thats wat i did last time..but lemme give it some more thght
Regards,
Raj
March 24, 2009 at 8:15 am
there is no front end people......i need to display the result set in reporting.....If i use varchar it is print the proc.......but when i execute its is giving an ERROR:Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
March 24, 2009 at 8:36 am
arr.nagaraj (3/24/2009)
@ Christopher,exec takes the param nvarhcar which can have max 4000.
So no matter how many '+' u do if ur total is gretaer than 4000 it doesnt work..
I know that NVARCHAR only takes 4000 which is why I hinted at the fact that he needs to use more than one variable to build his SQL statement.
If you post your code on this page we might be able to more with regards to trying to find a better solution to your problem.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 24, 2009 at 8:41 am
are u using sp_executesql/exec/print..?
Regards,
Raj
March 24, 2009 at 8:43 am
EXEC sp_executesql @sSQL
March 24, 2009 at 8:47 am
CREATE PROCEDURE [IDENTIFICATIONCLAIMS]
(
@UniTab varchar(max)
)
as
DECLARE @sSQL varchar(max)
SET @sSQL=
'SELECT [RID] , DIS =''P1'' FROM
( SELECT [RID]
FROM '+@UniTab+ ' R7
INNER JOIN [dbo].[TABA1] T1
ON R7.[DIAG]=T1.[ICDCOD]
WHERE T1.[Per Se Disabling]=''YES''
)A GROUP BY [RID]
UNION ALL
SELECT [RID],DIS =''P2'' FROM
( SELECT [RID],[Cnt] = COUNT(INV)
FROM '+@UniTab+ '
WHERE [DIAG] IN (''49310'',''49311'',''49312'',''49320'',''49321'',''49322'',''49381'',''49382'',''4939'',''49390'',''49391'' )
AND (INV =''01'')
GROUP BY [RID] Having Count(*) >= 2
UNION
SELECT [RID],[Cnt] = COUNT([HCPC_NDC])
FROM ' +@UniTab+ '
WHERE [DIAG] IN (''49310'',''49311'',''49312'',''49320'',''49321'',''49322'',''49381'',''49382'',''4939'',''49390'',''49391'' )
AND [HCPC_NDC] IN (''99281'',''99282'',''99283'',''99284'',''99285'')
GROUP BY [RID] Having Count([HCPC_NDC]) >= 3
)A GROUP BY [RID]
UNION ALL
SELECT [RID], DIS =''P3'' FROM
( SELECT [RID],[Cnt] = COUNT(INV)
FROM '+@UniTab+'
WHERE [diag] IN(''3450'',''34500'',''34501'',''3451'',''34510'',''34511'',''3452'',''3453'',''3454'',''34540'',''34541'',''3455'',''34550'',''34551'',''3456'',''34560'',''34561'',''3457'',''34571'',''3458'',''34580'',''34581'',''3459'')
AND (INV=''01'') GROUP BY [RID] HAVING COUNT(*)>=1
UNION
SELECT [RID],[Cnt] = COUNT([HCPC_NDC])
FROM '+@UniTab+'
WHERE [DIAG] IN (''3450'',''34500'',''34501'',''3451'',''34510'',''34511'',''3452'',''3453'',''3454'',''34540'',''34541'',''3455'',''34550'',''34551'',''3456'',''34560'',''34561'',''3457'',''34571'',''3458'',''34580'',''34581'',''3459'')
AND [HCPC_NDC] IN (''99281'',''99282'',''99283'',''99284'',''99285'')
GROUP BY [RID]
HAVING COUNT([HCPC_NDC]) >= 3
)A GROUP BY [RID]
UNION ALL
SELECT [RID], DIS =''P4'' FROM
( SELECT [RID]
FROM '+@UniTab+' R7
INNER JOIN [dbo].[TABA1] T1
ON R7.[DIAG]=T1.[ICDCOD]
WHERE (T1.[Per Se Disabling]=''YES'' or T1.[Activity limiting condition]=''YES'')
AND R7.[INV] IN(''01'' ,''03'')
GROUP BY [RID]
UNION
SELECT [RID]
FROM '+@UniTab+' R7
JOIN [dbo].[TABA1] T1
ON R7.[DIAG]=T1.[ICDCOD]
WHERE (T1.[Per Se Disabling]=''YES'' or T1.[Activity limiting condition]=''YES'')
AND [HCPC_NDC] IN (''99281'',''99282'',''99283'',''99284'',''99285'')
GROUP BY [RID]
HAVING COUNT([HCPC_NDC]) >= 3
)A GROUP BY [RID]
UNION ALL
SELECT [RID], DIS =''M1'' FROM
( SELECT [RID] FROM
( SELECT [RID]
FROM '+@UniTab+' R7
WHERE diag IN (''2962'',''2963'')
UNION ALL
SELECT [RID]
FROM '+@UniTab+' R7
WHERE diag IN(''300'',''3000'',''30000'',''30001'',''30002'',''30009'',''3001'',''30010'',''30011'',''30012'',''30013'',''30014'',''30015'',''30016'',''30019'',''3002'',''30020'',''30021'',''30022'',''30023'',''30029'',''3003'')
)D GROUP BY [RID]
HAVING COUNT(*)>1
UNION
SELECT [RID] FROM
( SELECT [RID]
FROM '+@UniTab+' R7
JOIN [dbo].[754sadqd] T1
ON R7.[DIAG]=T1.
UNION ALL
SELECT [RID]
FROM '+@UniTab+' R7
WHERE R7.[DIAG]=''3001''
)C GROUP BY [RID]
HAVING COUNT(*)>1
)A GROUP BY [RID]
UNION ALL
SELECT [RID], DIS =''M2'' FROM
(SELECT [RID]
FROM R7541f3 R7
WHERE DIAG IN (''2962'',''2963'')
AND ((inv=''01'' AND DATEDIFF(DAY,CONVERT(DATETIME, from_dos, 12),CONVERT(DATETIME, thru_dos, 12))>=30)
OR (DATEDIFF(YEAR,CONVERT(DATETIME, from_dos, 12),CONVERT(DATETIME, GETDATE(), 12))<=2 ) OR hcpc_ndc=''90870'')
)A GROUP BY [RID]
UNION ALL
SELECT [RID], DIS = ''M3'' FROM
( SELECT [RID]
FROM R7541f3 R7
WHERE DIAG=''3001''
AND ((inv=''01''AND DATEDIFF(DAY,CONVERT(DATETIME, from_dos, 12),CONVERT(DATETIME, thru_dos, 12))>=30)
OR DESCRIPTION IN(''AZILECT'',''ELDEPRYL'',''EMSAM'',''MARPLAN'',''NARDIL'',''PARNATE'',''ZELAPAR''))
)A GROUP BY [RID]
UNION ALL
SELECT [RID], DIS = ''M4'' FROM
( SELECT [RID]
FROM '+@UniTab+' R7
WHERE DIAG=''3003''
AND((inv=''01''AND DATEDIFF(DAY,CONVERT(DATETIME, from_dos, 12),CONVERT(DATETIME, thru_dos, 12))>=30)
OR DESCRIPTION IN (''TEGRATOL'',''CLOZAPINE'',''DILANTIN'',''AZILECT'',''ELDEPRYL'',''EMSAM'',''MARPLAN'',''NARDIL'',''PARNATE'',''ZELAPAR''))
)A GROUP BY [RID]
UNION ALL
SELECT [RID], DIS =''M5'' FROM
( SELECT [RID]
FROM '+@UniTab+' R7
INNER JOIN [dbo].[TAB3A] T1
ON R7.[DIAG]=T1.[ICDCODE]
WHERE inv=''01'' AND DATEDIFF(DAY,CONVERT(DATETIME, from_dos, 12),CONVERT(DATETIME, thru_dos, 12))>=30
UNION
SELECT [RID]
FROM '+@UniTab+' R7
JOIN [dbo].[TAB3A] T1
ON R7.[DIAG]=T1.[ICDCODE]
WHERE INV=''03''
GROUP BY [RID] HAVING SUM(paid) >= 5000
)A GROUP BY [RID]'
EXEC sp_executesql @sSQL
March 24, 2009 at 8:48 am
ok there goes the concat solution, as you can't concat with sp_executeSQL
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 24, 2009 at 8:49 am
I thought your problem was in SQL 2000? as there is no VARCHAR(MAX) in 2000
ALSO if you USE sp_executeSQL you must use a NVARCHAR
I'll work on a solution now
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 24, 2009 at 8:51 am
If I change the VARCHAR to NVARCHAR and run the code there are no problems?
can you confirm?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply