Passing an array of input parameters to stored procedure

• Raja Raman S

  SSC Veteran

  Points: 259

  More actions

  August 23, 2010 at 12:47 am

  #100297

  Hi,

  I am developing an application in .Net 2003 with Sql Server 2005 version. I have to pass some input parameters with comma separated to a stored procedure as for eg:

  CREATE PROCEDURE GetRics

  (@RICLIST varchar(8000)) // where @EmpNames are XS0256312264=MSXL,XS0473293701=MSXL,USP5880CAA82=MSXL,XS0442190855=MSXL....etc.

  Now, my problem is I cannot send this parameter list as xml since .net 2003 doesn't support xml datatype(even though sql server 2005 does). So I did the following

  CREATE PROCEDURE GetRics

  (@RICLIST varchar(8000))

  as

  begin

  declare @RICLST varchar(8000)

  declare @pos int

  SET @RICLST = @RICLIST + ','

  WHILE CHARINDEX(',',@RICLST) > 0

  begin

  SET @pos = CHARINDEX(',', @RICLST)

  SET @RICLST = STUFF(@RICLST, 1, @pos, '''')

  end

  This is to print the data as below:

  'XS0256312264=MSXL','XS0473293701=MSXL','USP5880CAA82=MSXL','XS0442190855=MSXL'. However, when I tried to print, it is printing without any quotes. So how will I pass the single quotes?

  Or is the above syntax will be fine if I pass the string from frontend? I checked the above just executing the command from query window using exec [StoredProcedure] command. I can pass the whole thing in a string from frontend.

  Please help. Thanks in advance.

  Rajaraman.

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  August 23, 2010 at 4:12 am

  #1210330

  Try this

  declare @RICLST varchar(8000)

  set @RICLST ='XS0256312264=MSXL,XS0473293701=MSXL,USP5880CAA82=MSXL,XS0442190855=MSXL'

  select '''' + replace (@RICLST , ',', ''',''') + ''''

  PRINT @RICLST

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)