April 15, 2011 at 8:35 am
I am trying to make a search query that I can pass a search phrase and the order field and order type but I keep getting an error.
DECLARE @NEW_SEARCH_STRING varchar(250)
DECLARE @SORTFIELD varchar(50)
DECLARE @SORTFIELD_TYPE varchar(5)
SET @SORTFIELD = 'PRODUCT_NAME'
SET @SORTFIELD_TYPE = 'ASC'
SET @NEW_SEARCH_STRING = 'nfl football'
DECLARE @sSQL as nvarchar(4000)
SET @sSQL = 'SELECT p.product_id, p.product_name, p.product_sml_img, p.product_size, isnull(pti.pti_image, '''') as product_type_image, fnCreateProductURL_New(product_id,null) as product_url
FROM product p
JOIN category c ON p.cat_id = c.cat_id
LEFT JOIN PRODUCT_TYPE_IMAGES pti ON pti.pti_id = p.product_ifr
WHERE p.product_display_cd = 0
AND c.cat_display_cd = 0
AND freetext(product_name, @SEARCH_STRING) ORDER BY @sfield @SFIELD_TYPE'
DECLARE @ParmDefinition nvarchar(4000)
SET @ParmDefinition = N'@SEARCH_STRING varchar(250), @sfield varchar(50), @SFIELD_TYPE varchar(5)'
EXECUTE sp_executesql @sSQL, @ParmDefinition, @NEW_SEARCH_STRING, @SORTFIELD, @SORTFIELD_TYPE
The error I get is:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@SFIELD_TYPE'.
Anyone know what I am doing wrong?
April 15, 2011 at 8:47 am
You were missing a comma to separate the sorted columns ORDER BY @sfield, @SFIELD_TYPE
DECLARE @NEW_SEARCH_STRING VARCHAR(250)
DECLARE @SORTFIELD VARCHAR(50)
DECLARE @SORTFIELD_TYPE VARCHAR(5)
SET @SORTFIELD = 'PRODUCT_NAME'
SET @SORTFIELD_TYPE = 'ASC'
SET @NEW_SEARCH_STRING = 'nfl football'
DECLARE @sSQL AS NVARCHAR(4000)
SET @sSQL = 'SELECT p.product_id, p.product_name, p.product_sml_img, p.product_size, isnull(pti.pti_image, '''') as product_type_image, fnCreateProductURL_New(product_id,null) as product_url
FROM product p
JOIN category c ON p.cat_id = c.cat_id
LEFT JOIN PRODUCT_TYPE_IMAGES pti ON pti.pti_id = p.product_ifr
WHERE p.product_display_cd = 0
AND c.cat_display_cd = 0
AND freetext(product_name, @SEARCH_STRING) ORDER BY @sfield, @SFIELD_TYPE'
DECLARE @ParmDefinition NVARCHAR(4000)
SET @ParmDefinition = N'@SEARCH_STRING varchar(250), @sfield varchar(50), @SFIELD_TYPE varchar(5)'
EXECUTE sp_executesql @sSQL, @ParmDefinition, @NEW_SEARCH_STRING, @SORTFIELD,
@SORTFIELD_TYPE
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 15, 2011 at 9:06 am
No, that means my sql would be
SELECT *
FROM <table>
ORDER by <field>, <ASC|DESC>
That doesn't look right to me.
April 15, 2011 at 9:11 am
Sorry wasn't paying that close attention. It is Friday and all. My Bad.
Have you tried running the Query against the DB directly with out the variables and see what you get?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 15, 2011 at 10:06 am
Yes, it returns results.
SELECT p.product_id, p.product_name, p.product_sml_img, p.product_size, isnull(pti.pti_image, '''') as product_type_image, fnCreateProductURL_New(product_id,null) as product_url
FROM product p
JOIN category c ON p.cat_id = c.cat_id
LEFT JOIN PRODUCT_TYPE_IMAGES pti ON pti.pti_id = p.product_ifr
WHERE p.product_display_cd = 0
AND c.cat_display_cd = 0
AND freetext(product_name, 'nfl football') ORDER BY PRODUCT_NAME ASC
If I remove the ORDER BY <field> <ASC|DESC> part it works fine. Can I put the results into a temp table and then do ORDER BY?
April 15, 2011 at 10:30 am
Try removing the variable for ASC/DESC I am guessing this is where it has the issue. I have never tried using a variable for that but that would seem to be where the most likely cause of the problem is coming from.
If that is the case try concantenating the two variables into a single string and then use that in you order by clause.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 15, 2011 at 1:50 pm
You cannot use variables in your ORDER BY for your column list or the ASC/DESC expression...I tested in 2005 and 2008.
Try it this way:
DECLARE @NEW_SEARCH_STRING VARCHAR(250),
@SORTFIELD VARCHAR(50),
@SORTFIELD_TYPE VARCHAR(5),
@ParmDefinition NVARCHAR(MAX),
@sSQL AS NVARCHAR(MAX) ;
SELECT @SORTFIELD = 'ContactID',
@SORTFIELD_TYPE = 'DESC',
@NEW_SEARCH_STRING = 'amy%',
@sSQL = 'SELECT *
FROM AdventureWorks.Person.Contact
WHERE EmailAddress LIKE @SEARCH_STRING
ORDER BY ' + @SORTFIELD + ' ' + @SORTFIELD_TYPE,
@ParmDefinition = N'@SEARCH_STRING varchar(250), @sfield varchar(50), @SFIELD_TYPE varchar(5)' ;
EXECUTE sys.sp_executesql
@statement = @sSQL,
@params = @ParmDefinition,
@SEARCH_STRING = @NEW_SEARCH_STRING,
@sfield = @SORTFIELD,
@SFIELD_TYPE = @SORTFIELD_TYPE ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply