Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schema dilemma


Schema dilemma

Author
Message
drahcir.nivaled
drahcir.nivaled
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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 Sad

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

Thanks,
Richard C. Delavin
Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1579 Visits: 699
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. Smile

/Kenneth



drahcir.nivaled
drahcir.nivaled
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.
Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1579 Visits: 699
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



antonio.collins
antonio.collins
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 921
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14417 Visits: 9729
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
Carl Federl
Carl Federl
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2342 Visits: 4349
"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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45518 Visits: 39948
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
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
Carl Federl
Carl Federl
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2342 Visits: 4349
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search