sql dwh problem: procedure

Showing posts with label procedure. Show all posts

Thursday, March 29, 2012

Dynamic Security Stored Procedure Repeatedly Called

I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?Do you have more than 10 attributes in this dimension ? (i.e. number of calls to sproc should be the same as number of attributes in dimension).|||

Thank you. I don't quite follow. The dimension is wide (it has some 30 attributes). However, only one attribute hierarchy has an allowed set filter defined. Why does this need to be evaluated many times?

In addition, the dimension is large (some 2 mil plus members). Even with allowed set caching inside the stored procedure, it takes an enormous amount of time (some 10 min) for the filter to be applied, e.g. if the filter returns some 40,000 allowed members. Any optimization tips?

|||10 minutes to filter 40,000 members out of 2 million is way too much. I've written sprocs which were able to do comparable filtering in a matter of seconds. Of course, it depends on the logic inside sproc. Would you share more details please - what is the criteria for the filtering ?|||

Thank you for helping out.

As I mentioned, the dimension has some 2 mil members. I has a Security Filter attribute with some 50,000 members on which the allowed set is applied. The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

> The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

This is not a good practice. Much better approach is to use Server Adomd.NET object model and return AdomdServer.Set from sproc instead of giant string. But since you query database table, I wonder whether you can make this table part of UDM and build joins inside UDM and avoid sproc altogether...

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Since it seems that the information about which users can see which members is already stored in the table, you could build an aux measure group with User dimension and your dimension included, and then use something like

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

|||Thank you. But this will result in a very large fact table which will hold the allowed members for each user, corrrect? So, if I have 100 users and each user is allowed to see 100,000 members on average, the table will have 10,000,000 rows. I wonder how both approaches compare from a performance standpoint. Do you have any performance test results to share?|||Before we can compare the approaches - can you explain how the database table used by sproc looks like ? Doesn't it have the same structure (i.e. for user you need to be able to get list of allowed members), and therefore same size ?|||

To make the things simpler, let's assume that that in both cases the security policy will be materialized into the same table (dimension surrogate key, employee surrogate key). In the case of dynamic security, the stored procedure will query this table by user and apply the allowed set but the allowed set will not exceed the maximum number of members that the user can access. In the case of the security measure group, EXISTS can be potentially applied over millions of records. As the securiy table gets larger, I'd expect the benefits of EXISTS to level off. Or, will it?

|||Please note, that the call to Exists is always filtered by the current User, therefore there will never be a full scan of this measure group. I.e. if this measuregroup was implemented as pure ROLAP - it would generate exactly same SQL queries as you are likely doing from your sproc already. And, of course, in case of MOLAP it will be much more efficient. Therefore I beleive that the approach with EXISTS will always outperform approach with sproc no matter what sizes of tables will be.|||Thank you so much for you help, Mosha. I will post to this thread if I find otherwise.|||I think it will be very interesting to everybody to see results of your testing in either scenario. While I have seen approach that I recommended working well in number of installations, your data volumes (100 users each one having access to different set of 100,000 members out of 2 million) are interesting enough to see how well it will perform in the real world. My prediction is that it should perform well, but it is only a feeling without hard data behind it.|||

Got results. The materialized results are impressive! For the sake of testing, I generated all permutations of 250 employees with 52,000 securable items resulting in a fact table with some 13 mil rows. I compared this against the dynamic security approach where 52,000 securable items are converted to a Set. I tested the connect time from Excel and noted the query log events in the Profiler.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Not to mention that the dynamic security test currently excludes the application latency (records are read directly from a table). In real life, it would take additional time for stored procedure/service to prepare the security filter.

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

Dynamic Security Stored Procedure Repeatedly Called

Thank you for helping out.

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

Dynamic Security Stored Procedure Repeatedly Called

Thank you for helping out.

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

sql

Dynamic reports

Yooo... I'm trying to build a dynamic report with Reporting Services. The problem is that I have a stored procedure that returns a different number of columns with different name for the columns almost each time. So... how can I get dynamic the number and the name of the columns at runtime.

He is an example of the SP:

CREATE PROCEDURE [dbo].[Test]
@.nrCol INT
, @.CarCol CHAR(5)
AS
CREATE TABLE #Part(DenPart CHAR(10))
DECLARE @.i INT
SET @.i = 0
WHILE @.i < @.nrCol
BEGIN
EXEC('ALTER TABLE #Part ADD [' + @.CarCol + @.i + '] NUMERIC(18,2) NOT NULL DEFAULT(0)')
SET @.i = @.i + 1
END
INSERT INTO #Part (DenPart) VALUES('A')
INSERT INTO #Part (DenPart) VALUES('B')
SELECT * FROM #Part

Any ideeas?

Thanks

I wont think we can use the above stored procedure. To develop a report we need to have a result set at design time. Report need to know what are the data fields at design time.sql

