Showing posts with label common. Show all posts
Showing posts with label common. Show all posts

Wednesday, March 28, 2012

Passing multiple values in 1 varchar variable in a stored proc IN

This is a common Newbie question. This shows that you don't know SQL
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first w of any SQL language class. SQL is not your original
procedural programming language.
1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.
2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. You can add a computation for the
relative postion of each element in the list (left as a exercise for
the student)
You can then write:a query like this:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.
3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):
CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL
(CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
or use scalar subqueries like this on subsets of the parameters:
(SELECT L.address_code
FROM Locations AS L
WHERE @.p1 = L.longitude
AND @.p2 = L.latitude
AND @.p3 = 'Paris');
SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
...> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
This shows your lack of industrial programming experience and exposure, this
is a common requirement from application screeens that allow multiple
values, for instance a multi-value select list.

> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
Why is it dangerous?
Why is it slow?
Why is it a kludge?
Dynamic SQL is not used by newbies, its used by people who understand how to
get an effiecent well maintained and scalable solution.
You repeated 'can' this answer and people repeatedly ask you (including
myself) to post your statistics backing up your claims - you never do which
we can only conclude that you are talking rubbish.
Your proposed solution is slow and will not scale and is certainly
significantly slower and more combersome than do it the correct way - using
dynamic SQL or XML instead.
You need to stop and do a fundemental programming course, go get some real
industrial experience instead of gaining experience from books and playing
with the product.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1135719723.547696.113660@.g14g2000cwa.googlegroups.com...
> This is a common Newbie question. This shows that you don't know SQL
> uses only scalar parameters and has only one data structure, the table.
> This is a fundamental programming concept that you should learn in the
> first w of any SQL language class. SQL is not your original
> procedural programming language.
> 1) The dangerous, slow kludge is to use dynamic SQL and admit that any
> random furure user is a better programmer than you are. It is used by
> Newbies who do not understand SQL or even what a compiled language is.
> A string is a string; it is a scalar value like any other parameter; it
> is not code. Again, this is not just an SQL problem; this is a basic
> misunderstanding of programming principles.
> 2) Passing a list of parmeters to a stored procedure can be done by
> putting them into a string with a separator. I like to use the
> traditional comma. Let's assume that you have a whole table full of
> such parameter lists:
> CREATE TABLE InputStrings
> (keycol CHAR(10) NOT NULL PRIMARY KEY,
> input_string VARCHAR(255) NOT NULL);
> INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
> INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
> etc.
> This will be the table that gets the outputs, in the form of the
> original key column and one parameter per row.
> CREATE TABLE Parmlist
> (keycol CHAR(10) NOT NULL,
> parm INTEGER NOT NULL);
> It makes life easier if the lists in the input strings start and end
> with a comma. You will need a table of sequential numbers -- a
> standard SQL programming trick, Now, the query, in SQL-92 syntax
> (translate into your local dialect):
> INSERT INTO ParmList (keycol, parm)
> SELECT keycol,
> CAST (SUBSTRING (I1.input_string
> FROM S1.seq
> FOR MIN(S2.seq) - S1.seq -1)
> AS INTEGER)
> FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
> WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
> ','
> AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
> ','
> AND S1.seq < S2.seq
> GROUP BY I1.keycol, I1.input_string, S1.seq;
> The S1 and S2 copies of Sequence are used to locate bracketing pairs of
> commas, and the entire set of substrings located between them is
> extracted and cast as integers in one non-procedural step. The trick
> is to be sure that the right hand comma of the bracketing pair is the
> closest one to the first comma. You can add a computation for the
> relative postion of each element in the list (left as a exercise for
> the student)
> You can then write:a query like this:
> SELECT *
> FROM Foobar
> WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);
> Hey, I can write kludges with the best of them, but I don't. You need
> to at the very least write a routine to clean out blanks and
> non-numerics in the strings, take care of floating point and decimal
> notation, etc. Basically, you must write part of a compiler in SQL.
> Yeeeech! Or decide that you do not want to have data integrity, which
> is what most Newbies do in practice altho they do not know it.
> 3) The right way is to use tables with the IN () predicate, You set up
> the procedure declaration with a "fake array" made from a repeated
> gorup, like this in SQL/PSM (translate into your local dialect):
> CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
> INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
> BEGIN
> SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm -- kill redundant dups
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL -- ignore empty aparameters
> AND <other conditions> )
> AND <more predicates>;
> <more code>;
> END;
> The idea is that creating a derived table will perform better .You can
> also add functions to the parameters like UPPER(pi), apply CASE
> expressions like in T-SQL
> (CASE WHEN @.p1 = 'usa' THEN @.p2 ELSE 2.2 * @.p2 END)
> or use scalar subqueries like this on subsets of the parameters:
> (SELECT L.address_code
> FROM Locations AS L
> WHERE @.p1 = L.longitude
> AND @.p2 = L.latitude
> AND @.p3 = 'Paris');
> SQL Server can have up to 1,024 parameters in a stored procedure and
> that is usually good enough. If not, make two calls to the procedure
> ...
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Dynamic SQL is not used by newbies, its used by people who understand
> how to get an effiecent well maintained and scalable solution.
But for this particular problem, dynamic SQL is definitely not very
scalable. When the list grows in size, the performance for IN() gets
horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
has any improvements.)
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|||That depends on the number of elements for the IN clause, there are only 3
on the OP's post so I don't see a problem, and the problem isn't anything to
do with dynamic SQL, rather, the way the IN clause works.
For a larger IN list, say hundreds rather than < dozen then i would then I'd
probably chop the list up into a set and do an IN or EXISTS.
But to repeat for the benefit of celko, this is not a dynamic sql
performance problem but rather the number of elements on the IN clause.
Tony.
Tony Rogersonen
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973BE48CB0Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> But for this particular problem, dynamic SQL is definitely not very
> scalable. When the list grows in size, the performance for IN() gets
> horrendeous, at least in SQL 2000. (I have not checked whether SQL 2005
> has any improvements.)
>
> --
> 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

