SQL Query From String

Question

I'm trying to execute an SQL Query from a string that I generate with variables. This SP produces a Select query and when I execute this query manually it works, but when it's in SP there is an error like

Msg 911, Level 16, State 4, Procedure sp_Products_Select, Line 26
Database 'SELECT *,(Select Section From Sections Where SectionID = Products' does not exist. Make sure that the name is entered correctly.

The query that generated from SP

SELECT 
   *, 
   (Select Section 
    From Sections 
    Where SectionID = dbo.Products.SectionID) as Section,
   (Select Category 
    From Categories 
    Where CategoryID = Products.CategoryID) as Category 
FROM 
    Products 
WHERE 
    1 = 1AND (Status = 1 OR Status = 0)

And my SP is,

ALTER PROC [dbo].[sp_Products_Select]
    @ProductID int,
    @Status int,
    @CategoryID int,
    @SectionID int
AS

DECLARE @SQL varchar(500)
SET @SQL ='SELECT *,(Select Section From Sections Where SectionID = Products.SectionID) as Section,(Select Category From Categories Where CategoryID = Products.CategoryID) as Category FROM Products WHERE 1 = 1';
IF @ProductID <> '0' BEGIN
    SET @SQL += 'AND ProductID = @ProductID';
END
IF  @Status <> 0 BEGIN 
    SET @SQL += 'AND Status = @Status';
END ELSE BEGIN
    SET @SQL += 'AND (Status = 1 OR Status = 0)';
END
IF @CategoryID <> '0' BEGIN
    SET @SQL += 'AND CategoryID = @CategoryID';
END
IF @SectionID <> '0' BEGIN
    SET @SQL += 'AND SectionID = @SectionID';
END

EXEC @SQL

How can I execute this query from SP correctly?

Just take care of your 'AND CategoryID = @CategoryID' parts too. — András Ottó
– András Ottó, Commented Aug 29, 2012 at 10:06

marc_s · Accepted Answer · 2012-08-29 08:58:37Z

3

~~You're missing a space in your WHERE clause:~~

WHERE 1 = 1AND (Status = 1 OR Status = 0)

must be:

WHERE 1 = 1 AND (Status = 1 OR Status = 0) * * space here is absolutely essential!

~~So you need to change your stored procedure's code that builds up the T-SQL statement to put at least one space there between 1 = 1 and the AND ....~~

(that wasn't the problem, as Martin Smith discovered....)

Update: ok - so that space doesn't seem to be required after all.....

BUT: try using

EXEC (@SQL)

I believe you need to put your SQL statement into brackets when executing it.

edited Aug 29, 2012 at 8:58

answered Aug 29, 2012 at 8:53

marc_s

760k185 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Martin Smith Over a year ago

SELECT 1 WHERE 1 = 1AND 2=2 actually parses fine. The problem is misssing brackets after EXEC(...)

marc_s Over a year ago

@MartinSmith: hmmm.... you're right .... I could have sworn I had issues with basically the same construct (WHERE 1=1AND ....) in my days... :-)

John Woo Over a year ago

@KanerTUNCEL what erro do you get?

András Ottó Over a year ago

And how is it working when @CategoryID is not 0, it should crash.

Martin Smith Over a year ago

@AndrásOttó Depends on the preceding line. If it is AND (Status = 1 OR Status = 0) then it will parse OK. if it is AND Status = @Status it won't. The OP should still put the spaces in. Just that wasn't the reason for the error they were getting. (Ah having just seen your answer I see what you mean. Yes that will be another issue!)

|

John Woo · Accepted Answer · 2012-08-29 08:55:07Z

1

try simplifying your query,

SELECT  a.*, b.Section, c.Section
FROM    Products a
            INNER JOIN Sections b
                ON a.SectionID = a.SectionID
            INNER JOIN Categories c
                ON a.SectionID = c.Category

and in your SET @SQL syntax, you need to add extra space with them.

SET @SQL += ' AND ProductID = @ProductID';
             ^ here

answered Aug 29, 2012 at 8:55

John Woo

265k70 gold badges508 silver badges500 bronze badges

Comments

András Ottó · Accepted Answer · 2012-08-29 10:04:59Z

EDITED:

So the EXEC(..) won't solve all of your problem!

I think you even need to declare @ProductID and @CategoryID and the other parameters for the dynamic query, if you want to run them inside.

EXEC starts an other scope, like an other StoredProcedure call. In an other scope your @variables won't be available. You can make reference on temp tables, but not on memory tables or variables.

I would say, change those lines like:

SET @SQL += 'AND ProductID = @ProductID'

To something like this (this one is a bit ugly btw):

SET @SQL += 'AND ProductID = ' + CAST(@ProductID as varchar(20))

OR

Take a look on sq_executesql , with this you can define parameters and values to the parametes. If you check the SQL Profiler with a lot of ad-hoc queries the engine is doing the same. Just running the input as a dynamic sql with sp_executesql and parameters.

podiluska · Accepted Answer · 2012-08-29 08:54:49Z

0

You need a space after 1=1

'SELECT *,(Select Section From Sections Where SectionID = Products.SectionID) as Section,(Select Category From Categories Where CategoryID = Products.CategoryID) as Category FROM Products WHERE 1 = 1 ';

answered Aug 29, 2012 at 8:54

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Collectives™ on Stack Overflow

SQL Query From String

4 Answers 4

6 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Related