CODEWITHSUNDEEP
sqlsql-server
Kaner TUNCEL
Kaner TUNCEL

Reputation: 269

SQL Query From String

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?

Upvotes: 2

Views: 1778

Answers (4)

Andr&#225;s Ott&#243;
Andr&#225;s Ott&#243;

Reputation: 7695

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.

Upvotes: 1

marc_s
marc_s

Reputation: 754348

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.

Upvotes: 3

John Woo
John Woo

Reputation: 263693

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

Upvotes: 1

podiluska
podiluska

Reputation: 51494

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 ';

Upvotes: 0

Related Questions