Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Schema dilemma Expand / Collapse
Author
Message
Posted Friday, May 9, 2008 5:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 7, 2009 5:19 AM
Points: 7, Visits: 70
Hi,

I'm really having a hard time with the problem that I'm encountering, so this is my very first time to ask something in any forum.
I guess this is the best sql forum that I've been to. Anyway, here is my dilemma.

Inside my SP:
IF EXISTS (select topiccode from topic_status where topiccode = @topiccode and status = @status)
begin
delete from topic_status ...;
insert into topic_status ...;
update topic_status ...;
end

Above is actually a simple query if you notice, but now I was told that we need to add the schemaname and incorporate it in every command. The schemaname will be of course an input parameter.

with schema:
IF EXISTS (select topiccode from [companyA].[topic_status] where topiccode = @topiccode and status = @status)

@schemaname varchar(25)


So basically, to add the @schemaname, I need to put the command in string before executing the said command, easy? well, not really for me:


declare @sql varchar(max) -- I need this to run the if exists command

select @sql = 'IF EXISTS (select topiccode from [' + @schemaname + '].[topic_status] where topiccode = ''' + @topiccode + ''' and status = ''' + @status + ''') print 1 else print 0'

exec(@sql)

Of course this will run, but how can I get the value of 1 if it exists and 0 if it doesn't.

Using "print' will just write the value, I've tried using "return" but it doesn't work and even instead of using
the "print/return" I tried "set @returnval = 1 else set @returnval = 0"; which returns again an error.

I also tried this:
set @returnval = exec(@sql) -- of course it wont work

Is there any workaround this problem? Thank for your time and effort. Please help.

Thanks,
Richard C. Delavin

Post #497753
Posted Friday, May 9, 2008 5:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672
Maybe a silly question, but why on earth does schema need to be an input parameter?

This is exactly the same as saying the table name must be an input parameter..
..or the columnname.. etc..

The schemaname is part of the object's qualified name, and shouldn't be needed to be handled as a parameter. If you could get rid of this requirement, then the whole thing would become much easier for you. :)

/Kenneth



Post #497766
Posted Friday, May 9, 2008 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 7, 2009 5:19 AM
Points: 7, Visits: 70
Because the database will be handling different companies, with their own schema. They actually wants a multi-tenant DB but the problem is that they don't want specific "logins".

Thats why I'm having this dilemma.
Post #497812
Posted Friday, May 9, 2008 7:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672
Well, there may be more problems down this path than perhaps is apparent.
What you're looking at is extensive use of dynamic SQL. This is a real Pandoras Box.

In order to get some insight on what's ahead, please do read this article.
http://sommarskog.se/dynamic_sql.html

/Kenneth



Post #497883
Posted Friday, May 9, 2008 9:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 8:41 AM
Points: 438, Visits: 908
although you may no like them, seperate logins are a simple solution to this problem. give them all the same passwod and let your app connect as the company's user and implicitly access their schema-scoped objects. objects that are common for all companies would remain part of the dbo schema.
Post #498036
Posted Friday, May 9, 2008 2:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
The only way to do exactly what you're asking is dynamic SQL. That opens up all kinds of serious security and other issues. Might want to reconsider the whole separate logins thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #498227
Posted Friday, May 9, 2008 2:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,281, Visits: 4,225
"Because the database will be handling different companies, with their own schema."
That is the wrong solution to the problem. Have one database for each company and each login has access to only one database.

Be sure to setup a separate ODBC name and DNS aliases for each company and so that if you decide to move one of the database to a different server, only the IP of the DNS alias needs to change.


SQL = Scarcely Qualifies as a Language
Post #498233
Posted Friday, May 9, 2008 5:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Would "context switching" work for you? Here's the example from Books Online...

-- Switch execution context to the dan1 user account.
EXECUTE AS USER = 'dan1';
-- Execute the DBCC statement.
DBCC CHECKDB ('AdventureWorksDW');
-- Revert to the previous execution context.
REVERT;



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #498285
Posted Friday, May 9, 2008 10:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:19 PM
Points: 2,278, Visits: 3,059
This is not leading down a path of success and successful implementation. At minimum you should have logins that segregate schemas. This way you can maintain objects for each company. What happens if a customer wants a stored procedure to operate differently than the one everyone else is using? Then your dynamic solution will not work and you will have to create a new procedure anyway.

The best solution is to have different databases for each company, or as stated before a default schema set for each company login. This allows for seperation of objects and increased security, and allows for greater manageability.




My blog: http://jahaines.blogspot.com
Post #498320
Posted Saturday, May 10, 2008 9:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,281, Visits: 4,225
Tried Jeff Moden's suggestion to use "context switching" but I cannot get this to work based on three schemas and a dbo schema stored procedure, where the schema of the table is not specified. Any suggestions?

EXECUTE AS USER = 'CarlFederl1'
select * from Foo
exec dbo.foo_list
revert
go
Output is:

name
------
ONE

(1 row(s) affected)

Msg 208, Level 16, State 1, Procedure foo_list, Line 2
Invalid object name 'foo'.


-- Reproduction SQL statements
-- create database and logins not included.
USE CarlFederl
GO
create schema Schema1 authorization dbo;
go
create schema Schema2 authorization dbo;
go
create table Schema1.Foo
(name varchar(255) not null );
create table Schema2.Foo
(name varchar(255) not null );
CREATE USER CarlFederl1 FOR LOGIN CarlFederl1 WITH DEFAULT_SCHEMA=Schema1;
CREATE USER CarlFederl2 FOR LOGIN CarlFederl2 WITH DEFAULT_SCHEMA=Schema2;
insert into Schema1.Foo (name) values ('ONE');
insert into Schema2.Foo (name) values ('TWO');
go
create procedure dbo.foo_list as
select * from foo
go
grant execute on dbo.foo_list to CarlFederl1 , CarlFederl2;
grant select on schema1.foo to CarlFederl1 ;
grant select on schema2.foo to CarlFederl2;
go



SQL = Scarcely Qualifies as a Language
Post #498376
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse