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 2005 Forums
 Transact-SQL (2005)
 Stored proc generating a bunch of errors

Author  Topic 

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2009-04-13 : 21:21:30
Dear Experts,

I need a set of brains, eyes, and most importantly, patience.

I have not done stored in a long time and attempting to write this dynamic stored proc is generating all kinds of errors and I am really stumped trying to fix it.

Any help is greatly, greatly appreciated.

Here are the errors, then the code I am working on.

Msg 156, Level 15, State 1, Procedure SP_RECID, Line 3
Incorrect syntax near the keyword 'in'.
Msg 137, Level 15, State 2, Procedure SP_RECID, Line 30
Must declare the scalar variable "@wherestring1".
Msg 137, Level 15, State 2, Procedure SP_RECID, Line 31
Must declare the scalar variable "@WhereString3".
Msg 102, Level 15, State 1, Procedure SP_RECID, Line 56
Incorrect syntax near 'Column1'.
Msg 102, Level 15, State 1, Procedure SP_RECID, Line 57
Incorrect syntax near ' + WhereString11 + '.
Msg 102, Level 15, State 1, Procedure SP_RECID, Line 58
Incorrect syntax near ' + WhereString11 + '.
Msg 102, Level 15, State 1, Procedure SP_RECID, Line 59
Incorrect syntax near ' + WhereString11 + '.
Msg 102, Level 15, State 1, Procedure SP_RECID, Line 60
Incorrect syntax near ' + WhereString11 + '.
Msg 102, Level 15, State 1, Procedure SP_RECID, Line 61
Incorrect syntax near ' + WhereString11 + '.
Msg 102, Level 15, State 1, Procedure SP_RECID, Line 62
Incorrect syntax near ' + WhereString33 + '.
Msg 105, Level 15, State 1, Procedure SP_RECID, Line 62
Unclosed quotation mark after the character string ')



CREATE PROCEDURE "SP_RECID"
(
@Status in varchar,
@StatusType in varchar,
@WhereString1 in varchar,
@WhereString3 in varchar,
@Column1 in varchar,
@Column2 in varchar,
Return_Cursor IN OUT RECIDIVISMPACK.CURSOR_TYPE
)
As
DECLARE @WhereString2 varchar(max)
DECLARE @WhereString11 varchar(max)
DECLARE @WhereString33 varchar(max)
DECLARE @DateString varchar2(10)
DECLARE @sqlstring varchar2(1000)
DECLARE @Status1 varchar2(15)
DECLARE @StatusType1 varchar2(15)
DECLARE @Column11 varchar2(20)
DECLARE @Column22 varchar2(20)
DECLARE @WhereString30 varchar(max)

BEGIN
--Status := 'OPEN'
--StatusType := 'SUCCESSFUL'
--Column1 := 'Violence'
--Column2 := 'Drugs'
--WhereString3 := ' and Upper(mn.Gender) = #MALE# and sa.FirstUseDrug = #Heroin# '

set @whereString11 = replace(@wherestring1, '#','''')
set @WhereString33 = replace(@WhereString3,'#','''')
set @WhereString2 = ''
If Upper(Status) = 'OPEN'
begin
set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''OPEN'' '
end
else if Upper(Status) = 'CLOSED'
begin
-- WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' '
if Upper(StatusType) = 'SUCCESSFUL'
begin
set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' and Upper(c.Close_FinalResult) in (''GRADUATED'', ''SUCCESS'', ''COMPLETED'') '
end
else if Upper(StatusType) = 'FAILURE'
begin
set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED''and Upper(c.Close_FinalResult) in (''FAILURE'', ''FTA'', ''REVOKED'', ''TERMINATED'', ''FAILED OTHER'') '
end
else if Upper(StatusType) = 'REMOVED'
begin
set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' and Upper(c.Close_FinalResult) in (''DISMISSED'', ''DROPPED'', ''REMOVED'', ''TRANSFERRED'', ''WITHDREW'') '
end
end
--WhereString11 = ' and Upper(mn.Gender) = ''MALE'' and sa.FirstUseDrug = ''Heroin'' and cas.ArrestDate > c.Setup_SupervisionStart and cas.ArrestDate > c.Setup_SupervisionStart '
--WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' and Upper(c.Close_FinalResult) in (''DISMISSED'', ''DROPPED'', ''REMOVED'', ''TRANSFERRED'', ''WITHDREW'') '

exec (' Select ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column1 + ' and UPPER(CAS.CaseSeverity) = 'M' + @WhereString11 + ' ' + @WhereString2 + ' ) ,0) AS Viol_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column1 + ' and UPPER(CAS.CaseSeverity) = 'F' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Viol_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column2 + ' and UPPER(CAS.CaseSeverity) = 'M' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Drug_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column2 + ' and UPPER(CAS.CaseSeverity) = 'F' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Drug_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (' + Column1 + ',' + Column2 + ') and UPPER(CAS.CaseSeverity) = 'M' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Other_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (' + Column1 + ',' + Column2 + ') and UPPER(CAS.CaseSeverity) = 'F' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Other_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID ' + WhereString33 + ' ' + WhereString2 + ') ,0) AS Total_Clients ' + ' FROM tbl_SYS')