Tuesday, March 27, 2012

Dynamic Report Columns..?

I have a stored procedure that outputs a dynamic resultset with an unknown number of columns...

So, the resultset might look like:

RowId 03/16/2006 03/17/2006 03/18/2006 03/19/2006
Run of House 02 10 20 35
Suites 05 30 05 15

Or it could look like:

RowId 03/16/2006 03/17/2006
Run of House 02 10
Suites 05 30

Or perhaps:

RowId 03/16/2006 03/17/2006 03/18/2006 03/19/2006 03/20/2006 03/21/2006 03/22/2006
Run of House 02 10 20 35 10 15 45
Suites 05 30 05 15 05 10 80

I need a way that I can run a report that displays these results the from the stored procedure without having to know the number of columns. Is this possible..? If so, can you explain how this can be done in SSRS...

Thanks

the matrix component should fulfill your needs

its like a table but expanding horizontally
but its also a bit more tricky, as you will see

greets
gerhard

Dynamic Query!

I am trying to create a stored procedure containing a dynamic query.
I am still new to using conditionals in sql, so any help to
get this query running would be appreciated!!!
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = NULL,
@.from_dt SMALLDATETIME = NULL,
@.to_dt SMALLDATETIME = NULL
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
IF @.name IS NOT NULL
first_name + ' ' + last_name LIKE @.name
IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
AND start_dt >= from_dt AND <= to_dt
ELSE
start_dt >= from_dt AND <= to_dt
ENDHi
Just check if this solves the purpose
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = NULL,
@.from_dt SMALLDATETIME = NULL,
@.to_dt SMALLDATETIME = NULL
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
CASE WHEN @.name IS NOT NULL
first_name + ' ' + last_name LIKE @.name
CASE WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
start_dt >= from_dt AND <= to_dt
ELSE
start_dt >= from_dt AND <= to_dt
END
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"AJ" wrote:

> I am trying to create a stored procedure containing a dynamic query.
> I am still new to using conditionals in sql, so any help to
> get this query running would be appreciated!!!
> CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
> AS
> BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> IF @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
> AND start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
> END|||Well you could approach it very simplistically and just replace all your
different IF cases with OR operations (because that's what they really
are) like this:
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE (@.name IS NOT NULL and first_name + ' ' + last_name LIKE @.name)
OR (@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR (start_dt BETWEEN @.from_dt AND @.to_dt)
You could shuffle the WHERE clause around a bit but chances are the
query optimiser will come up with the same plan for the majority of the
variations so you may as well stick to something that you understand and
that's readable (so those who maintain the system after you aren't
bamboozled by your code).
I changed your "<= AND >=" bits to "BETWEEN" because it's a little more
readable IMHO. Also I noticed you left off the '@.' symbol on a couple
references to your proc parameters in the WHERE clause (to_dt & from_dt).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AJ wrote:

>I am trying to create a stored procedure containing a dynamic query.
>I am still new to using conditionals in sql, so any help to
>get this query running would be appreciated!!!
>CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
>AS
>BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> IF @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
> AND start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
>END
>|||Hmmm... The CASE statement is wrong. I think you mean
WHERE
CASE
WHEN @.name IS NOT NULL
THEN first_name + ' ' + last_name LIKE @.name
WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
THEN start_dt >= @.from_dt AND start_dt <= @.to_dt
ELSE
start_dt >= @.from_dt AND start_dt <= @.to_dt
END
But I'm not sure that would work even. BOL says the bit after THEN can be a
ny valid SQL expression but I don't know if "x LIKE y" or "a >= x and a <= b
" are valid in this context (even though they just resolve to a boolean, whi
ch I guess is a valid SQL e
xpression) - never tried that before. At the very least it's a little unort
hodox. Typically a CASE is used to return a specific value to compare somet
hing to like this
WHERE MyCol = (CASE
WHEN a THEN SomeVal
WHEN b THEN SomeOtherVal
ELSE SomeCatchAllVal
END)
Also, the middle case is redundant because it's the same result as the ELSE
case. You could simply write it as
WHERE
CASE
WHEN @.name IS NOT NULL
THEN first_name + ' ' + last_name LIKE @.name
ELSE
start_dt >= @.from_dt AND start_dt <= @.to_dt
END
However, you could get rid of the CASE statement entirely by saying
WHERE first_name + ' ' + last_name LIKE @.name
OR start_dt BETWEEN @.from_dt AND @.to_dt
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Chandra wrote:

