469,625 Members | 1,080 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Hai ,plz help me

i have a query
create PROCEDURE spListSMSLogAndQueue
@intSort int,
@intDir int,
@strSearch nVarchar(100) = ''

AS
SET NOCOUNT ON
declare @strSql nvarchar(2000)
declare @strSort nvarchar(50)
declare @strDir nvarchar(50)
declare @strCondition nvarchar(500)
Begin
Set @strDir =
Case @intDir
When 1 Then ' desc'
Else ''
End
Set @strSort =
Case @intSort
When 1 Then ' a.SenderID'
When 2 Then ' a.DateAndTime'
When 3 Then ' intCount'
Else ' a.SMSID'
End

if @strSearch <> ''
set @strCondition = ' where and (b.UID like N''%' + @strSearch + '%'' or a.DateAndTime like ''%' + @strSearch + '%'')'
else
set @strCondition = ''

Begin
set @strSql = 'select a.SMSID,a.SenderID,convert(nvarchar(500),a.Message ) as Message,a.DateAndTime,b.UID as Sender,count(c.SMSID) as intCount
from SMS a
left outer join [User] b on a.SenderID = b.UserID
left outer join SMSStudEmpMatch c on a.SMSID = c.SMSID
left outer join Student d on c.StudentID = d.StudentID
' + @strCondition +
' group by a.SMSID,a.SenderID,convert(nvarchar(500),a.Message ),a.DateAndTime,b.UID
order by ' + @strSort + @strDir

exec(@strSql)
End
Begin
set @strSql = 'select p.SMSID,isnull(q.Student,'''') as Recep,p.StudentID as RecepID
from SMSStudEmpMatch p
left outer join Student q on p.StudentID = q.StudentID
union
select p.SMSID,isnull(q.UID,'''') as Recep,p.EmployeeID as RecepID
from SMSStudEmpMatch p
left outer join [User] q on p.EmployeeID = q.UserID'

exec(@strSql)
End




End


GO


there is a coloum in in the second part named recep
i want to get tht values of tht colum in the first part divived by commas how can i get it plz help me
Jul 16 '07 #1
1 930
Vidhura
99
i have a query
create PROCEDURE spListSMSLogAndQueue
@intSort int,
@intDir int,
@strSearch nVarchar(100) = ''

AS
SET NOCOUNT ON
declare @strSql nvarchar(2000)
declare @strSort nvarchar(50)
declare @strDir nvarchar(50)
declare @strCondition nvarchar(500)
Begin
Set @strDir =
Case @intDir
When 1 Then ' desc'
Else ''
End
Set @strSort =
Case @intSort
When 1 Then ' a.SenderID'
When 2 Then ' a.DateAndTime'
When 3 Then ' intCount'
Else ' a.SMSID'
End

if @strSearch <> ''
set @strCondition = ' where and (b.UID like N''%' + @strSearch + '%'' or a.DateAndTime like ''%' + @strSearch + '%'')'
else
set @strCondition = ''

Begin
set @strSql = 'select a.SMSID,a.SenderID,convert(nvarchar(500),a.Message ) as Message,a.DateAndTime,b.UID as Sender,count(c.SMSID) as intCount
from SMS a
left outer join [User] b on a.SenderID = b.UserID
left outer join SMSStudEmpMatch c on a.SMSID = c.SMSID
left outer join Student d on c.StudentID = d.StudentID
' + @strCondition +
' group by a.SMSID,a.SenderID,convert(nvarchar(500),a.Message ),a.DateAndTime,b.UID
order by ' + @strSort + @strDir

exec(@strSql)
End
Begin
set @strSql = 'select p.SMSID,isnull(q.Student,'''') as Recep,p.StudentID as RecepID
from SMSStudEmpMatch p
left outer join Student q on p.StudentID = q.StudentID
union
select p.SMSID,isnull(q.UID,'''') as Recep,p.EmployeeID as RecepID
from SMSStudEmpMatch p
left outer join [User] q on p.EmployeeID = q.UserID'

exec(@strSql)
End




End


GO


there is a coloum in in the second part named recep
i want to get tht values of tht colum in the first part divived by commas how can i get it plz help me
Sample:

Declare @val Varchar(2000)
select @val=Coalesce(@val , '') + convert(varchar,ID) + ',' from tEmployee
print @val
Jul 16 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by William C. White | last post: by
2 posts views Thread by Albert Ahtenberg | last post: by
3 posts views Thread by James | last post: by
reply views Thread by Ollivier Robert | last post: by
1 post views Thread by Richard Galli | last post: by
4 posts views Thread by Albert Ahtenberg | last post: by
1 post views Thread by inderjit S Gabrie | last post: by
2 posts views Thread by Jack | last post: by
3 posts views Thread by Sandwick | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.