END SP_RECID

hambalang
Starting Member

4 Posts
Posted - 2009-04-13 : 22:34:33
The syntax of create store procedure does not have the word "in".
CREATE PROCEDURE "SP_RECID"
(
@Status varchar,
@StatusType varchar,
@WhereString1 varchar,
@WhereString3 varchar,
@Column1 varchar,
@Column2 varchar,
...
)
When fix this error, some errors will also be fixed.
Go to Top of Page

hambalang
Starting Member

4 Posts
Posted - 2009-04-13 : 23:04:42
With error "Msg 102, Level 15, State 1, Procedure SP_RECID, Line 56
Incorrect syntax near 'Column1'."

In this statement,
exec (' Select ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column1 + ' and UPPER(CAS.CaseSeverity) = 'M' + @WhereString11 + ' ' + @WhereString2 + ' ) ,0) AS Viol_Misd, ' +

You do not have parameter "Column1" but "@Column1". Check it again.
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2009-04-14 : 00:31:34
hi Thanks a lot for your responses.

I tried both but they didn't stop the error.
Go to Top of Page

thiyagu_rind
Starting Member

46 Posts
Posted - 2009-04-14 : 01:30:41
Hey,

i hope you missed the "@"

exec (' Select ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column1 + ' and UPPER(CAS.CaseSeverity) = 'M' + @WhereString11 + ' ' + @WhereString2 + ' ) ,0) AS Viol_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column1 + ' and UPPER(CAS.CaseSeverity) = 'F' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Viol_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column2 + ' and UPPER(CAS.CaseSeverity) = 'M' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Drug_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + Column2 + ' and UPPER(CAS.CaseSeverity) = 'F' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Drug_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (' + Column1 + ',' + Column2 + ') and UPPER(CAS.CaseSeverity) = 'M' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Other_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (' + Column1 + ',' + Column2 + ') and UPPER(CAS.CaseSeverity) = 'F' ' + WhereString11 + ' ' + WhereString2 + ') ,0) AS Other_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID ' + WhereString33 + ' ' + WhereString2 + ') ,0) AS Total_Clients ' + ' FROM tbl_SYS')

Can you try this

Regards
Thiyagarajan
Go to Top of Page

Kokkula
Starting Member

41 Posts
Posted - 2009-04-14 : 01:58:27
Hello,

Try this

CREATE PROCEDURE [SP_RECID]
(
@Status varchar,
@StatusType varchar,
@WhereString1 varchar,
@WhereString3 varchar,
@Column1 varchar,
@Column2 varchar
)
As
DECLARE @WhereString2 varchar(max)
DECLARE @WhereString11 varchar(max)
DECLARE @WhereString33 varchar(max)
DECLARE @DateString varchar2(10)
DECLARE @sqlstring varchar2(1000)
DECLARE @Status1 varchar2(15)
DECLARE @StatusType1 varchar2(15)
DECLARE @Column11 varchar2(20)
DECLARE @Column22 varchar2(20)
DECLARE @WhereString30 varchar(max)

BEGIN
--Status := 'OPEN'
--StatusType := 'SUCCESSFUL'
--Column1 := 'Violence'
--Column2 := 'Drugs'
--WhereString3 := ' and Upper(mn.Gender) = #MALE# and sa.FirstUseDrug = #Heroin# '