>Hi
>Just check if this solves the purpose
>CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
>AS
>BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> CASE WHEN @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> CASE WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
> start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
> END
>END
>
>|||Hi all, so far i have adopted the following approach.
If no parameters are supplied i want all records to be selected.
At the moment this isn't catered for in the query below.
My overall logic is:
If @.name is provided filter results with @.name
If @.from_dt is provided filter results with @.from_dt
If @.name and @.from_dt are provided filter with both.
@.if no parameters are provided just select all records.
Can anyone give some modifications to this query to achieve this?
Thanx...
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
(@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
OR
(@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR
(@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
ORDER BY
last_name|||Sounds like you're trying to do this:
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = '%',
@.from_dt SMALLDATETIME = '19000101',
@.to_dt SMALLDATETIME = '20790606'
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE first_name + ' ' + last_name LIKE @.name
AND start_dt >= @.from_dt
AND start_dt <= @.to_dt
END
This goes along the lines of factor in each parameter in the where
clause but if no value is passed into the proc for each specific
parameter then a default value will be used, for each parameter, such
that it won't limit the resultset at all (ie. any @.name string, the min
@.from_dt value and the max @.to_dt value for the datatypes you chose).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AJ wrote:

>Hi all, so far i have adopted the following approach.
>If no parameters are supplied i want all records to be selected.
>At the moment this isn't catered for in the query below.
>My overall logic is:
>If @.name is provided filter results with @.name
>If @.from_dt is provided filter results with @.from_dt
>If @.name and @.from_dt are provided filter with both.
>@.if no parameters are provided just select all records.
>Can anyone give some modifications to this query to achieve this?
>Thanx...
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> (@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
> OR
> (@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
>@.from_dt AND @.to_dt)
> OR
> (@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
> ORDER BY
> last_name
>|||One small caveat to Mike's excellent suggestion - the technique he proposes
requires that none of the columns (first_name, last_name, start_dt, and
end_dt columns) be nullable. If any of the rows contains nulls in one or
more of those columns, then you will not match that row. Since you did not
post any DDL, we do not know the details of the table being searched, so
this may or may not apply in your case. In the future, please post DDL and
sample data so you have the best chance of receiving the most complete
answer possible.
Erland Sommarskog has a great article about dynamic search criteria at:
http://www.sommarskog.se/dyn-search.html
ITHT
Jeremy Williams
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%23xxsVZmaFHA.3400@.tk2msftngp13.phx.gbl...
Sounds like you're trying to do this:
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = '%',
@.from_dt SMALLDATETIME = '19000101',
@.to_dt SMALLDATETIME = '20790606'
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE first_name + ' ' + last_name LIKE @.name
AND start_dt >= @.from_dt
AND start_dt <= @.to_dt
END
This goes along the lines of factor in each parameter in the where clause
but if no value is passed into the proc for each specific parameter then a
default value will be used, for each parameter, such that it won't limit the
resultset at all (ie. any @.name string, the min @.from_dt value and the max
@.to_dt value for the datatypes you chose).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
AJ wrote:
Hi all, so far i have adopted the following approach.
If no parameters are supplied i want all records to be selected.
At the moment this isn't catered for in the query below.
My overall logic is:
If @.name is provided filter results with @.name
If @.from_dt is provided filter results with @.from_dt
If @.name and @.from_dt are provided filter with both.
@.if no parameters are provided just select all records.
Can anyone give some modifications to this query to achieve this?
Thanx...
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
(@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
OR
(@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR
(@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
ORDER BY
last_name|||No, SQL has a CASE expression **not** a CASE statement. Big
difference! Expressionds return a value: they do not control flow of
control.|||Exactly what I was getting at (did you read my whole post?).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
--CELKO-- wrote:

>No, SQL has a CASE expression **not** a CASE statement. Big
>difference! Expressionds return a value: they do not control flow of
>control.
>
>

Dynamic query with SQL statement longer than 8000 chars

I have a stored procedure generating a dynamic SQL that runs against Analysi
s
Services. The statement can be quite complicated and can be longer than 8000
.
I know I can use "exec (@.strSQL1 + @.strSQL2)" but since I don't know how man
y
@.strSQL variables I'll need, I was wondering if anybody has a better solutio
n
for this.
If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
that resembles a procedure in VB? I don't think UDFs would work here.
Thanks in advance for any help,
Carmen.Carmen (Carmen@.discussions.microsoft.com) writes:
> I have a stored procedure generating a dynamic SQL that runs against
> Analysis Services. The statement can be quite complicated and can be
> longer than 8000. I know I can use "exec (@.strSQL1 + @.strSQL2)" but
> since I don't know how many @.strSQL variables I'll need, I was wondering
> if anybody has a better solution for this.
Upgrade to SQL 2005, where you can use varchar(MAX) and you are free
of all hassle.

> If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
> that resembles a procedure in VB? I don't think UDFs would work here.
If it's that bad, it may be better to build the query that has better
string capabilities, for instance VB.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Which parts of your SQL statement are dynamic? Is it possible that some of
your dynamic SQL could be done another way? Perhaps using a temp table in
place of a list for an in clause, or splitting parts of the dynamic SQL off
into views? I can only guess at what you are doing but I have to believe
that if you are getting over 16000 characters in your SQL that there must be
ways to simplify it and make the final SQL more concise.
Sometimes we get so concerned with making the current process work as
originally designed, and we overlook that there are flaws in the design.
Once we redesign the process to remove those flaws we find out that what we
thought was a problem was really only a symptom of another problem. This
may or may not be the case here, but it is worth considering nonetheless.
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:A7D9B446-D779-4A8B-8823-5701137660C3@.microsoft.com...
> I have a stored procedure generating a dynamic SQL that runs against
Analysis
> Services. The statement can be quite complicated and can be longer than
8000.
> I know I can use "exec (@.strSQL1 + @.strSQL2)" but since I don't know how
many
> @.strSQL variables I'll need, I was wondering if anybody has a better
solution
> for this.
> If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
> that resembles a procedure in VB? I don't think UDFs would work here.
> Thanks in advance for any help,
> Carmen.sql

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
Chris
Have a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
sql

Dynamic query to store record count in variable??

Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris

dynamic query problem

I make a sp in mt sql server,

the SP get one parameter, its the WHERE,
and then I execute the query,

here is my SP:
ALTER PROCEDURE [dbo].[rptEventToPsy]
@.strSQL nvarchar(4000)=''
as
DECLARE @.SQLString NVARCHAR(4000);
SET @.SQLString = N'SELECT
dbo.tbl_CA_meeting.userID, dbo.tblUsersName.OrdName, COUNT(dbo.tbl_CA_event.itemInPackageID) AS Expr1, dbo.tbl_CA_itemInPackage.itemInPackageName, dbo.tbl_CA_package.packageName FROM dbo.tbl_CA_meeting INNER JOIN
dbo.tbl_CA_event ON dbo.tbl_CA_meeting.eventID = dbo.tbl_CA_event.eventID
INNER JOIN
dbo.tbl_CA_itemInPackage ON dbo.tbl_CA_event.itemInPackageID = dbo.tbl_CA_itemInPackage.itemInPackageID
INNER JOIN
dbo.tbl_CA_package ON dbo.tbl_CA_itemInPackage.packageID = dbo.tbl_CA_package.packageID
INNER JOIN
dbo.tblUsersName ON dbo.tbl_CA_meeting.userID = dbo.tblUsersName.UserId
INNER JOIN
dbo.tbl_CA_mishmeret ON dbo.tbl_CA_meeting.mishmeretID = dbo.tbl_CA_mishmeret.mishmeretID ';
SET @.SQLString = @.SQLString + @.strSQL ;
SET @.SQLString = @.SQLString + '
GROUP BY
dbo.tbl_CA_meeting.userID,
dbo.tblUsersName.OrdName,
dbo.tbl_CA_itemInPackage.itemInPackageName,
dbo.tbl_CA_package.packageName';
EXEC sp_ExecuteSql @.SQLString
return;

my problem is in the Reports, I cant to connect the data to the fileds, (the value is empty)

how can I do it?First, your stored procedure is very susceptible to SQL injection. You really need to constrain the input more. There are several articles on the web that will help you. You can accomplish the same result by using an expression for the SQL statement in the report and write some functions to check the input values.sql

Monday, March 26, 2012

Dynamic query

Hi,
In my stored procedure I have differnet paramters and based on the paramters
different queries needs to be executed against the database.as these queries
share some parts ,I decided to use a dynamic query,so I declared a variable
(DECLARE @.DynamicQuery Varchar(5000)) and based on parameters I append
differnt strings to it (@.DynamicQuery=@.DynamicQuery+ 'string') .The problem
is that this string limits to 750 characters only and it can not hold my
whole query which is more than 750 characters.Why is that so?
Thanks
Here is my sp:
CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
@.AFFILIATEID NUMERIC (8) = 1,
@.ENROLLMENT_TYPR INT=1 -- 1
WITH RECOMPILE --Throw out the query plan on every execution of this
storedprocedure due to its dynamic nature
AS
SET NOCOUNT ON
DECLARE @.CurrentAcademicYear NUMERIC(5)
DECLARE @.CurrentSeesion NUMERIC(5)
DECLARE @.DynamicQuery VARCHAR(5000)
SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
dbo.CURRENT_SESSION
SET @.DynamicQuery = 'SELECT T1.FILEID AS FileID,T1.CURRENT_CGA_REGION_CODE
AS Affiliate_Code,MIN(T1.RECORD_TIMESTAMP) AS
File_ProcessDate,COUNT(T1.FILEID) AS Total_Records,COUNT(CASE
T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
NOfRows_with_Critical_Error,COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID
END) AS NOfRows_with_Biz_Critical_Error,COUNT(CA
SE T1.ERROR_STATUS WHEN 2
THEN T1.FILEID END) AS NOfRows_with_Warning,COUNT(CASE
T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
Inserted_NewMember,COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID
END) AS Updated_Different_20,COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN
T1.FILEID END) AS Updated_Different_21,COUNT(CASE T1.BIZPROCESSING_STATUS
WHEN 30 THEN T1.FILEID END) AS Matched_NoAction,'
SELECT LEN(@.DynamicQuery )
IF @.ENROLLMENT_TYPR =1 --CURRENT/FUTURE ENROLLMENT
BEGIN
SET @.DynamicQuery = @.DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cu
rrentAcademicYear,@.CurrentSeesion,1)
AS Unknown_Active_Members,'
END
ELSE --HISTORY ENROLLMENT
BEGIN
SET @.DynamicQuery = @.DynamicQuery +
'dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cu
rrentAcademicYear,@.CurrentSeesion,2)
AS Unknown_Active_Members,'
END
SET @.DynamicQuery = @.DynamicQuery +
'T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS
File_Type
FROM IF_C1TRANSFORM T1 LEFT OUTER JOIN IF_FILE T2 ON
T1.FILEID=T2.[FILE_ID]
LEFT OUTER JOIN IF_CONTENT_TYPE T3 ON
T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID'
IF @.AFFILIATEID <> 1
BEGIN
SET @.DynamicQuery=@.DynamicQuery +
'WHERE T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID'
END
SET @.DynamicQuery=@.DynamicQuery +
'GROUP BY
T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME'
SELECT @.DynamicQuery
GO"Ray" <RayAll@.microsft.com> wrote in message
news:upq9rh9VFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In my stored procedure I have differnet paramters and based on the
> paramters
> different queries needs to be executed against the database.as these
> queries
> share some parts ,I decided to use a dynamic query
You should not use a dyniamic query for the purposes of code reuse. Use
dynamic SQL reluctantly and only for good reasons.
Here, this won't hurt a bit...
CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
@.AFFILIATEID NUMERIC (8) = 1,
@.ENROLLMENT_TYPR INT=1 -- 1
AS
SET NOCOUNT ON
DECLARE @.CurrentAcademicYear NUMERIC(5)
DECLARE @.CurrentSeesion NUMERIC(5)
DECLARE @.DynamicQuery VARCHAR(5000)
SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
dbo.CURRENT_SESSION
SELECT
T1.FILEID AS FileID,
T1.CURRENT_CGA_REGION_CODE AS Affiliate_Code,
MIN(T1.RECORD_TIMESTAMP) AS File_ProcessDate,
COUNT(T1.FILEID) AS Total_Records,
COUNT(CASE T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
NOfRows_with_Critical_Error,
COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID END) AS
NOfRows_with_Biz_Critical_Error,
COUNT(CASE T1.ERROR_STATUS WHEN 2 THEN T1.FILEID END) AS
NOfRows_with_Warning,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
Inserted_NewMember,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID END) AS
Updated_Different_20,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN T1.FILEID END) AS
Updated_Different_21,
COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 30 THEN T1.FILEID END) AS
Matched_NoAction,
dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.Cur
rentAcademicYear,@.CurrentSeesion,case
@.ENROLLMENT_TYPR when 1 then 1 else 2 end)
AS Unknown_Active_Members,
T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS File_Type
FROM IF_C1TRANSFORM T1
LEFT OUTER JOIN IF_FILE T2
ON T1.FILEID=T2.[FILE_ID]
LEFT OUTER JOIN IF_CONTENT_TYPE T3
ON T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID
WHERE (@.AFFILIATEID = 1 or T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID)
GROUP BY
T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME
--David|||I guess my question is about the where clause.When @.AFFILIATEID = 1,I don;t
want to have a where clause ,how dose it work for this purpose?
Thanks
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u9krc39VFHA.1152@.tk2msftngp13.phx.gbl...
> "Ray" <RayAll@.microsft.com> wrote in message
> news:upq9rh9VFHA.2520@.TK2MSFTNGP09.phx.gbl...
> You should not use a dyniamic query for the purposes of code reuse. Use
> dynamic SQL reluctantly and only for good reasons.
> Here, this won't hurt a bit...
> CREATE PROCEDURE [dbo].[IF_repGetC4GeneralStat]
> @.AFFILIATEID NUMERIC (8) = 1,
> @.ENROLLMENT_TYPR INT=1 -- 1
> AS
> SET NOCOUNT ON
>
> DECLARE @.CurrentAcademicYear NUMERIC(5)
> DECLARE @.CurrentSeesion NUMERIC(5)
> DECLARE @.DynamicQuery VARCHAR(5000)
>
> SELECT @.CurrentAcademicYear=CUR_YEAR,@.CurrentSe
esion=CUR_SESSION FROM
> dbo.CURRENT_SESSION
> SELECT
> T1.FILEID AS FileID,
> T1.CURRENT_CGA_REGION_CODE AS Affiliate_Code,
> MIN(T1.RECORD_TIMESTAMP) AS File_ProcessDate,
> COUNT(T1.FILEID) AS Total_Records,
> COUNT(CASE T1.ERROR_STATUS WHEN 1 THEN T1.FILEID END) AS
> NOfRows_with_Critical_Error,
> COUNT(CASE T1.ERROR_STATUS WHEN 3 THEN T1.FILEID END) AS
> NOfRows_with_Biz_Critical_Error,
> COUNT(CASE T1.ERROR_STATUS WHEN 2 THEN T1.FILEID END) AS
> NOfRows_with_Warning,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 10 THEN T1.FILEID END) AS
> Inserted_NewMember,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 20 THEN T1.FILEID END) AS
> Updated_Different_20,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 21 THEN T1.FILEID END) AS
> Updated_Different_21,
> COUNT(CASE T1.BIZPROCESSING_STATUS WHEN 30 THEN T1.FILEID END) AS
> Matched_NoAction,
> dbo.GetC4MissingEnrollments(T1.FILEID,@.AFFILIATEID,T3.CONTENT_TYPE_NAME,@.C
urrentAcademicYear,@.CurrentSeesion,case
> @.ENROLLMENT_TYPR when 1 then 1 else 2 end)
> AS Unknown_Active_Members,
> T2.EXTRACT_DATE AS File_CreationDateTime,T3.CONTENT_TYPE_NAME AS File_Type
> FROM IF_C1TRANSFORM T1
> LEFT OUTER JOIN IF_FILE T2
> ON T1.FILEID=T2.[FILE_ID]
> LEFT OUTER JOIN IF_CONTENT_TYPE T3
> ON T2.CONTENT_TYPE_ID = T3.CONTENT_TYPE_ID
> WHERE (@.AFFILIATEID = 1 or T1.CURRENT_CGA_REGION_CODE = @.AFFILIATEID)
> GROUP BY
> T1.FILEID,T1.CURRENT_CGA_REGION_CODE,T2.EXTRACT_DATE,T3.CONTENT_TYPE_NAME
>
> --David
>
>|||"Ray" <RayAll@.microsft.com> wrote in message
news:%23xjDS%239VFHA.584@.TK2MSFTNGP15.phx.gbl...
>I guess my question is about the where clause.When @.AFFILIATEID = 1,I don;t
>want to have a where clause ,how dose it work for this purpose?
>
If it adversely affects the execution plan of your query to have the where
clause there, you have a couple of options without resorting to dynamic SQL.
For a query with ony two real variants, I would just code each one as a
static query. That way each one has an appropriate cached plan, and the
whole procedure is much more readable than with dynamic SQL.
David|||>> In my stored procedure I have different paramters and based on the
paramters
different queries needs to be executed against the database.as these
queries share some parts ,I decided to use a dynamic query, <<
Please stop programming and read any basic Software Engineering book.
Look up coupling and cohesion. This is far more fundamental than just
SQL programming. You do not know how to program.
Dynamic SQL is slow, dangerous and an admission that your design is so
weak that a random future user should have more control over it than
you did at design time. I like to name these errors the "Get
BritneySpearsOrSquid" procedures.
Put the core query into a VIEW.|||I so appreciate your help and **thanks for the nice wording** (Excellent way
of helping the others;-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116018663.230642.241190@.g14g2000cwa.googlegroups.com...
> paramters
> different queries needs to be executed against the database.as these
> queries share some parts ,I decided to use a dynamic query, <<
> Please stop programming and read any basic Software Engineering book.
> Look up coupling and cohesion. This is far more fundamental than just
> SQL programming. You do not know how to program.
> Dynamic SQL is slow, dangerous and an admission that your design is so
> weak that a random future user should have more control over it than
> you did at design time. I like to name these errors the "Get
> BritneySpearsOrSquid" procedures.
> Put the core query into a VIEW.
>|||On Fri, 13 May 2005 09:25:55 -0700, Ray wrote:
(an exact copy of a message he also sent 15 minutes earlier)
Hi Ray,
I already replied to your previous message. Please don't multi-post!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Dynamic Query

dynamic procedure creation - possible?

Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/pr...005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx

> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David

dynamic procedure creation - possible?

Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegrou ps.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx

> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David
|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).
|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegro ups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
sql

