May 7, 2009 at 10:26 am
I've done parameterized sql before and had no problems but I can't see what I am doing wrong here. Please help.
The following SQL returns this error:
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@p_OP'.
DECLARE @sql nvarchar(MAX),
@OP nvarchar(2),
@Name nvarchar(20),
@Value nvarchar(20)
SET @sql = N'
SELECT *
FROM [MyTable]
WHERE ([@p_Name] @p_OP ''@p_Value'')'
SET @OP = N'='
SET @Name = N'MyColumn'
SET @Value = N'My Matching Value'
EXEC sp_executesql @sql,
N'@p_Name nvarchar(20), @p_OP nvarchar(10), @p_Value nvarchar(320)',
@p_Name = @Name,
@p_OP = @OP,
@p_Value = @Value
If I PRINT the @sql I get the following:
SELECT *
FROM [MyTable]
WHERE ([@p_Name] @p_OP '@p_Value')
So assuming the parameterization is working properly sp_executesql should run a query like this:
SELECT *
FROM [MyTable]
WHERE ([MyColumn] = 'My Matching Value')
In an attempt to troubleshoot the error I got rid of the @p_OP parameter and just used a hard-coded = sign.
That was enough to make the error go away but brought up a whole new issue. The query always returns an empty result set. Even for values that I know should return results.
My hunch is that the parameters are not being substituted.
So, if I change the above code to be this:
DECLARE @sql nvarchar(MAX),
@Name nvarchar(20),
@Value nvarchar(20)
SET @sql = N'
SELECT *
FROM [MyTable]
WHERE ([@p_Name] = ''@p_Value'')'
SET @Name = N'MyColumn'
SET @Value = N'My Matching Value'
EXEC sp_executesql @sql,
N'@p_Name nvarchar(20), @p_Value nvarchar(320)',
@p_Name = @Name,
@p_Value = @Value
I always get an empty result.
Now if I PRINT the @sql string I get:
SELECT *
FROM [MyTable]
WHERE ([@p_Name] = '@p_Value')
And assuming the parameterization is working would result in sp_executesql running the query as:
SELECT *
FROM [MyTable]
WHERE ([MyColumn] = 'My Matching Value')
However, if I run the expected query manually I will get results but sp_executesql gives none.
What am I doing wrong? Its probably something simple that I've overlooked. Could this be a permissions issue? Although I am dbo for this database.
Thanks in advance.
May 7, 2009 at 10:33 am
Your problem is with how you are trying to build your dynamic query. You can't use variables passed in like you are trying, as you are trying to put variables where they aren't allowed in a normal query.
May 7, 2009 at 10:39 am
This is closer to what you need:
DECLARE
@sql nvarchar(MAX),
@OP nvarchar(2),
@Name nvarchar(20),
@Value nvarchar(20);
SET @OP = N'=';
SET @Name = N'MyColumn';
SET @Value = N'My Matching Value';
SET @sql = N'
SELECT *
FROM [MyTable]
WHERE ([' + @Name + '] ' + @OP + ' ''@p_Value'')';
print @sql;
EXEC sp_executesql @sql,
N'@p_Value nvarchar(320)',
@p_Value = @Value;
May 7, 2009 at 10:45 am
Thanks for the quick replies!
So I thought the point of the parametrization was to avoid using string concatenation?
But it only works in certain situations? That's odd.
I suppose I could use QUOTENAME(@Name) in the string concatenation.
I'm validating the input first anyways I just want to be as careful as I can be.
May 7, 2009 at 11:02 am
Okay still no luck.
My query now looks something like this:
DECLARE @sql nvarchar(MAX),
@OP nvarchar(9),
@Name nvarchar(128),
@Value nvarchar(320)
SET @OP = N' = '
SET @Name = N'MyColumn'
SET @Value = N'My Matching Value'
SET @sql = N'
SELECT *
FROM [MyTable]
WHERE (' + QUOTENAME(@Name) + @OP + '''@p_Value'')'
EXEC sp_executesql @sql,
N'@p_Value nvarchar(320)',
@p_Value = @Value
PRINT @sql now gives:
SELECT *
FROM [MyTable]
WHERE ([MyColumn] = '@p_Value')
But I'm still getting no results from sp_executesql.
And again, if I take the above printed query and manually substitue @p_Value for a known matching value I get the results I want.
May 7, 2009 at 11:27 am
mikesigsworth (5/7/2009)
Okay still no luck.My query now looks something like this:
DECLARE @sql nvarchar(MAX),
@OP nvarchar(9),
@Name nvarchar(128),
@Value nvarchar(320)
SET @OP = N' = '
SET @Name = N'MyColumn'
SET @Value = N'My Matching Value'
SET @sql = N'
SELECT *
FROM [MyTable]
WHERE (' + QUOTENAME(@Name) + @OP + '''@p_Value'')'
EXEC sp_executesql @sql,
N'@p_Value nvarchar(320)',
@p_Value = @Value
PRINT @sql now gives:
SELECT *
FROM [MyTable]
WHERE ([MyColumn] = '@p_Value')
But I'm still getting no results from sp_executesql.
And again, if I take the above printed query and manually substitue @p_Value for a known matching value I get the results I want.
Try this instead:
DECLARE @sql nvarchar(MAX),
@OP nvarchar(9),
@Name nvarchar(128),
@Value nvarchar(320)
SET @OP = N' = '
SET @Name = N'MyColumn'
SET @Value = N'My Matching Value'
SET @sql = N'
SELECT *
FROM [MyTable]
WHERE (' + QUOTENAME(@Name) + @OP + '@p_Value)'
May 7, 2009 at 12:07 pm
OIC the parameter is still a parameter in the query. I was thinking for some reason the @p_Value would be replaced inline with the value but it just works as a normal parameter. Duh. I feel stupid now.
Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply