How do i write this Stored Procedure.

Question

How do i write this Stored Procedure.

Abhay Jadhav

Mr or Mrs. 500

Points: 556
More actions
May 14, 2007 at 12:13 am

#178508

hello guys,
i have one SP. which is building dynamically now. its goes like this.
ALTER
PROCEDURE [dbo].[q_GetInboxForReader]
@service
varchar(50) = null,
@PatOrd
varchar(5),
@StTyp
varchar(5),
@PtNmDt
varchar(5),
@DtOrd
varchar(5),
@ServOrd
varchar(15),
@TypInfo
varchar(15),
@SubResult
varchar(15)
AS
declare
@sSql nvarchar(1024)
set
nocount on
set
transaction isolation level read committed
set @service = ltrim(rtrim(lower(IsNull(@service,''))))
set
@sSql='SELECT ' +
'RI.patientId, ' +
'(dbo.qf_makeStringReadable(patients.LastName) + '','' + dbo.qf_makeStringReadable(patients.FirstName)) AS PatientName, ' +
'RI.studyId, ' +
'RI.studyType, ' +
'RI.inboxDate, ' +
'RI.service, ' +
'RI.signDate, ' +
'vRI.reportId, ' +
'vRI.signDate as reportSignedDate, ' +
'vRI.signerUserId, ' +
'physicians.digitalSignature, ' +
'vRI.signedMD5Signature, ' +
'vRI.submissionDate, ' +
'vRI.submissionResult, ' +
'vRI.xmlLength, ' +
'vRI.blobLength ' +
'from readerInbox RI '
+
'left outer join ' +
'( ' +
'v_ReaderInboxList vRI inner join physicians on VRI.signerUserId = physicians.ntUserName ' +
') ' +
'on ' +
'vRI.patientId = RI.patientId AND ' +
'vRI.studyId = RI.studyId AND ' +
'vRI.studyType = RI.studyType ' +
'inner join patients on ' +
'patients.patientId = RI.patientId ' +
'where '
+
'inboxQueName= ''all'' OR ' +
'RI.service ='''+ @service + ''''

if
(@TypInfo='tcd')
Begin
set @sSql=@sSql + ' and RI.studyType = ''TCD'''
end
else
if(@TypInfo='xenon')
Begin
set @sSql=@sSql + ' and RI.studyType = ''xenon'''
end
else
if(@TypInfo='sicklecell')
Begin
set @sSql=@sSql + ' and RI.studyType = ''sickleCell'''
end
else
if(@TypInfo='flowguard')
Begin
set @sSql=@sSql + ' and RI.studyType = ''flowguard'''
end
now the paramters @PatOrd @StTyp @PtNmDt @DtOrd @ServOrd @TypInfo
these all parameters passes the values like ascending and descding to SP. now i want to write append those values to the current query. means if the @patOrd is having ASC as parameter. then the query which is allready formed should be concatenated with additional string like order by patientid if it is desc. then order by patientid as desc. like wise for all the parameters. i would like to do.
how should i do this i am getting confused. i dont want to write if ... else loop for all the parameters as it would take lot of conditions for it. is there any other way to do this out.

Thx
Abhay

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

the only other way is to do the sorting in your application otherwise there is no other way to do it than by [if...else]or [select case]

Everything you can imagine is real.

Abhay Jadhav Mr or Mrs. 500 Points: 556 More actions · Answer 2

How can we do it by Select case?? will u plz explain me

bledu SSChampion Points: 12335 More actions · Answer 3

oops too many programming languages. its actually the CASE statement

check in BOL on usage but its pretty much the same as an IF...ELSE..

Everything you can imagine is real.