dynamic procedure creation - possible?

Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David

Thursday, March 22, 2012

Dynamic Parameters S.P.

Using a Stored procedure, i want to sort my records according to a dynamic
field... Could be sort by firstName, CompanyName, UserID... and so on...
Receiving value in @.orderingBy, but i can't seem to find a way to properly
integrate it into my query, juse into the 'order by'...
If any could help...
CREATE PROCEDURE [dbo].[SelectContactListTEST]
@.orderingBy nvarchar (255)
AS
SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid
FROM UserObject UO INNER JOIN
WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN (0, 1)) AND
(UO.Hidden = 0)
ORDER BY "+ @.orderingBy + "How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"lp_rochon" wrote:

> Using a Stored procedure, i want to sort my records according to a dynamic
> field... Could be sort by firstName, CompanyName, UserID... and so on...
> Receiving value in @.orderingBy, but i can't seem to find a way to properly
> integrate it into my query, juse into the 'order by'...
> If any could help...
>
> CREATE PROCEDURE [dbo].[SelectContactListTEST]
> @.orderingBy nvarchar (255)
> AS
> SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid
> FROM UserObject UO INNER JOIN
> WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN (0, 1)) A
ND
> (UO.Hidden = 0)
> ORDER BY "+ @.orderingBy + "|||Thank alot, it does work..
But now, to next question:
I the same aspect, i got my 'where' statement that i need to be dynamic...
As:
WHERE (UO.customer_type = 'wholesaler')
could become:
WHERE (UO.customer_type = 'wholesaler') AND (UO.A = 'XX') AND (UO.B =
'ZZ')
to do that, i want to pass 1 variable (string) as:
WHERE (UO.customer_type = 'wholesaler') @.whereString
know how to do this?
"Alejandro Mesa" wrote:

