Use of '' + in SQL Server 2005 Stored Procedure to build SQL string

Question

Hi

I'm building a stored procedure which is rather stretching my experience. With the help of people who responded to this thread http://stackoverflow.com/questions/1863527/nested-if-statements-in-mssql-stored-procedure-select-statement I think I'm most of the way there :)

In short, the SP takes a series of paramterised inputs to dynamically build an SQL statement that creates a temporary table of id values ordered in a specific way. The remainder of the SP, which returns the data according to the requested page from the id values in this temporary table is all sorted.

I am having to build it this way because of the way legacy projects use this code. Some request all the records, some have search functions that pass the search field (OIR.objectinput_id) and the value to find against that (OIR.value).

The dyamicly generated area of code starts with:

    DECLARE @SQL VARCHAR(MAX)   
    SET @SQL = '

And then continues to build up the statement according to what has been passed in.

I think I've got most of the handling of '' correct when building the SQL string in this way. Thus:

case when @displayordertype = 'string' then

Becomes:

'       case when ' + '@displayordertype' + ' = ''string'' then             '

And:

else ''

Becomes:

else ''''

Etc.

Where I'm coming unstuck, and have been going round in circles for half a day is when I need to pass in an integer value variable. Like this:

'       WHERE OT.site_id = ' + @siteid          '

Which falls over with:

Conversion failed when converting the varchar value 'full sql string generated right up to the line above' to data type int

I've tried:

'       WHERE OT.site_id = @siteid              '

'       WHERE OT.site_id = ' + @siteid + '      '

And a few other derivations.

I've tried replacing with:

'       WHERE OT.site_id = 10001                '

Which works correctly.

Clearly I'm missing something about building up a SQL string in this way and how to pass in both int and varchar parameters, escape them with ' ' or '' '' and use + to build up the string.

Any assistance with this would be really apprecaited. Thank you.

Regards, Simon

Relevant part of the SP below. The dynamically generated bit starts with:

If @searchf1 <> ''

And ultimately there will be up to 15 of these passed in search field/result pairs.

@siteid int,         
@refkey varchar(200),     -- refkey of individual item to return
@itemid int,      -- id of individual item to return (if neither @refkey or @itemid are present the SP will assume a list of all items according to search filter(s))
@searchf1 varchar(100),   -- search filter 1
@searchr1 varchar(100),   -- search result 1
@searchf2 varchar(100),   -- search filter 2
@searchr2 varchar(100),   -- search result 2
@searchf3 varchar(100),   -- search filter 3
@searchr3 varchar(100),   -- search result 3
@searchf4 varchar(100),   -- search filter 4
@searchr4 varchar(100),   -- search result 4
@searchf5 varchar(100),   -- search filter 5
@searchr5 varchar(100),   -- search result 5
@searchf6 varchar(100),   -- search filter 6
@searchr6 varchar(100),   -- search result 6
@searchf7 varchar(100),   -- search filter 7
@searchr7 varchar(100),   -- search result 7
@searchf8 varchar(100),   -- search filter 8
@searchr8 varchar(100),   -- search result 8
@searchf9 varchar(100),   -- search filter 9
@searchr9 varchar(100),   -- search result 9
@searchf10 varchar(100),     -- search filter 10
@searchr10 varchar(100),     -- search result 10
@searchf11 varchar(100),     -- search filter 11
@searchr11 varchar(100),     -- search result 11
@searchf12 varchar(100),     -- search filter 12
@searchr12 varchar(100),     -- search result 12
@searchf13 varchar(100),     -- search filter 13
@searchr13 varchar(100),     -- search result 13
@searchf14 varchar(100),     -- search filter 14
@searchr14 varchar(100),     -- search result 14
@searchf15 varchar(100),     -- search filter 15
@searchr15 varchar(100),     -- search result 15
@languageid int,      -- language to return
@itemidlist varchar(8000),   -- object types (i.e., the TYPE of object)
@fieldidlist varchar(8000),  -- fields that we want to return
@displayorderid int,      -- which field to order by
@displayordertype varchar(50),  -- whether the order by field is a string, date or number
@direction char(1),    -- whether to order 
@page int,       -- page number
@pagesize int,      -- records per page
@maxrecords int     -- maximum records

AS
set nocount on
set dateformat dmy

-- create temporary table with all data

CREATE TABLE #tmp
    (
    uid int identity(1,1), 
    [object_id] int,
    displayorder_string varchar(8000),
    displayorder_date datetime,
    displayorder_number real
    )

DECLARE @recordcount int

IF @searchf1 <> '' -- a search has been performed so look up search parameters

    DECLARE @SQL VARCHAR(MAX)   

    SET @SQL = '

    INSERT INTO #tmp ([object_id], displayorder_String, displayorder_Date, displayorder_Number)
    SELECT
        OIR.[object_id], 
  case when ' + '@displayordertype' + ' = ''string'' then
   case when OI.objectinputtype_id = 1 then OIR.[value]
    when OI.objectinputtype_id = 2 then OIR.[value]
    when OI.objectinputtype_id = 6 then OIR.[value]
    when OI.objectinputtype_id = 3 then dbo.fnGetItemDataResponseValue(OIR.site_id, OIR.language_id, OIR.objectinput_id, OIR.objecttype_id, OIR.[object_id])
    else ''''
   end
  else
   ''''
  end,
  case when ' + ' @displayordertype' + ' = ''date'' then
   convert(datetime, 
    (case when OI.objectinputtype_id = 1 then OIR.[value]
     when OI.objectinputtype_id = 2 then OIR.[value]
     when OI.objectinputtype_id = 6 then OIR.[value]
     when OI.objectinputtype_id = 3 then dbo.fnGetItemDataResponseValue(OIR.site_id, OIR.language_id, OIR.objectinput_id, OIR.objecttype_id, OIR.[object_id])
     else ''''
    end),
    103)
  else
   null
  end,
  case when ' + '@displayordertype' + ' = ''number'' then
   convert(real, 
    (case when OI.objectinputtype_id = 1 then OIR.[value]
     when OI.objectinputtype_id = 2 then OIR.[value]
     when OI.objectinputtype_id = 6 then OIR.[value]
     when OI.objectinputtype_id = 3 then dbo.fnGetItemDataResponseValue(OIR.site_id, OIR.language_id, OIR.objectinput_id, OIR.objecttype_id, OIR.[object_id])
     else ''''
    end)
    )
  else
   0
  end
 FROM
  ObjectType OT,
  ObjectInput OI,
  ObjectInputResponse OIR,
  [Object] O,
  #tmp TMP 
 WHERE
  OT.site_id = ' + @siteid
  SET @SQL = @SQL + ' AND OT.deleted = ''n''
  AND OIR.deleted = ''n''
  AND O.deleted = ''n''
  AND OI.deleted = ''n''
  AND getdate() BETWEEN OT.validfrom AND OT.validto
  AND OT.id = OIR.objecttype_id
  AND OT.site_id = OIR.site_id
  AND OIR.language_id = ' + @languageid
  SET @SQL = @SQL + 'AND (OIR.objecttype_id IN (SELECT ItemValue FROM dbo.split_int(' + '@displayordertype' + ', '','')) OR ' + '@displayordertype' + ' = '''')
  AND OIR.objectinput_id = OI.id
  AND OIR.site_id = OI.site_id
  AND OIR.objecttype_id = OI.objecttype_id
  AND OIR.objectinput_id = ' + @displayorderid
  SET @SQL = @SQL + 'AND OIR.objectinput_id = 47
  AND OIR.[object_id] = O.id
  AND OIR.site_id = O.site_id
  AND getdate() BETWEEN O.validfrom AND O.validto
  AND OIR.objecttype_id = O.objecttype_id
  AND TMP.[object_id] = O.id'

  If @searchf1 <> '' 

   DECLARE @x int
   SET @x = 1

   SET @SQL = @SQL + '

    GROUP BY O.id HAVING

    --search 1 of 15   
        sum(case when OIR.objectinput_id = 1196 AND value IN(2395,2396) AND deleted = ''n'' AND language_id = 1 THEN 1 ELSE 0
    end) +
    @x = @x + 1'

   if @x >= 1 

    DECLARE @trim int
    SET @trim = len(@SQL)
    SET @trim = @trim-2
    SET @SQL = left(@SQL,@trim)

   --SET @SQL = @SQL + ' = ' --+ @ix (ignore this line for now, commented out)



 EXEC(@SQL)

Answer 1

To concatenate integer values into the dynamic SQL statement you need to convert to a varchar e.g:

....WHERE
    OT.site_id = ' + CAST(@siteid AS VARCHAR)

If the SQL statement is always going to be less than 4000 chars, I'd at least consider using sp_executesql to use parameterised SQL.

e.g.

DECLARE @SQL NVARCHAR(4000)
DECLARE @siteid INTEGER
SET @siteid = 1

SET @SQL = 'SELECT * FROM MyTable WHERE site_id = @siteid'
EXECUTE sp_executesql @SQL, N'@siteid INTEGER', @siteid

All in all, what you're doing is not likely to be very performant/scalable/maintainable and you don't really gain much from having it as a sproc. Plus you need to be very very careful to validate the input as you could open up yourself to SQL injection (hence my point about using sp_executesql with parameterised SQL).

Answer 2

You need to cast the int param to be a char/varchar so that you can add it to the existing string. The fact that you aren't surrounding it with quotes in the final sql means it will be interpreted as a number.

Answer 3

Take a look at CONVERT() and CAST() for the integers.

Answer 4

Reconsider the use of dynamic SQL - you should really know what you are doing if you go that route.

What is the problem you are trying to solve? I am sure people here will be able to find a better solution than the dynamic SQL you are proposing to use.