Stored Procedure help

• Lucky9

  Default port

  Points: 1470

  More actions

  January 22, 2011 at 6:07 pm

  #232616

  I have a table with 2 columns First column has the reqid and second column has the Error Messages

  for related request id

  Create Table ErrorList

  (reqid int,

  Err-Message nvarchar(200))

  Insert into ErrorList values(1,'AB')

  Insert into ErrorList values(1,'CD')

  Insert into ErrorList values(1,'DE')

  Insert into ErrorList values(1,'FG')

  Insert into ErrorList values(2,'PP')

  Insert into ErrorList values(2,'VV')

  Insert into ErrorList values(2,'LL')

  Insert into ErrorList values(2,'RR')

  Insert into ErrorList values(3,'AR')

  Insert into ErrorList values(3,'VR')

  Insert into ErrorList values(3,'LR')

  I have to write a stored Procedure were it as one Input parameter and one output parameter

  Input Parameter should be like v_reqid int

  Output Parameter should return like a string

  So if i pass a reqid value as input parameter then the output be in the form of a string with all the Err-Messages

  related to that reqid with commas

  For Example: if we pass Input parameter value as 1 then the output parameter should return it as 'AB','CD','DE','FG'

  If we pass input parameter value as 2 then the output parameter should return it as 'PP','VV','LL','RR'

  If we pass Input parameter value as 3 then the output parameter should return it as 'AR','VR','LR'

  Could someone please help me in writing a stored Procedure

• Vedran Kesegic

  SSCrazy Eights

  Points: 9302

  More actions

  January 23, 2011 at 12:43 am

  #1276954

  create procedure ErrorListGet

  -- Returns comma-separated list of errors for given request

  (@reqid int,

  @Messages varchar(max) out

  ) as

  begin

  -- Bild comma-separated list of messages

  -- If you want some particular order, simply add "order by" clause

  set @Messages = ''

  select @Messages = @Messages + ',' + e.ErrMessage

  from ErrorList e

  where e.reqid = @reqid

  -- Remove leading comma

  set @Messages = SUBSTRING( @Messages, 2, 1000000 )

  end

  go

  -- test

  declare @msg varchar(max)

  exec ErrorListGet 1, @msg out

  print @msg

  exec ErrorListGet 2, @msg out

  print @msg

  exec ErrorListGet 3, @msg out

  print @msg

  Result:

  AB,CD,DE,FG

  PP,VV,LL,RR

  AR,VR,LR

  _____________________________________________________
  Microsoft Certified Master: SQL Server 2008
  XDetails Addin - for SQL Developers
  blog.sqlxdetails.com - Transaction log myths
  

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 23, 2011 at 2:45 pm

  #1277003

  Depending on what you're really doing, the may be no need here for a separate stored procedure nor even a UDF...

  SELECT t1.ReqID,

  ErrorListCSV =

  STUFF(CAST(

  (

  SELECT ',' + t2.[Err-Message]

  FROM dbo.ErrorList t2

  WHERE t2.ReqID = t1.ReqID

  ORDER BY t2.[Err-Message]

  FOR XML PATH('')

  )

  AS VARCHAR(MAX)),1,1,'')

  FROM dbo.ErrorList t1

  GROUP BY t1.ReqID

  ORDER BY t1.ReqID

  Of course, you could convert the above into a high performance inline Table Valued Function (iTVF) to do what you want.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)