How to dynamically select inside a procedure

Question

I have this dilemma I am creating a procedure that uses a table from another database inside it. Say:

CREATE PROCEDURE uspRetrieveCurrentPropertyDate (
@InternalEntityId VARCHAR(10)
,@InternalUserId VARCHAR(10)
,@InternalSiteId VARCHAR(10) --this is the database I need to get the table from
)
as begin
...
...
select TOP 1 arsDailyCtlDate from @InternalSiteId..AccountsSetting
end

but of course it will return an error.

The original script uses something like:

SET @cSQL = 'SELECT TOP 1 arsDailyCtlDate FROM  S' + @SiteID + '.dbo.AccountsSetting WITH (NOLOCK)'

EXEC(@cSQL)

to accomplish the task. But I wanted to rewrite the code. Is there anyway I can do it the way I like it to be done? Without using exec(@cSQL)?

Thanks, Sherwin

Well, you do have to use dynamic sql, but instead of EXEC() you could use sp_executesql(). — Tab Alleman
– Tab Alleman, Commented Jul 22, 2015 at 13:50
What kind of error would it return? Why not use naming convention for selecting from another database like: databasename.schemaname.tablename in your dynamic sql? Also, using sp_executesql is recommended if you don't want to open doors for sql injection. — FutbolFan
– FutbolFan, Commented Jul 22, 2015 at 13:50
@NepaliRookie - database names in the query can't be parameterised, you're forced to write a literal value in to the sql statement, so using sp_executesql won't protect from sql injection attacks here. Best option is to check the parameter against a white list before injecting the database name in to the dynamic sql. — MatBailie
– MatBailie, Commented Jul 22, 2015 at 14:13
@MatBailie I suppose you are right in this case, it wouldn't matter much. But still recommended to use sp_executesql. — FutbolFan
– FutbolFan, Commented Jul 22, 2015 at 14:17

Tab Alleman · Accepted Answer · 2015-07-22 14:06:07Z

1

There IS one way you can do what you need without dynamic SQL, but it is a maintenance nightmare. You can do this:

IF @InternalSiteId = 'DatabaseA'
  SELECT TOP 1 arsDailyCtlDate  FROM DatabaseA..AccountsSetting
ELSE IF @InternalSiteId = 'DatabaseB'
  SELECT TOP 1 arsDailyCtlDate  FROM DatabaseB..AccountsSetting
ELSE IF @InternalSiteId = 'DatabaseC'
  SELECT TOP 1 arsDailyCtlDate  FROM DatabaseC..AccountsSetting

And so on for every possible database the user could choose. As you add new databases you will need to update the proc to handle them.

You're probably better off with the dynamic sql solution, but this would work.

answered Jul 22, 2015 at 14:06

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

1 Comment

kams Over a year ago

Thank you for the suggestion. I consulted my senior and yes, he said that there is no other way to do it but to use EXEC() or sp_executesql().

Collectives™ on Stack Overflow

How to dynamically select inside a procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related