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
Post a Comment