Dynamic SQL + temp tables?
-
Is there a way to use dynamic SQL in a stored procedure that uses (@) temp tables that can be accessed after the dynamic sql is run(i.e. have the dynamic sql insert into a temp table for use in the usp)?
set @sql = 'insert @temp(col1, col2) values(blah, blah2)'
execute(@sql)
select * from @temp
(and it has blah, blah2 inside of it)
OR is there a workaround for this type of action?
Boris
-
I would use #temp tables but I would like to order the data being inserted into the temp table (and as far as I know I must use @tables instead of #tables to accomplish this).
Boris
-
Inserting in a table yields no order (like throwing stuff all in a big bag)
Order is done via your query/stored procedure ...
-
derek - what are you trying to insert into your temp table ?! is it data from another table or something else ?!
also, if you could pl. explain why you want a temp table (as opposed to storing and retrieving from a non-temp table)?!!
**ASCII stupid question, get a stupid ANSI !!!** -
It's a long story-but I am defining a way to simplify administration of multiple applications each differently under a rule based approach.
application "pos system"
tables
pos_user_type_link(user_type_id, user_id)
pos_user_type(id, description)
application "other system"
tables
other_system_type_link(user_type_id, user_id)
other_system_type(id, description)
universal tables
applications(id, description, ...)
user(id, login_name, ....)
table i'm dealing with:
rule_table(applicationid, tablename, usercolumn, othertablename, othercolumn, otherkeyparam, otherkeydescription)
where a row in rule_table might look like:
('POS_System', 'pos_user_type_link', 'user_id', 'pos_user_type', 'user_id',
'id', 'description')
where each row in the rule table defines a relationship that the individual application uses for user types and there are other applications that use this relationship to individually define access to their individual applications. the reason i must use this rule based approach is that many applications call similar columns different names (i.e. UserID, user_id, account_user_id, etc.)
My goal here is to be able to query passing a userid, ruleid and get back all of the "user types" of that given rule and have bit columns noting if the passed user has the link to these tables. there are many applications that use this type of role approach to security and i want a way to query by "rule" to see which user types a given user account has (without having to use each individual applications admin tool or by hand writing scripts to do this). To answer sushila, I don't necessarily 'want a temp table' as that is what I see as this problem requires in a stored procedure and dynamic sql. I hope some of this makes sense to you and I really appreciate your help!
Boris
-
What I understood was that you need some sort of a "catch all" table that will list all the applications, their users, userid etc..
however, I'm not clear about why (for instance) a view cannot be created to display the columns you want from the various tables! I'm also not clear (sorry!) about how it matters if the columns are named differently as long as they all have the same datatypes and you can "join" them on "UserID", "account_user_id" etc provided they're all referencing the same ID...?!?!
maybe if you provided some sample data from each table and a sample row of the resultset you're looking for....?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
First off, thanks for helping!
From the example above's tables:
pos_user_type(id, description):
1, 'Regular POS user'
2, 'Supervisor'
3, 'Administrator'
pos_user_type_link(user_type_id, user_id):
1, 1
1, 2
1, 3
1, 4
2, 2
2, 3
3, 3
users(id, name):
1, 'joe'
2, 'beth'
3, 'julie'
4, 'paul'
I am attempting to define a way that multiple application's user types can be defined by one table (rule table). I'm not too familiar with using views, so I'm not sure if that's a good solution. I am looking at beth's(2) access page and I clicked on a pos system's rule. now I want to see:
rule that is selected has its columns specify values denoting which tables to link to the pos system
I pass ruleid 3 and userid 2 and I get back:
ruleID, roleDescription, hasRole
3, 'Regular POS User', true
3, 'Supervisor', true
3, 'Administrator', false
The reason I need that rule table is so I can say "add to rule x user type y" and it will add the row to the given table based on the rule. I would do views but I'd like a way to abstract away the details of the given tables of an application, so I can just give this rule approach to the administrator of each application and say give me the rows of what rules you want defined. Then, without having to know each application's table functionality, I use a global admin tool that controls each applications individual rules. Thanks again.
Boris
-
derek - i'll try and get back to this later on tonight but meanwhile a couple more confirmations required......
1) is your pos_user_type_link.user_id = users.id ?!
2) is the pos_user_type.id = pos_user_type_link.user_type_id ?!
3) from the sample data provided (assuming ?s 1 and 2 are answered in the affirmative) - i don't see where Beth is a 'Regular POS User'?!
4) where do you get the "hasRole = true/false" from ?!
5) do you already have a rule table or is this the table that you orginally posted about ?!
6) have you looked into defining roles and access permissions at all ?! - that way you have all your different roles with different rules and would only have to add/remove users from the various roles if anything changes...unless I've totally missed the point ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
1)Yes, which is part of the reason I am developing this rule-based approach in the first place.
2)Yes, pos_user_type.id = pos_user_type_link.user_type_id; likewise users.id = pos_user_type_link.user_id
3)There is no yes or no. The notation I used is this:
pos_user_type_link(user_type_id, user_id):
1, 1
1, 2
1, 3
1, 4
2, 2
2, 3
3, 3
Meaning that users 1-4 all are regular users (user_type_id 1), users 2-3 are type 2 also, and type 3(administrator) is only held by user_id 3(Julie).
4)Sorry, I used true/false instead of 0(false) and 1(true). The sp would return 1 (meaning the link exists) and 0(if the link does not exist). I wrote true/false for simplification sake.
5)The rule table I am talking about is what I originally posted about. The rule table basically defines a relationship between 3 tables(a "users" table, the "intermediate table" and an "other" table all within a given application). So, this rule based approach could define users to roles of an application, map users to countries, users to groups, users to regions, etc.-each with 1 row in the rule table).
6)This is exactly the issue I am trying to address: I want a way to add/remove roles to users of given applications, but without needing to write seperate stored procedures per application. I'm trying to use the rule approach so I can define a 'rule' that says "for application x, users can map to countries", then if I click on a user for a given application I can get back a list of countries and I know which countries they are mapped to(having the mapping defined in this rule). That way, I can say rule x, application y, please add user z to countryid "YourCountry", etc. This abstracts away the need for seperate administration tools per application so I can have one administration system that can edit the mapping of users to anything(based on these rules) with one app. Mostly this is supporting MANY legacy applications with bizarre mapping naming conventions so I don't want to write seperate "add user x to mapping y" stored procedures.
Hope that clarifies...thanks for the help!
Boris
-
derek - i haven't had much time to work on this...but here's something to get you started off in the right direction...I have the query you're looking for (I think!) with the 'POS' tables that you sent me - just play around and use it to work in a similar way with the other tables: - let me know if it works the way you want!
create table pos_user_type(id int, description varchar(30))
create table pos_user_type_link(user_type_id int, user_id int)
create table users(id int, name varchar(30))
insert into pos_user_type values(1, 'Regular POS user')
insert into pos_user_type values(2, 'Supervisor')
insert into pos_user_type values(3, 'Administrator')
insert into pos_user_type_link values(1, 1)
insert into pos_user_type_link values(1, 2)
insert into pos_user_type_link values(1, 3)
insert into pos_user_type_link values(1, 4)
insert into pos_user_type_link values(2, 2)
insert into pos_user_type_link values(2, 3)
insert into pos_user_type_link values(3, 3)
insert into users values(1, 'joe')
insert into users values(2, 'beth')
insert into users values(3, 'julie')
insert into users values(4, 'paul')
SELECT PT.description AS roleDescription, CASE
WHEN A.hasRole IS NULL THEN 'False' ELSE 'True' END hasRole
FROM
pos_user_type PT
LEFT OUTER JOIN
(SELECT user_type_id AS hasRole FROM pos_user_type_link PL INNER JOIN users U ON PL.user_id = U.id
AND U.id = 2)A
ON
PT.id = A.hasRole
**ASCII stupid question, get a stupid ANSI !!!** -
That bottom query would be what the stored procedure generates, but it would dynamically generate that query based on the rule passed to the stored procedure. I think I can figure it out from here-thanks a bunch!!!
Boris
-
you would pass an @userid variable and your query would change to:
SELECT PT.description AS roleDescription, CASE
WHEN A.hasRole IS NULL THEN 'False' ELSE 'True' END hasRole
FROM
pos_user_type PT
LEFT OUTER JOIN
(SELECT user_type_id AS hasRole FROM pos_user_type_link PL INNER JOIN users U ON PL.user_id = U.id
AND U.id = @userid)A
ON
PT.id = A.hasRole
i just didn't see where you used your rule since you essentially seemed to want to get back all the rules and only specify true or false for each of them based on userid...in the expected results you sent in a previous post:
ruleID, roleDescription, hasRole
3, 'Regular POS User', true
3, 'Supervisor', true
3, 'Administrator', false
anyway, so long as you can get it to work from here on that's what matters!
**ASCII stupid question, get a stupid ANSI !!!** -
Yeah, it works like a champ! Thanks again sushila
Boris
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply