How do i write this Stored Procedure.

Question

Post reply

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