Is this possible

Question

Post reply

Is this possible

Hemant Kumar

Ten Centuries

Points: 1063
More actions
August 6, 2005 at 3:43 am

#94396

Hello everyone,
I would like to know if this is possible
DECLARE @USR VARCHAR(50)
SET @USR = USER
EXEC @USR + '.TRUE_VALUE()
Here dbo.TRUE_VALUE() is a user-defined function. Why i want this behaviour is because we have 3rd party people who give us space on their sql server with different ID. And if that changes, I need to change all the locations where i have used hard-coded dbo.
Or otherwise, Is there is any other behaviour to work around this
August 9, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

No, you can't reference objects dynamically that way.

/Kenneth

Hemant Kumar Ten Centuries Points: 1063 More actions · Answer 2

Oh OK,

Any other work around to handle this situation easily can really be a great help for me

Regards,

Hemant

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You'd have to code everything with dynamic sql on the server anyways to do this. It might be easier to do it client side. Unless someone has some mapping trick with the users???

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

I didn't mention dynamic SQL, simply because it isn't 'an easy' way...

But, to gather an opinion of oneself, please go to http://www.sommarskog.se/ and read the article entitled "The curse and blessings of dynamic SQL."

/Kenneth

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

The easy way would be a mapping of some kind... probabely possible but I just got no clue to where to even start looking. The dynamic sql client side wouldn't be too bad. But then again you lose many advantages of the procs doing that.

noeld SSC Guru Points: 96590 More actions · Answer 6

Well I think there is a way

M$ uses this all the time in their replication procedures which are all over the place in the publisher, distribution and subscriber databases.

you can do this:

declare @procname sysname, @paramvalue int, @retvalue int

select @procname = 'databasename'+ '.' + 'ownername' + '.' + 'procedurename'

, @paramvalue = 0 -- dummy example parameter

, @retvalue = 0 -- dummy example returnvale

exec @retvalue = @procname @paramname

* Noel

VladRUS.ca SSC Enthusiast Points: 127 More actions · Answer 7

Hi, Hemant Kumar

Here dbo.TRUE_VALUE() is a user-defined function. Why i want this behaviour is because we have 3rd party people who give us space on their sql server with different ID. And if that changes, I need to change all the locations where i have used hard-coded dbo.

Why you should change dbo.?

-- login as "sa"

use tempdb

go

if object_id('fn_test', 'FN') > 0 drop function fn_test

go

create function fn_test()

returns sysname

as

begin

return SUSER_SNAME()

end

go

grant execute on fn_test to public

go

select dbo.fn_test()

go

-- login as 'test_user'

use tempdb

go

select dbo.fn_test()

go