CODEWITHSUNDEEP
sql.netsql-serversql-server-2008where-in
Jasmine
Jasmine

Reputation: 5326

where condition have a declared variable on a wrong place do not know if its valid

I have a statement like this below in one of my big stored proc, and I am wondering if this is valid to write this way.

SELECT  @PVDate = pv.Date,
        @PVdMBeginDate = dbo.fPVoidDate(pv.myID)
FROM PVMeter pv (NOLOCK)
WHERE pv.PR_ID = @PR_ID
AND @VCommen BETWEEN pv.PVDMDate AND dbo.fPVoidDate(pv.myID)

Now, here, my question is, @VCommen is a declared date variable with a value set on it. It is not at all a column of PVMeter, while PVDMDate is a column in PVMeter and fpVoidDate returns datetime

While I debug SP, I do not see the value on @PVDate and @PVDMBeginDate

Upvotes: 0

Views: 68

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

The original query is equivalent to:

SELECT  
    @PVDate = pv.Date,
    @PVdMBeginDate = dbo.fPVoidDate(pv.myID)
FROM PVMeter pv (NOLOCK)
WHERE 
    pv.PR_ID = @PR_ID
    AND pv.PVDMDate <= @VCommen
    AND @VCommen <= dbo.fPVoidDate(pv.myID)

This style should be more familiar. In general, you can put any expression in the WHERE clause, it can be made of variables or constants without referring to table columns at all.

Examples

Classic example: WHERE 1=1 ... when the query text is generated dynamically. It is easy to add as many expressions as needed in no particular order and prepend all of them with AND.

DECLARE @VarSQL nvarchar(max);

SET @VarSQL = 'SELECT ... FROM ... WHERE 1=1 ';

IF ... THEN SET @VarSQL = @VarSQL + ' AND expression1';
IF ... THEN SET @VarSQL = @VarSQL + ' AND expression2';
IF ... THEN SET @VarSQL = @VarSQL + ' AND expression3';

EXEC @VarSQL;

Thus you don't need to have complex logic determining whether you need to add an AND before each expression or not.

Another example.

You have a stored procedure with parameter @ParamID int.

You have a complex query in the procedure that usually returns many rows and one column of the result set is some unique ID. 

SELECT ID, ...
FROM ...
WHERE
expression1
AND expression2
AND expression3
...

You want to return all rows if @ParamID is NULL and only one row with the given ID if @ParamID is not NULL. I personally use this approach. When I open the screen with the results of a query for the first time I want to show all rows to the user, so I pass NULL as a parameter. Then user makes changes to a selected row, which is done through a separate UPDATE statement. Then I want to refresh results that user sees on the screen. I know ID of the row that was just changed, so I need to requery just this row, so I pass this ID to procedure and fetch only one row instead of the whole table again.

The final query would look like this:

SELECT ID, ...
FROM ...
WHERE
(@ParamID IS NULL OR ID = @ParamID)
AND expression1
AND expression2
AND expression3
...
OPTION (RECOMPILE);

Thus I don't have to repeat the complex code of the query twice.

Upvotes: 1

Related Questions