> How do I use a variable in an ORDER BY clause?
> http://www.aspfaq.com/show.asp?id=2501
>
> AMB
>|||And now to a new problem...
Calling my procedure, passing this
EXEC SelectContactListTEST '0','1','0','lastname','ASC'
I'm getting the following error:
erver: Msg 245, Level 16, State 1, Procedure SelectContactListTEST, Line 9
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value 'TEST' to a column of data type bit.
I really don't see were this 'TEST' column comes from...
See below for code
----
--
CREATE PROCEDURE [dbo].[SelectContactListTEST]
@.activeSearch bit,
@.activeSearch2 bit,
@.hiddenSearch bit,
@.orderingBy nvarchar (255),
@.orderingWay nvarchar (255)
AS
SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid, UO.company AS
company, UO.ContOwner AS contowner, UO.u_first_name AS firstname,
UO.u_last_name AS lastname, UO.u_email_address AS
email, UO.Hidden AS hidden, UO.Activated AS activated, UO.d_date_created AS
datecreated,
A.u_city AS city, A.u_country_code AS country,
A.u_region_code AS region
FROM UserObject UO INNER JOIN
Addresses A ON UO.g_user_id = A.g_id
WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN
(@.activeSearch,@.activeSearch2)) AND (UO.Hidden = @.hiddenSearch)
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then Activated
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then Activated
end
END
GO
---
"Alejandro Mesa" wrote:

