Pass environment name and database as a parameter

[Chumley40]

I want the users to choose a database @Environment. Then the applications plugs in the current servername + .dbo. + @environment + tablename.

How do I do that? Her is my case statement that I want it done in:
Case When detailobjectvalue like '%^MRN' then
(select top 1 cv.clientdisplayname from
(@MasterSQLServerName + '.dbo.' + @Environment + 'dbo.' + CV3Clientvisit) cv where cv.idcode = Replace(detailobjectvalue, '^MRN','') )
else ' '
end as clientdisplayname,

I have defined @Environment as a param and it passes in fine. My @MasterSQLServerName works fine retrieving the servername--It is stringing it together in a way that sql will read it where I have a problem.

Thanks in advance
TJ.

 

[philhege]

SQL statements can't take a variable in the object hierarchy. You'd have to issue a dynamic SQL statement to make this work.

declare @SQL varchar(max)
set @SQL = 'select top 1 cv.clientdisplayname from 
'+@MasterSQLServerName + '.dbo.' + @Environment + 'dbo.CV3Clientvisit) cv where cv.idcode = Replace(detailobjectvalue, ''^MRN'','''')'
EXEC (@SQL)

-------++NO CARRIER++-------

 

[philhege]

Oh, and

SERVER.DATABASE.OWNER.OBJECT

should be the correct hierarchy.

-------++NO CARRIER++-------

 

[Chumley40]

The problem seems to come from plugging it into the case statement. When I follow your suggestion, I still error, but I think it is the way it is cased not the statement itsself.

Case When detailobjectvalue like '%^MRN' then
(Exec @SQL)
else ' '
end as clientdisplayname,

 

[Chumley40]

If I run just the @sql , and replace the detailobjectvalue with ''it runs without error. The field detailobjectvalue is from the main stored proc, So I wonder, can I call a table from outside the @sql or is that just not possible?

 

[djj55]

If the table is a variable name no you cannot call it outside of @sql.

You can create tables with the execute command and access that.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Qik3Coder]

Chumley40,

Sanity Check Moment:
You need to think clearly about where you're generating and executing your SQL statements from. If you are going so far as to allow different server names, then each database that can be connected to will need to have access to all of your possible other servers (i.e. Linked Servers, i.e. Audit/Security Risk). If this is really a "self" (read server) contained script, and it will always be plugging in whichever server it happens to be on, then just be aware what you're plumbing in.

Supporting comment:
I have worked with an environment similar to this. Instead of leaving them as variables to be passed in, and up to the application to figure out which db to attach to, we created a dynamic sql statement, which would put the appropriate names in the stored procedure when it was created on the database. This removes the dynamic sql, let's us manage a single SQL script, and the target Server/DB is based off a value in a "Environment" table.

The reason we had to do this was because our dev/test/prod database all had different names, that couldn't be changed (Lots of Dumb)

Lodlaiden


You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)