Saturday, February 25, 2012

Pass a parameter with a Snap Shot request

I have a set of common reports which I run on a nightly snap shot just fine.

I would like to run a snap shot on these same reports passing a customer_id parameter to override the default customer_id already in the report.

I know one way around this is to create a linked report from the core reports and change the customer_id parameter there but I would rather not maintain several thousand linked reports folders or have to go through and create a new one every time we have a new customer.

So is there a way I can send a parameter through a Snap Shot or some other way I could do this that I have not thought of?

You can't really dynamically provide a per-snapshot parameter the way you describe it. One thing you could do (which has its own implicit drawbacks) is to create your snapshots with default paramter values that will NOT do any fitering at all. You essentially save a giant snapshot of ALL your data.

Then, when your users request the snapshot, you implement filtering on the report server itself (instead of on the SQL side) by utilizing a filter on the dataset or data region itself -- The obvious drawback is that you're bringing tons of unnecessary data across the wire to be filtered at SSRS....

|||

Russell Christopher - msft wrote:

Then, when your users request the snapshot, you implement filtering on the report server itself (instead of on the SQL side) by utilizing a filter on the dataset or data region itself -- The obvious drawback is that you're bringing tons of unnecessary data across the wire to be filtered at SSRS....

Ok, this is what I plan on doing but i've been all over the net looking for how to do exactly this, could someone explain?

|||

We have a similar requirement for creating snapshots and we are going to write a console application that will do all of our schedule runs and call the SetReportParameters() method along with CreateReportHistorySnapshot() right after it.

This solves the issues with runs for all data, which our security would never allow, even if we guaranteed that the filters would work.

Monday, February 20, 2012

Partitions and Common Practice

