Post reply

delimiter in a variable

  • July 8, 2011 at 1:26 pm

    #145901

    Hi All,

    In the following query,

    SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%TempTable%'

    I want to make TempTable as variable as in, I want to make the following query

    SET @tableName = 'TempTable'

    SET @sql = 'SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE %'+ @tableName+ '%'

    EXEC (@sql)

    I am not able to do this as I can not put delimiter in a query.

    Can anybody please help?

    Thank you!

  • July 8, 2011 at 1:34 pm

    #1350619

    are you not able to use it like this

    declare @Tablename varchar(10)

    set @tablename = 'tablename'

    SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE '%'+ @tableName+ '%'

    Jayanth Kurup[/url]

  • July 8, 2011 at 1:38 pm

    #1350622

    No. I want the delimiters before and after % like '%AOC_CompanyCode%'

  • July 8, 2011 at 1:43 pm

    #1350625

    try this

    set @tablename = CHAR(37)+'ticker'+char(37)

    select @tablename

    SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE @tableName

    Jayanth Kurup[/url]

  • July 8, 2011 at 1:52 pm

    #1350630

    I did not understand this. Could you please explain?

    and above did not work. error was "A severe error occurred on the current command."

  • July 8, 2011 at 2:30 pm

    #1350649

    I was able to execute this successfully with out any errors , could you explain how your executing the query ?

    declare @tablename varchar(20)

    --Declared a variable to store search term

    set @tablename = CHAR(37)+'ticker'+char(37)

    -- Set value for search term using char(37) which is Asci char map for "%"

    select @tablename

    SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.TEXT LIKE @tableName

    -- Above query passes value @tablename as '%ticker%'

    Jayanth Kurup[/url]

  • July 8, 2011 at 2:36 pm

    #1350652

    For me its giving the value of @tablename as %ticker%

  • July 8, 2011 at 3:02 pm

    #1350662

    I used CHAR(39) ..Ascii value for single quote

Viewing 8 posts - 1 through 7 (of 7 total)

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