problem with sqlquery - 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

SQL Server 2000 Forums

Transact-SQL (2000)

problem with sqlquery

Author

Topic

umapathy
Starting Member

24 Posts

Posted - 2007-10-03 : 03:05:50

ALTER procedure USp_GetEmployeeInformation_forRoles
(
@InRoleID int,
@vcEmpSubGroup VARCHAR(1000),--=null,
@bussinessUnit int,
@Location int,
@vcPayRollAreaCode VARCHAR(100),--=null,
@WorkSchedule VARCHAR(100)--=NULL
)
as
begin
declare @bu bit
declare @band bit
declare @loc bit
declare @pa bit
declare @ws bit
declare @strsql nvarchar(3000)
declare @strsql2 nvarchar(4000)
declare @strsql3 nvarchar(2000)
declare @strsql4 nvarchar(3000)
declare @strsql5 nvarchar(3000)

set @strsql2= 'select distinct dbo.TblMst_Employee.nmEmployeeCode
,dbo.TblMst_Employee.vcFirstName FROM
dbo.TblMst_Employee INNER JOIN
dbo.TblTrn_Employee_Roles ON
dbo.TblMst_Employee.nmEmployeeCode = dbo.TblTrn_Employee_Roles.nmEmployeeCode
WHERE dbo.TblTrn_Employee_Roles.boEnabled=1 and dbo.TblTrn_Employee_Roles.inRoleId= '+convert(varchar(9),@InRoleID)

select @bu=boBUWise
,@band=boBandWise
,@loc=boLocationWise
,@pa=boParollAreaWise
,@ws=boWorkScheduleWise from TblMst_User_Role
where inRoleId=@InRoleID

if @bu=1 --and (@bussinessUnit !=null)
begin
set @strsql2=@strsql2+ ' and TblTrn_Employee_Roles.vcL3_Code= '+convert(varchar(9),@bussinessUnit)
end
if @band=1 and @vcEmpSubGroup <> ''
begin
set @strsql2=@strsql2+ ' and dbo.TblTrn_Employee_Roles.vcEmpSubGroup='''+ @vcEmpSubGroup+''''
end

if @loc=1
begin
set @strsql2=@strsql2+ ' and dbo.TblTrn_Employee_Roles.vcLoc_Code= '+convert(varchar(9),@Location)
end

if @pa=1 and @vcPayrollAreaCode <> ''
begin
set @strsql2=@strsql2+ ' and dbo.TblTrn_Employee_Roles.vcPayrollAreaCode='''+ @vcPayrollAreaCode+''''
end
if @ws=1 and @WorkSchedule <> ''
begin
set @strsql2=@strsql2+ ' and dbo.TblTrn_Employee_Roles.WorkSchedule='''+ @WorkSchedule+'''' --+convert(varchar(9),@WorkSchedule)
end
exec sp_executesql @strsql2
end

EXEC USp_GetEmployeeInformation_forRoles 2,'e1',null,null,'02',null

i want to get this output.i got it as well as
exec USp_GetEmployeeInformation_forRoles 2,'e1',null,null,null,null
this is also i got output this is not required

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 04:44:25

You don't need to use Dynamic SQL for this Sproc - as best as I can see it.

Its much harder to debug, reduces performance, and requires additional user permissions to the table involved

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 05:29:21

http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources