SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schema dilemma


Schema dilemma

Author
Message
drahcir.nivaled
drahcir.nivaled
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 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
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6745 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
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 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
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6745 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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2218 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
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57787 Visits: 9730
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
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6892 Visits: 4352
"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 Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215178 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 6264 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
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6892 Visits: 4352
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