Use "like statement" in stored procedure

• ramyamca84

  SSC Journeyman

  Points: 76

  More actions

  November 18, 2009 at 3:38 am

  #136229

  Hi frnds....

  I like to create store procedure for this stmt instead of using query

  select * from tabname where sname like 'ram%' and sdate='10/24/2009'

  I tried this procedure

  CREATE procedure GET_COMM @qcname varchar(30),@reldate varchar(10)

  as

  DECLARE @SSQL AS VARCHAR(1000)

  BEGIN

  SET @SSQL='select * from tabname where sname LIKE '''+LTRIM(@qcname)+''' and sdate='''+LTRIM(@reldate)+''''

  EXECUTE sp_executesql @SSQL

  END

  GO

  But it throws err like this...

  Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

  can anyone clear this..

  Thanks in advance

• Old McDonald

  SSCertifiable

  Points: 5768

  More actions

  November 18, 2009 at 3:52 am

  #1080782

  DECLARE @SSQL AS VARCHAR(1000)

  change @SSQL to NVARCHAR and it will work

  read about sp_executesql on BOL.

  -Vikas Bindra

• Jus

  SSCrazy

  Points: 2536

  More actions

  November 18, 2009 at 9:29 am

  #1080996

  Instead...

  to make the code work with varchar variable..

  you can change the code

  from

  EXECUTE sp_executesql @SSQL

  to

  Exec(@SSQL)

  --Jus