Basic IF question 4

[jararaca]

I have a stored procedure that needs to refer to one of two tables depending on the value of a parameter. Here's what I know and don't know how to do:

DECLARE @ThisQuery varchar(8000)

SET @ThisQuery = 'Select * '
SET @ThisQuery = @ThisQuery + 'FROM '
(Here, I need to be able to append different table names depending on the value of a parameter)
EXEC(@ThisQuery)

Thank you for your help.

 

[SQLDenis]

Why do you need an if?
Just use the variable

DECLARE @ThisQuery varchar(8000)

DECLARE @TAbleNAme varchar(50)
Select @TAbleNAme ='authors'

SET @ThisQuery = 'Select * FROM ' + @TAbleNAme

print @ThisQuery
EXEC(@ThisQuery)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Probably with some IF's before:

DECLARE @ThisQuery varchar(8000)
DECLARE @tablName varchar(128)

IF somebooleancondition
	SET @tableName = 'table1'
ELSE
	SET @tableName = 'table2'

SET @ThisQuery = 'Select * ' + 'FROM ' + @tableName
EXEC(@ThisQuery)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jararaca]

Thank you for the reply.

The reason is that the table name might change over time. I'd like to centralize this in the stored procedure, rather than rely on each application to do the right thing. That way, each app simply identifies the type of process it needs, and the sp can make the decision about what resources to use.

 

[gmmastros]

DECLARE @ThisQuery varchar(8000)

SET @ThisQuery = 'Select * '
SET @ThisQuery = @ThisQuery + 'FROM '

If @Param = SomeValue
  Begin
    Set @ThisQuery = @ThisQuery + 'Table1'
  End
Else
  Begin
    Set @ThisQuery = @ThisQuery + 'table2'
  End

EXEC(@ThisQuery)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jararaca]

Thank you. This is just what I needed!

 

[jararaca]

Is there some kind of switch, case, or elseif structure that would allow me to branch out into more than two directions? In other words, select one of three tables rather than just two?

Thanks, again!

 

[gmmastros]

DECLARE @ThisQuery varchar(8000)

SET @ThisQuery = 'Select * '
SET @ThisQuery = @ThisQuery + 'FROM '

Set @ThisQuery = @ThisQuery + 
    Case When @Param = Val1 Then 'Table1'
         When @Param = Val2 Then 'Table2'
         When @Param = Val3 Then 'Table3'
         End

EXEC(@ThisQuery)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

yes

IF Param  =1
    SET @tableName = 'table1'
ELSE IF Param  =2
    SET @tableName = 'table2'
ELSE IF Param  =3
    SET @tableName = 'table3'
.
.
.
.
ELSE     
     SET @tableName = 'table99'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jararaca]

You guys are awesome! Thanks, again!

 

[SQLSister]

An if inside an if.

However, I am concerned about your entire approach. Dynamic SQL is not a good technique. Table names should not change over time or you have very bad database design. What you want from any table should be different, so using a dynamic statement to make that decision is not only inefficent is it s security problem.

Why not write it without the dynamic SQL. Even if you have to have the if statements, you can you the parameter to determine which exact SQL statment to run. This will be better for your system performance wise, you can set security at the sp level instead of the table level, you aren't so vunerable to attacks, and it's easier to debug.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jararaca]

Thank you for your concern and interest. I'd like to try to better understand what you are saying. First, though, the issue of changing table names is not quite that way, actually. I was trying to keep my question simple, but what might actually change are business rules, and that might mean that required data may come from a different type of entity, or table, so the table required by the query might change, not the actual table name. I don't want applications concerned about this level of detail. Now, whether or not the sp is the best place to do it... I'm not sure. You seem to be indicating another approach, though I'm not sure I've understood it, yet. Can you please clarify what you mean when you say that I can "use the parameter to determine which exact SQL statement to run," and how this would be better for system performance?

Thank you very much for your interest!

 

[SQLDenis]

in that case create a view and if something changes, then just change the view so that the apps will be unaffected

What SQLSister is refering to is SQL Injections, queryplans not being reused and of course trying to debug the mess with print statements and/or SQL crappy debugger

Read this "The Curse and Blessings of Dynamic SQL" (

http://www.sommarskog.se/dynamic_sql.html)

written by SQL server MVP Erland Sommarskog to get the complete picture about dynamic SQL

Hope this helps



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jararaca]

Thank you. I will look into this!

 

[SQLSister]

Dennis's link is a good one.

What I'm trying to say is that if you know the business rules, then just write the SQL and use the if to take you to the branch rather than wirte a dynamic query within the if. example:

if @param2 = 'table2'
begin
select field1, field2, 'test' from table2
end
else 
begin
select field1, 0, field3 from table1
end

you still have the fleibility of going to different branches but are not using dynamic sql.



Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jararaca]

Ah! I get it. Thank you. The problem is dynamic SQL? I didn't realize that that was such an issue.

Thank you.