sql - How to dynamically select inside a procedure -


i have dilemma creating procedure uses table database inside it. say:

create procedure uspretrievecurrentpropertydate ( @internalentityid varchar(10) ,@internaluserid varchar(10) ,@internalsiteid varchar(10) --this database need table ) begin ... ... select top 1 arsdailyctldate @internalsiteid..accountssetting end 

but of course return error.

the original script uses like:

set @csql = 'select top 1 arsdailyctldate  s' + @siteid + '.dbo.accountssetting (nolock)'  exec(@csql)  

to accomplish task. wanted rewrite code. there anyway can way done? without using exec(@csql)?

thanks, sherwin

there 1 way can need without dynamic sql, maintenance nightmare. can this:

if @internalsiteid = 'databasea'   select top 1 arsdailyctldate  databasea..accountssetting else if @internalsiteid = 'databaseb'   select top 1 arsdailyctldate  databaseb..accountssetting else if @internalsiteid = 'databasec'   select top 1 arsdailyctldate  databasec..accountssetting 

and on every possible database user choose. add new databases need update proc handle them.

you're better off dynamic sql solution, work.


Comments

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

sql - MySQL query optimization using coalesce -

unity3d - Unity local avoidance in user created world -