Compiling SQL statement in stored proc

(I'm sure this is possible, so maybe someone can just mention the topic that covers it and I'll research it from there.)

In essence, I have two SQL statements that are 98% the same, but I want to substitute a few different clauses according to the variable passed in.

I can get the following simple example to work fine, as long as the CASE statement adds a simple string.
But syntax errors appear when trying to use more complex SQL statements, such as FROM or JOIN clauses, in the CASE statement. I think it doesn't like keywords next to 'WHEN'.

If it's possible to compile the SQL statement as one long string while substituting the conditional clauses, that would work too. That's what I already do in VBScript on the ASP page querying the DB, but I'd like to remove the 1.5-page-long SQL statement from the code to the database, and just pass in one of two options.

The commented-out lines represent my failed attempt. Clues?

CREATE PROCEDURE dbo.tstsp
@.usr char(1)

AS

SET NOCOUNT ON
BEGIN

--DECLARE @.strSQL varchar(3000)
--SET @.strSQL = 'SELECT * FROM dbo.sur_followup WHERE '

SELECT * FROM dbo.sur_followup WHERE USERNAME =

CASE @.usr
-- WHEN 'a' THEN SET @.strSQL = @.strSQL + 'USERNAME = '"ASnow" '
-- WHEN 'b' THEN SET @.strSQL = @.strSQL + ''response_id = 214'
WHEN 'a' THEN 'ASnow'
WHEN 'b' THEN 'JUser'
END

--EXEC @.strSQL

SET NOCOUNT OFF
END
(This example just shows the idea...my query is actually much more complex.)
Thanks!

Hi,

I'm not sure I fully understand your problem, but I have a few initial responses

1) As Umachandar recently explained in the post on "Paging large result sets", you should be using sp_execsql so that you have an opportunity for plan caching.

2) If you're attempting to use "more complex SQL statements, such as FROM or JOIN clauses, in the CASE statement" then they need to evaluate to scalar expressions. You must enclose subqueries in () when the result is needed in a scalar expression context. Here is a silly, simplified example that illustrates the point:

DECLARE @.id int
SELECT @.id = 2
SELECT * FROM sysobjects
WHERE name = CASE @.id
WHEN 1 THEN (SELECT name FROM sysobjects WHERE id=@.id)
WHEN 2 THEN (SELECT name FROM sysobjects WHERE id=@.id)
-- etc
ELSE '?'
END

Does this answer your question?!

Regards,
Clifford Dibble|||

Thanks for the reply, Clifford.

Perhaps a snippet of VBScript code will explain better. This is what I want to do in the stored proc:

[...lots of SQL before this...]
If strRptType = "naddr" Then strSQL = strSQL & "FROM SUR_RESPONSE "

If strRptType = "yaddr" Then
strSQL = strSQL & "FROM SUR_FOLLOWUP "
strSQL = strSQL & "LEFT JOIN SUR_RESPONSE "
End If

strSQL = strSQL & "LEFT JOIN SUR_RESPONSE_ANSWER ON SUR_RESPONSE.RESPONSE_ID = SUR_RESPONSE_ANSWER.RESPONSE_ID "
strSQL = strSQL & "LEFT JOIN SUR_ITEM ON SUR_RESPONSE_ANSWER.ITEM_ID = SUR_ITEM.ITEM_ID "

If strRptType = "yaddr" Then strSQL = strSQL & "ON SUR_FOLLOWUP.response_id = SUR_RESPONSE.RESPONSE_ID "

strSQL = strSQL & "WHERE "

[...lots of SQL after this...]
I think what I want to do is called 'dynamic SQL.' I basically would need to compile the SQL string into a variable, but substitute certain clauses according to the parameter passed into the stored proc. I tried using CASE/WHEN instead of IF/THEN but I guess I don't know the proper syntax for all this.

|||Since you're stuck on doing dynamic SQL, just do the string manipulation in your language of choice (since it will undoubtedly handle strings better/easier) and pass the whole thing on to SQL server as the text of a command object and lose the stored procedure since it's not gaining you anything anyway (be sure to parse the strings of the where clause to avoid SQL injection attacks).

When I'm writing stored procedures with multiple where clauses driven by parameters, I take the laborious route of actually writing the whole stored proc with loads of if statements. E.g.

create procedure db.SomeTableGet(
@.p_lVar1 integer = null,
@.p_lVar2 integer = null,
@.p_lVar3 integer = null)

if @.p_lVar1 is null

if @.p_lVar2 is null

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col3 = @.p_lVar3

else

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col2 = @.p_lVar2

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1
and t.Col2 = @.p_lVar2

else

if @.p_lVar2 is null

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col3 = @.p_lVar3
and t.Col1 = @.p_lVar1

else

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col2 = @.p_lVar2
and t.Col1 = @.p_lVar1

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1
and t.Col2 = @.p_lVar2
and t.Col1 = @.p_lVar1

As you can see, the sp's get long in a hurry, but you can copy and paste most of it, and if you're really enterprising, you can either write a tool to do most of it, or use CodeSmith scripts to do it for you. Also, this kind of thing messes with SQL Server's parameter guessing logic "stuff", but oh well. I wonder if anyone has any ideas on a better way to do it while keeping it in a stored procedure and avoiding exec'ing SQL strings? The main purpose here is to keep the sorting and filtering on the SQL server instead of pushing it off into the business tier that isn't very good at filtering or sorting and doesn't have the advantage of SQL Server's table indexes (and to keep from transferring larger-than-necessary recordsets from the SQL box to the business tier box).|||

Hi Robert
I always write my SPs with multiple where clauses driven by parameters as follows

create procedure dbo.SomeTableGet
@.p_lVar1 integer = 0, -- zeros not nulls cos null <> null
@.p_lVar2 integer = 0,
@.p_lVar3 integer = 0
as
select t.Col1,t.Col2,t.Col3
from Table t
where (case when @.p_lVar1 = 0 then 0 else t.Col1 end )= @.p_lVar1
And (case when @.p_lVar2 = 0 then 0 else t.Col2 end )= @.p_lVar2
And (case when @.p_lVar3 = 0 then 0 else t.Col3 end )= @.p_lVar3

|||

Hi DN,

I think I finally understand your issue. The key point for me was "tried using CASE/WHEN instead of IF/THEN"

Here is the syntax for the CASE expression.

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Notice that (a) this is an expression, so it must produce a value and (b) the same holds for result_expression.

But, SET is not an expression. To see this, try this in a query window:

DECLARE @.x char(1)
SET @.x = 'a'

and notice that no value is produced.

Therefore, this is an illegal expression and so you get a syntax error

SELECT
CASE @.x
WHEN 'a' THEN SET @.y = '1'
WHEN 'b' THEN SET @.y = '2'
END
Server: Msg 156, Level 15, State 1, Line 7

Does this answer your question?!

Regards,
Clifford Dibble