set @whereString11 = replace(@wherestring1, '#','''')
set @WhereString33 = replace(@WhereString3,'#','''')
set @WhereString2 = ''
If Upper(Status) = 'OPEN'
begin
set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''OPEN'' '
end
else if Upper(Status) = 'CLOSED'
begin
-- WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' '
if Upper(StatusType) = 'SUCCESSFUL'
begin
set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' and Upper(c.Close_FinalResult) in (''GRADUATED'', ''SUCCESS'', ''COMPLETED'') '
end
else if Upper(StatusType) = 'FAILURE'
begin
set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED''and Upper(c.Close_FinalResult) in (''FAILURE'', ''FTA'', ''REVOKED'', ''TERMINATED'', ''FAILED OTHER'') '
end
else if Upper(StatusType) = 'REMOVED'
begin
set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' and Upper(c.Close_FinalResult) in (''DISMISSED'', ''DROPPED'', ''REMOVED'', ''TRANSFERRED'', ''WITHDREW'') '
end
end
--WhereString11 = ' and Upper(mn.Gender) = ''MALE'' and sa.FirstUseDrug = ''Heroin'' and cas.ArrestDate > c.Setup_SupervisionStart and cas.ArrestDate > c.Setup_SupervisionStart '
--WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' and Upper(c.Close_FinalResult) in (''DISMISSED'', ''DROPPED'', ''REMOVED'', ''TRANSFERRED'', ''WITHDREW'') '

exec (' Select ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + @Column1 + ' and UPPER(CAS.CaseSeverity) = ''M''' + @WhereString11 + ' ' + @WhereString2 + ' ) ,0) AS Viol_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + @Column1 + ' and UPPER(CAS.CaseSeverity) = ''F'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Viol_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + @Column2 + ' and UPPER(CAS.CaseSeverity) = ''M'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Drug_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + @Column2 + ' and UPPER(CAS.CaseSeverity) = ''F'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Drug_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (' + @Column1 + ',' + @Column2 + ') and UPPER(CAS.CaseSeverity) = ''M'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Other_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (' + @Column1 + ',' + @Column2 + ') and UPPER(CAS.CaseSeverity) = ''F'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Other_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID ' + @WhereString33 + ' ' + @WhereString2 + ') ,0) AS Total_Clients ' + ' FROM tbl_SYS')


END

--SP_RECID

Hope helpful...


Thanks,
Pavan
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-04-14 : 02:36:14
Well, VARCHAR2 is not a Microsoft SQL Server datatype. It seems you are working with ORACLE.
Nevertheless, here is a Microsoft SQL approach.
CREATE PROCEDURE SP_RECID
(
	@Status varchar(max),
	@StatusType varchar(max),
	@WhereString1 varchar(max),
	@WhereString3 varchar(max),
	@Column1 varchar(max),
	@Column2 varchar(max)
)
As

set nocount on

DECLARE @WhereString2 varchar(max)
DECLARE @WhereString11 varchar(max)
DECLARE @WhereString33 varchar(max)
DECLARE @DateString nvarchar(10)
DECLARE @sqlstring nvarchar(max)
DECLARE @Status1 nvarchar(15)
DECLARE @StatusType1 nvarchar(15)
DECLARE @Column11 nvarchar(20)
DECLARE @Column22 nvarchar(20)
DECLARE @WhereString30 varchar(max)

BEGIN 
--Status := 'OPEN'
--StatusType := 'SUCCESSFUL'
--Column1 := 'Violence'
--Column2 := 'Drugs'
--WhereString3 := ' and Upper(mn.Gender) = #MALE# and sa.FirstUseDrug = #Heroin# '

set @whereString11 = replace(@wherestring1, '#', '''')
set @WhereString33 = replace(@WhereString3,'#', '''')
set @WhereString2 = ''

If Upper(Status) = 'OPEN' 
	set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''OPEN'' '
else
	if Upper(Status) = 'CLOSED' 
		begin
			if Upper(StatusType) = 'SUCCESSFUL' 
				set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' and Upper(c.Close_FinalResult) in (''GRADUATED'', ''SUCCESS'', ''COMPLETED'') '
			else
				if Upper(StatusType) = 'FAILURE' 
					set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED''and Upper(c.Close_FinalResult) in (''FAILURE'', ''FTA'', ''REVOKED'', ''TERMINATED'', ''FAILED OTHER'') '
				else
					if Upper(StatusType) = 'REMOVED' 
						set @WhereString2 = ' and Upper(c.EpisodeStatus) = ''CLOSED'' and Upper(c.Close_FinalResult) in (''DISMISSED'', ''DROPPED'', ''REMOVED'', ''TRANSFERRED'', ''WITHDREW'') '
		end

set	@sqlstring = 'Select ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + @Column1 + ' and UPPER(CAS.CaseSeverity) = ''M''' + @WhereString11 + ' ' + @WhereString2 + ' ) ,0) AS Viol_Misd, ' + 
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + @Column1 + ' and UPPER(CAS.CaseSeverity) = ''F'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Viol_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + @Column2 + ' and UPPER(CAS.CaseSeverity) = ''M'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Drug_Misd, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ' + @Column2 + ' and UPPER(CAS.CaseSeverity) = ''F'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Drug_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (' + @Column1 + ',' + @Column2 + ') and UPPER(CAS.CaseSeverity) = ''M'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Other_Misd, ' + 
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (' + @Column1 + ',' + @Column2 + ') and UPPER(CAS.CaseSeverity) = ''F'' ' + @WhereString11 + ' ' + @WhereString2 + ') ,0) AS Other_Felony, ' +
' ISNULL((Select Count(*) from tblU_MName MN, tblU_DCEPISODE c, tblB_SCA sa, tblCases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID ' + @WhereString33 + ' ' + @WhereString2 + ') ,0) AS Total_Clients ' + ' FROM tbl_SYS'

print @sqlstring
exec(@sqlstring)
GO


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

simflex
Constraint Violating Yak Guru

327 Posts
Posted - 2009-04-14 : 11:10:42
Thank you so much, all of you for your kindness.

The reason I had not been back here was I was trying fix the ones I had been working with.

Fortunately, yours is better and it compiled successfully.

However, here is the error I am getting when I attempted to execute the stored proc:

Select ISNULL((Select Count(*) from tblU_MNAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = tttttt and UPPER(CAS.CaseSeverity) = 'M'tttt ) ,0) AS Viol_Misd, ISNULL((Select Count(*) from tblU_MNAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = tttttt and UPPER(CAS.CaseSeverity) = 'F' tttt ) ,0) AS Viol_Felony, ISNULL((Select Count(*) from tblU_MNAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ttttttt and UPPER(CAS.CaseSeverity) = 'M' tttt ) ,0) AS Drug_Misd, ISNULL((Select Count(*) from tblU_MAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = ttttttt and UPPER(CAS.CaseSeverity) = 'F' tttt ) ,0) AS Drug_Felony, ISNULL((Select Count(*) from tblU_MNAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (tttttt,ttttttt) and UPPER(CAS.CaseSeverity) = 'M' tttt ) ,0) AS Other_Misd, ISNULL((Select Count(*) from tblU_MNAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) not in (tttttt,ttttttt) and UPPER(CAS.CaseSeverity) = 'F' tttt ) ,0) AS Other_Felony, ISNULL((Select Count(*) from tblU_MNAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID ttttt ) ,0) AS Total_Clients FROM tbl_SYS
Msg 203, Level 16, State 2, Procedure SP_RECID, Line 60
The name 'Select ISNULL((Select Count(*) from tblU_MNAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = tttttt and UPPER(CAS.CaseSeverity) = 'M'tttt ) ,0) AS Viol_Misd, ISNULL((Select Count(*) from tblU_MNAME MN, tblU_DCEPISODE c, tblB_SCA sa, tbl_Cases CAS Where mn.ChsID = c.ChsID and c.ChsID = CAS.ChsID and c.EpsID = CAS.TREpsID and C.ChsID = sa.ChsID and c.EpsID = sa.EpsID and UPPER(CAS.CaseType) = tttttt and UPPER(CAS.CaseSeverity) = 'F' tttt ) ,0) AS Viol_Felony, ISNULL((S' is not a valid identifier.


Again, many thanks for your assistance.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site