SPs - 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 2005 Forums

Transact-SQL (2005)

SPs

Author

Topic

charul
Starting Member

18 Posts

Posted - 2008-08-08 : 08:56:04

i have a SP Like this

-- Create date: 08-08-2008
-- Description: TO FETCH THE EXITSING COUNT
-- =======================================================
ALTER Procedure [dbo].[Proc_ExistingCount_CreateString]
(
@strOrg1 as varchar(10),
@strOrg2 as varchar(10),
@strOrg3 as varchar(10),
@strOrg4 as varchar(10),
@strOrg5 as varchar(10),
@strOrg6 as varchar(10),
@strOrg7 as varchar(10),
@strOrg8 as varchar(10),
@strStatus as varchar(100)
)

AS
BEGIN

Declare @strSql as varchar(8000)
Declare @strDeclare as varchar(100)
Declare @strPrint as varchar(100)

---Exec Proc_ExistingCount_CreateString 'X','X','002','024','042','X','X','X','1,2'

Set @strStatus= REPLACE(@strStatus,',',''',''')

Set @strSql='Select count(*) from ReqRec_Employeedetails,
Setup_ORGEmployeeMaster where ED_EmpCode=ORGEM_EmpCode and Ed_Status IN
('''+ @strStatus+''')'

IF NOT @strOrg1='X'
BEGIN
set @strSql = @strSql + 'AND ORGEM_ORGLEVEL1ID IN (''' + @strOrg1 + ''')'
END
IF NOT @strOrg2='X'
BEGIN
set @strSql = @strSql + 'AND ORGEM_ORGLEVEL2ID IN (''' + @strOrg2 + ''')'
END
IF NOT @strOrg3='X'
BEGIN
set @strSql = @strSql + 'AND ORGEM_ORGLEVEL3ID IN (''' + @strOrg3 + ''')'
END
IF NOT @strOrg4='X'
BEGIN
set @strSql = @strSql + 'AND ORGEM_ORGLEVEL4ID IN (''' + @strOrg4 + ''')'
END
IF NOT @strOrg5='X'
BEGIN
set @strSql = @strSql + 'AND ORGEM_ORGLEVEL5ID IN (''' + @strOrg5 + ''')'
END
IF NOT @strOrg6='X'
BEGIN
set @strSql = @strSql + 'AND ORGEM_ORGLEVEL6ID IN (''' + @strOrg6 + ''')'
END
IF NOT @strOrg7='X'
BEGIN
set @strSql = @strSql + 'AND ORGEM_ORGLEVEL7ID IN (''' + @strOrg7 + ''')'
END
IF NOT @strOrg8='X'
BEGIN
set @strSql = @strSql + 'AND ORGEM_ORGLEVEL8ID IN (''' + @strOrg8 + ''')'
END
set @strPrint='Select @ExistingCount'

exec(@strSql)

How to do i return the value to a different SP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-08 : 08:59:31

[code]declare @c table
(
cnt int
)

insert into @c exec Proc_ExistingCount_CreateString 'X','X','002','024','042','X','X','X','1,2'[/code]

OR

use OUTPUT parameter and pass the value out

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources