TSQL Stored proc argument error

Question

If I have this stored proc definition minus the body

ALTER PROCEDURE sp_AlloctionReport(@where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200))
AS

And I call like this.

sp_AllocationReport "ProductCode = 'FA' AND AllocationDate = '20090112' AND VenueInfo.VenueID In(SELECT vf.VenueID FROM VenueFilters vf INNER JOIN FilterTypes ft  ON vf.FilterTypeID = ft.FilterTypeID WHERE ft.FilterDescription = 'Coke') AND State = 'NSW'","CampaignAllocations.AllocationDate = '20090112'","CampaignAllocations.AllocationDate = '20090105'"

Why do I get this error when my first argument is defined NVARCHAR(1000).

The identifier that starts with 'ProductCode = 'FA' AND AllocationDate = '20090112' AND VenueInfo.VenueID In(SELECT vf.VenueID FROM VenueFilters vf INNER JOIN Fi' is too long. Maximum length is 128.

Answer 1

• Check your double quotes around "Coke". The syntax highligher makes the error obvious.
• Use single quote for the main string, and double escape them where needed. Yes, it's a pain, but it's the right way to do it.
• Prefix NVarchar literals with a capital N
• This smells of an sql injection hack waiting to happen. Are you sure you need to pass sql around as string variables?

Answer 2

1. Take the where clause and edit it in a new file
2. Replace ' with '' (single quote -> double single quote)
3. EXEC dbo.sp_AllocationReport @where= '<THE TEXT EDITED ABOVE>'

Answer 3

The maximum length of a object in sql server in 128 chars (I think). Perhaps a syntax problem when you build the query has made it think that the whole string is a table/view name?

Answer 4

You must use single quotes instead of double quotes unless the connection has QUOTED_IDENTIFIER turned ON

http://msdn.microsoft.com/en-us/library/ms174393.aspx

This is the default from SQL 2000 onwards, but if you've upgraded from an older version then the old default will still be active.

Answer 5

If we had the body of the stored proc, it might be clearer. But ... what it looks like is that SQL Server is interpreting your parameter as an indentifer (i.e., column name). The clue is the error message which states "The identifier that starts with 'ProductCode = 'FA' AND [...] is too long". i.e., SQL Server is looking for a column named "ProductCode = 'FA' AND [...etc...]"

So what I suspect is you've done this in within the stored proc:

SELECT col1, col2, col3, ... FROM table WHERE @where

...and your hoping the where clause to work just like that.

Assuming this is what you've done, it won't work. If this isn't what you've done, the rest of this answer may be completely bogus :-) If you can give an example of the body of the sproc, it might make things clearer.

So assuming my suspision is correct, you need to write it as a dynamic SQL statement like this:

DECLARE @sql NVARCHAR(2000)
SET @sql = 'SELECT col1, col2, col3, ... FROM table WHERE ' + @where
EXEC sp_ExecuteSQL @sql

HOWEVER ... even this isn't the end of the story as this is prone to injection attacks, which are a very bad thing. What you're better off doing is changing the params to your stored proc to make use of parameterised SQL which won't be prone to injection attacks. Something like this...

ALTER PROCEDURE sp_AllocationReport (@ProductCode VARCHAR(10), @AllocationDate DATETIME, {rest of your parameters})
AS
    DECLARE @sql NVARCHAR(2000)
    SET @sql = 'SELECT col1, col2, col3, ... FROM table WHERE 1 = 1'

    IF ISNULL(@ProductCode, '') <> ''
       SET @sql = @sql + ' AND ProductCode = @pProductCode'
    IF @AllocationDate IS NOT NULL
       SET @sql = @sql + ' AND AllocationDate = @pAllocationDate'
    {other conditionals, depending on what you need to pass in}

    EXEC sp_ExecuteSQL @sql, '@pProductCode    VARCHAR(10), 
                              @pAllocationDate DATETIME,
                              {other passed in params}
                             ', @ProductCode, @AllocationDate

This code isn't prone to injection attacks. It's also more performant as SQL Server will cache execution plans more reliably. Read up about this; there's plenty out there on it.