> How do I use a variable in an ORDER BY clause?
> http://www.aspfaq.com/show.asp?id=2501
>
> AMB
>|||On Tue, 8 Feb 2005 09:03:01 -0800, lp_rochon wrote:

>And now to a new problem...
>
>Calling my procedure, passing this
>EXEC SelectContactListTEST '0','1','0','lastname','ASC'
>I'm getting the following error:
>erver: Msg 245, Level 16, State 1, Procedure SelectContactListTEST, Line 9
>[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
>nvarchar value 'TEST' to a column of data type bit.
>I really don't see were this 'TEST' column comes from...
Hi lp_rochon,
I guess it's in your data, as one of the last names.
Look at this part:

> case @.orderingWay
> when 'ASC' then
> CASE @.orderingBy
> WHEN 'company' then company
> WHEN 'lastname' then u_last_name
> WHEN 'country' then u_country_code
> WHEN 'region' then u_region_code
> WHEN 'city' then u_city
> WHEN 'email' then u_email_address
> WHEN 'activated' then Activated
> end
> END
I assume that all columns here are char, varchar nchar or nvarchar, except
for the Activated column, which I guess to be bit. The datatype of the
CASE is determined by looking at all datatypes used in the WHEN
expressions; if they are different (and no explicit conversion is used),
then datatype precedence decides which is converted to what. In this case,
SQL Server will attempt to convert all varchar columnsto bit, since bit
has a higher precedence than varchar.
Try changing your ORDER BY to either
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then CAST(Activated as char(1))
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then CAST(Activated as char(1))
end
END
or
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
end
END
DESC,
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'activated' then Activated
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
end
END,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'activated' then Activated
end
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Dynamic Parameters

Hi All,

I am stuck with a report, could you please help.

I have a dynamic stored procedure, which depending on Begindate and Enddate displays columns deptname and amount (depending on the month the amount field is going to sum and display the results in the appropriate date fields), that is sum(amount) in Jan05,Feb05,Mar05......depending on the Begindate and Enddate, it's displays the values.

EX:-

Deptname Jan 05 Feb 05 Mar 05

Housing 5 5 5

Shipping 45 56 85

Handling 10 14 18

How do i incorporate this into the Report designer, because when i use this Stored procedure and create a report, i can see the values coming in the Dataset, but i don't see the Amount values coming into the Preview Page.I only see deptname coming into the Preview page.

i am building a string in the Stored procedure.(Just Letting you know)

what can i do to make the amount values be displayed under appropriate month.

Thanks,

vnswathi.

What is the output of stored procedure? It should return date, category, Quantitiy/Number (appears in dataset)

If you return those three values from stored procedure then you can use Table grouping feature in the Report desinger to build the report.

Thanks
Murthy

|||

I am getting values from the Stored procedure and also able to see the results in the Dataset on the report designer DATA page, but when it comes to laying out the Report on the report designer LAYOUT page and see the PREVIEW, that is where my problem comes.

I am able to display the Department name column but not the Amount column which i have grouped by Year and month.

Any help is appreciated.

Thanks,

vnswathi.

|||

If you get into Dataset then you will surely have those values in Layout/Preview pages too.

Can you verify textbox properties in the layout screen and see are you able to get dataset values there or not? Also check visibility attribute of those text fileds too?

As a simple test, you can verify this by placing a textbox and set those dataset values and see the preview.

Thanks
Murthy

|||I have a other feeling that you may doing something wrong in the Grouping section. Are you building drill-down report? If yes you have to check toggle items?|||

Ok, i was doing it right, but the only thing which i was doing wrong was using a tabluar layout.

I have used Matrix layout and it worked.

Thanks,

vnswathi.

|||Great...any idea what is going wrong with the tabular report?

Dynamic Parameters

Hi All,

I am stuck with a report, could you please help.

I have a dynamic stored procedure, which depending on Begindate and Enddate displays columns deptname and amount (depending on the month the amount field is going to sum and display the results in the appropriate date columns), that is sum(amount) in Jan05,Feb05,Mar05........depending on the Begindate and Enddate, it's displays the values.

EX:-

Deptname Jan 05 Feb 05 Mar 05

Housing 5 5 5

Shipping 45 56 85

Handling 10 14 18

I am building a string in the Stored procedure.(Just Letting you know)

what can i do to make the amount values be displayed under appropriate month.

Thanks,

vnswathi.

hi ,

i guess i came across one such report and what i did was instead of using strings i used the BeginDate and Enddate Parameters and feed them into a function which would rip off the months and the sum up the data corresponding to the month.

month(@.BeginDate) would get u the month and then you can use Case statement to actually sum up the amount for that particular month.

i guess it more of a tedious job, but should work.

|||

yes, it works for months, but how about years.

because we know that there would be 12 Months, but you don't know the Date range.

Instead i have used Matrix layout and it worked.

Thanks,

vnswathi.

Dynamic ORDER BY within stored procedure

I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.


CREATE PROCEDURE procedure_name
@.regNum varchar(14),
@.sortOrder tinyint = 1
AS
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblSPG_Header
WHERE
REG = @.regNum
UNION
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblRCH_Header
WHERE
REG = @.regNum
ORDER BY Obs_Date DESC
GO

Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:


ORDER BY CASE WHEN @.sortOrder = 1 THEN Obs_Date END DESC

but I frequently get the following error when I try to do so:

"ORDER BY items must appear in the select list if the statements contain a UNION operator"

If anyone can offer any suggestions, I would appreciate it. Thanks.try something like :


EXEC ( ' SELECT Filler_OrdNum As ''Accession'', RTrim(Obs_Code) As ''Observation'', REG As ''Register'',Obs_Date As ''Observation Date''
FROM tblSPG_Header WHERE REG = ' + @.regNum + ' order by ' + @.ordcolumn +
' UNION
SELECT Filler_OrdNum As ''Accession'', RTrim(Obs_Code) As ''Observation'', REG As ''Register'',
Obs_Date As ''Observation Date'' FROM tblRCH_Header WHERE REG = ' + @.regNum + ' order by ' + @.ordcolumn )

hth

Thursday, March 29, 2012

Dynamic Security Stored Procedure Repeatedly Called

Dynamic Security Stored Procedure Repeatedly Called

Dynamic Security Stored Procedure Repeatedly Called

Dynamic reports

Tuesday, March 27, 2012

Dynamic Report Columns..?

Dynamic Query!

Dynamic query with SQL statement longer than 8000 chars

Dynamic query to store record count in variable??

Dynamic query to store record count in variable??

Dynamic query to store record count in variable??

dynamic query problem

Monday, March 26, 2012

Dynamic query

Dynamic Query

dynamic procedure creation - possible?

dynamic procedure creation - possible?

dynamic procedure creation - possible?

Thursday, March 22, 2012

Dynamic Parameters S.P.

Dynamic Parameters

Dynamic Parameters

Dynamic ORDER BY within stored procedure

sql dwh problem

Blog Archive

About Me