Hello All,
I posted a simillar question sometime back but I did not see any response. I
have modified my question quite a bit with more info and posting it hoping to
see some response this time :)
I have a Policy Master data file whose uniqueness is identified by the
following
columns (Business Key).
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction was entered into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
We loaded this file into the Staging table of SQLServer 2005 database --
STGPOLICY. The data in this staging table, STGPOLICY, will be processed and
populated into FACPOLICY table.
STGPOLICY Table has these 5 important columns apart from the other columns
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date]
Surrogate Key, UniqID, is an identity column that gets incremented as
records are inserted into the table.
For better performance and Maintainability, we decided to use Partitions on
FACPOLICY table with [Transaction Date] being the Partition Key.
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
For faster loading and processing, I have to Partition the STGPOLICY table
on [Transaction Date] column as this column is used for extensive
search/processing. So, I created the partition with [TransactionDate] as the
Partition column. However, when I try to create a unique index on the natural
keys the system gives an error as Transactiondate is not part of the Unique
Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
Date].
I went through the BOL and found that this is the ugly side of PArtitions.
Does that mean that I cannot define a primary key on the natural key when the
natural key is not a Partition Key? I'm left with either creating a nonunique
key or including the Partition Key along with the Natural Key. Is this a
common practice?
Now, with the FACPOLICY table, the UniqID adds another level of intrigue
since the Primary Key would be the UniqID (assuming that there is no
Partitions). With partitions, would it mean that I have to include
TransactionDate to UniqID to create a Primary Key ?
I'm looking for some common practices. Please help.
Thanks,
rgn
Hello All,
Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working on
Partitioning some of the tables for a datawarehousing project and need some
guidance/help.
I have a Policy Master table whose uniqueness is identified by the following
columns via a Primary Key Clustered constraint.
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction came into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
I created a Partition Function, Partition Scheme and the Created the table
on the Partition Scheme with the [Transaction Date] as the Partitioning
Column. Things looked good till this point. However, when I tried to create
the Primary Key on the above 4 columns, I got the following error:
Msg 1908, Level 16, State 1, Line 1
Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors
I understand what this error means. But I cannot modify the Primary Key by
adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
help?
Thanks,
rgn> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
Another other alternative is to not partition the natural key. The downside
is that you can't use SWITCH to quickly load data with the non-partitioned
index in place. If SWITCH is an important part of you partitioning
strategy, you can drop the non-partitioned unique constraint/index, SWITCH
in the new data and then recreate the constraint/index afterward.
I wouldn't go as far as to say that it is a common practice to add the
partitioning column to a unique constraint/index solely to facilitate
partitioning, but I suspect it is not all that unusual. Bending the
database design rules in favor of performance and manageability is an option
as long as you have mechanisms in place to ensure data integrity.
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
All partitioned indexes include the partitioning column and unique
partitioned indexes must explicitly include the partitioning column in the
key. The partitioning column is implicitly included in non-clustered
indexes (but not part of the key) if not already part of the clustered or
non-clustered key. So, like the natural key, you'll need to add the
partitioning column to the surrogate key to form a composite surrogate key.
It's a little more inconvenient to deal with the composite key but more
palatable that adding the partitioning key to a natural key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:7B2D7BEE-F322-46B0-8CB4-4493C7C7E86B@.microsoft.com...
> Hello All,
> I posted a simillar question sometime back but I did not see any response.
> I
> have modified my question quite a bit with more info and posting it hoping
> to
> see some response this time :)
> I have a Policy Master data file whose uniqueness is identified by the
> following
> columns (Business Key).
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction was entered into the system and since it is
> a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> We loaded this file into the Staging table of SQLServer 2005 database --
> STGPOLICY. The data in this staging table, STGPOLICY, will be processed
> and
> populated into FACPOLICY table.
> STGPOLICY Table has these 5 important columns apart from the other columns
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date]
> Surrogate Key, UniqID, is an identity column that gets incremented as
> records are inserted into the table.
> For better performance and Maintainability, we decided to use Partitions
> on
> FACPOLICY table with [Transaction Date] being the Partition Key.
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
>
> For faster loading and processing, I have to Partition the STGPOLICY table
> on [Transaction Date] column as this column is used for extensive
> search/processing. So, I created the partition with [TransactionDate] as
> the
> Partition column. However, when I try to create a unique index on the
> natural
> keys the system gives an error as Transactiondate is not part of the
> Unique
> Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
> Date].
> I went through the BOL and found that this is the ugly side of PArtitions.
> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
> I'm looking for some common practices. Please help.
>
> Thanks,
> rgn
>
>
> Hello All,
> Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working
> on
> Partitioning some of the tables for a datawarehousing project and need
> some
> guidance/help.
> I have a Policy Master table whose uniqueness is identified by the
> following
> columns via a Primary Key Clustered constraint.
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction came into the system and since it is a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> I created a Partition Function, Partition Scheme and the Created the table
> on the Partition Scheme with the [Transaction Date] as the Partitioning
> Column. Things looked good till this point. However, when I tried to
> create
> the Primary Key on the above 4 columns, I got the following error:
> Msg 1908, Level 16, State 1, Line 1
> Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
> Partition columns for a unique index must be a subset of the index key.
> Msg 1750, Level 16, State 0, Line 1
> Could not create constraint. See previous errors
> I understand what this error means. But I cannot modify the Primary Key by
> adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
> help?
> Thanks,
> rgn
>