access rights - dynamic reports

hi,
is it possible to make "dynamic reports" for users with different user
rights. depending on their rights one user sees all the columns and
another one with less rights sees just the first column?
thanksthere probably is ... but since i can't tell you i'll give you an
alternative solution - why don't you just create 2 reports
one for general users containing data that general people can see
one for 'special' users containing all of the 'secret' data. because
more than likely once the 'special' users see what they can get,
they're going to want more and it'd be easier to manage the reports on
a group basis rather than a more granular column by column basis.
those are my thoughts anyway ...
hth!

access result of "dynamic sql query" via transact sql

He,
want i want to do ist creating a dynamic query, execute it and access
the result via transact-sql.
e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE ..
The first thing i tried was to use dynamic sql in udf's, but i realised
very fast, that this wont work.
After that I tried to build the query in a stored procedure but i can't
return the result set to a function or use it in an sql statement (like
SELECT * FROM (exec sp...)). I also tried it with temporary tables but
i also can't access them via userdefined functions. And i can't use
static names for the temp-Tables or even let the user exec the stored
procedure itself, because the user should not see how the whole thing
is working. He should just type "SELECT * FROM [function name]" and not
more.
So if somebody knows how to solve this problem .. please tell me
Thanks,
stephansteph
If I understood you correctly
CREATE TABLE #T
(
col INT
)
INSERT INTO #T EXEC myStoredProcedure
"steph" <stephan@.aiche.info> wrote in message
news:1125924583.250239.32680@.o13g2000cwo.googlegroups.com...
> He,
> want i want to do ist creating a dynamic query, execute it and access
> the result via transact-sql.
> e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE ..
> The first thing i tried was to use dynamic sql in udf's, but i realised
> very fast, that this wont work.
> After that I tried to build the query in a stored procedure but i can't
> return the result set to a function or use it in an sql statement (like
> SELECT * FROM (exec sp...)). I also tried it with temporary tables but
> i also can't access them via userdefined functions. And i can't use
> static names for the temp-Tables or even let the user exec the stored
> procedure itself, because the user should not see how the whole thing
> is working. He should just type "SELECT * FROM [function name]" and not
> more.
> So if somebody knows how to solve this problem .. please tell me
> Thanks,
> stephan
>|||this would work, but i think it won't work in a udf. but i need to do
it with a udf becaus my users just want to type
SELECT * FROM ... and not
CREATE TABLE #T
(
col INT
)
INSERT INTO #T EXEC myStoredProcedure
SELECT * FROM #T
So is there any possibilty to do it with a udf ?|||steph
INSERT INTO #T SELECT <columnsd> FROM dbo.UDF does not work?
"steph" <stephan@.aiche.info> wrote in message
news:1125925509.599913.104850@.g43g2000cwa.googlegroups.com...
> this would work, but i think it won't work in a udf. but i need to do
> it with a udf becaus my users just want to type
> SELECT * FROM ... and not
> CREATE TABLE #T
> (
> col INT
> )
>
> INSERT INTO #T EXEC myStoredProcedure
> SELECT * FROM #T
> So is there any possibilty to do it with a udf ?
>|||INSERT INTO #T SELECT <columnsd> FROM dbo.UDF does not work?
not this way,
it won't work this way
create function dbo.udf ..
returns table
exec sp_creating_temp_table
return (select * from #created_temp_table)
so the user just have to type "SELECT * FROM dbo.udf WHERE .. "|||Please explain your requirement more fully and I'm sure someone can
suggest a better way. It isn't clear to me exactly why you want to do
this. Why can't you just write a query or create a view?
David Portas
SQL Server MVP
--|||I want to do a preselection like "SELECT * FROM ( dbo.udf(@.table_name,
@.other_param) ) WHERE ..." to accelerate the query. So i want to pass
the table name and the preselection params to the udf and the udf
returns the result set. The problem is i got to do some caltculations
for the preselection and then build the preselect query with the
calculated values and i think in this case a view or a selfwritten
query wont work ...
thanks
stephan|||Why not use a parameterized stored procedure? And by the way,
parameterizing table names is a really, really bad idea - and totally
unnecessary in a well-designed system.
David Portas
SQL Server MVP
--|||You didn't explain why you can't use a view or subquery. You can't use
dynamic code in a function.
Have you seen:
http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/dyn-search.html
Without more information all I can suggest is that you should review
your overall design - it sounds like a pretty odd setup to me. Have you
looked at middleware and BI tools?
David Portas
SQL Server MVP
--|||Stephan,
Can you explain what this "preselection" is (preferably with specific
examples - see http://www.aspfaq.com/etiquette.asp?id=5006).
In a well-designed database, it should not be necessary to jump
through hoops in order "to accelerate the query", whatever that
means.
Then again, if when you say "tables are dynamic," you mean
that you never know what tables exist at a given time, I think you
are in bigger trouble than if you were missing some indexes. I have
never seen a design that created and dropped tables willy-nilly that
was not little more than a huge mess.
Asking clear questions about a system like this is like asking
"What color is a chameleon?" Trying to manage one is like
trying to make clothes for amoebae. Nothing fits for more
than a few moments.
Steve Kass
Drew University
steph wrote:

>I already tried to use "parameterized stored procedure" but i can't
>access the result of a sp via t-sql so it won't work for a
>preselection.
>
>
>I know that it is not the best idea, but the tables in the db are
>dynamic, and also i want to use the functionality for more than one
>table and more then one db.
>thanks
>stephan
>
>

Access parameters within dynamic SQL

I am exploring the use of dynamic SQL within a stored procedure and have run
in to a problem. The dynamic SQL has no visibility of variables declared
outside the dynamic SQL. Try this snippet which causes an error:
declare @.branch int
set @.branch = 10
exec ( 'select @.branch_no' )
Is there any way to make these variables visible to the dynamic sql without
concatenation?
The reason why this will be a problem is that I will be use the openxml
command within the dynamic sql. I will be using very large XML strings so I
am pretty sure that I will have problems concatenating XML strings with
dynamic sql statements.
Any ideas?
McGy
[url]http://mcgy.blogspot.com[/url]> The reason why this will be a problem is that I will be use the openxml
> command within the dynamic sql. I will be using very large XML strings so
> I
> am pretty sure that I will have problems concatenating XML strings with
> dynamic sql statements.
As long as each string is <= 8000 characters (or 4000 characters with
Unicode), you can say EXEC(@.sql1 + @.sql2 + @.sql3);
A|||Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
example which explains how to pass & return values from such strings.
--
Anith|||Great that was really useful. I have combined a couple of examples (openxml
and sp_executesql) from books online in the snippet below to show how XML
can be passed in as a parameter to dynamic sql:
DECLARE @.SQLString NVARCHAR(500)
/* Build the SQL string */
SET @.SQLString =
N'
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, ''/ROOT/Customer'',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @.idoc'
/* Execute the string */
EXECUTE sp_executesql @.SQLString, N'@.doc text',
@.doc = '<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
McGy
[url]http://mcgy.blogspot.com[/url]
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#d272ckPGHA.740@.TK2MSFTNGP12.phx.gbl...
> Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
> example which explains how to pass & return values from such strings.
> --
> Anith
>|||>> am exploring the use of dynamic SQL within a stored procedure and have ru
n
in to a problem. <<
As well you should!! This is an awful way to even think of writing
code of any kind. Remember coupling, cohesion and all that stuff in
your fist Software Engineering course?
So you want on-the-fly, mixed, proprietary languages so you can
manipulate XML with T-SQL? This whole thing sounds like a pile of
kludges, but without better specs we can only guess at a relatioanl
solution.|||The reason for dynamic SQL is that I need to parameterize the database name
in the queries. We have a database with 20 odd tables with exactly the same
structure - so rather than duplicating the stored procedure 20 odd times I
am looking at writing it once with dynamic SQL.
The reason for XML is so that I can send large batches of data at a time to
the stored procedure. Which is very efficient.
I am not sure that I will use this technique but it is certainly one of
several I am considering. Its not a position I relish being in but that's
the way the database is so more likely than not I will have to work with its
shortcomings.
See another post by my titled "Parameterize table name without constructing
dynamic query?"
McGy
[url]http://mcgy.blogspot.com[/url]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141350534.964106.45490@.t39g2000cwt.googlegroups.com...
> in to a problem. <<
> As well you should!! This is an awful way to even think of writing
> code of any kind. Remember coupling, cohesion and all that stuff in
> your fist Software Engineering course?
>
> So you want on-the-fly, mixed, proprietary languages so you can
> manipulate XML with T-SQL? This whole thing sounds like a pile of
> kludges, but without better specs we can only guess at a relatioanl
> solution.
>