Combining the results of a cursor loop

Need a little help here.

I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

I need the results as one table.

Here is my code.
___________________________________
SET NOCOUNT ON

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData
_____________________________________

Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

Any help would be much appreciated.

NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:

Regards

Shaun McGuileSET NOCOUNT ON
CREATE TABLE #CurrentRates
(
AccountType VARCHAR(50),
EffectiveDate DATETIME,
tier INT,
gross FLOAT,
net FLOAT,
aer FLOAT,
footnotes VARCHAR(2000),
[id] INT
)
GO

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT #CurrentRates
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData

SELECT * FROM #CurrentRates

Works, but is it good? ;)

Regards

Shaun McGuile|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?|||Works, but is it good? ;) It uses cursors :o|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?

The line

...Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s...

Splits the ids into a table of column product id.

Have I understood your question?

Regards|||I have and always will be the one and only Shaun McGuile ;)

Lost my dbforums password/email combination somehow.|||Forgot to add the 'drop table #CurrentRates' at the end of the code

Doh!|||Beg your pardon - I thought you were parsing as a string.

Ok - what's the pk of dbo.GetProductRateView?|||pk ha ha ha ha ha ha ha - er..sorry Pootle you had me there.

The db has no pk's nor integrity of any type its real bad

dbo.GetProductRateView is a View pulling data from three non normalised tables its really evil - your heart and that of other members of the community might not take the shock of seeing them.

Its like 'The Ring' of databases (like the film - you see it then you die) lol.|||Well brace yourself

SELECT TOP 100 PERCENT dbo.saving_product.name AS AccountType, dbo.saving_product_variant.from_date AS EffectiveDate,
dbo.saving_product_variant.tier, dbo.saving_product_variant.gross, dbo.saving_product_variant.net, dbo.saving_product_variant.aer,
dbo.saving_date.footnotes, dbo.saving_product.id
FROM dbo.saving_product INNER JOIN
dbo.saving_product_variant ON dbo.saving_product.id = dbo.saving_product_variant.link_id INNER JOIN
dbo.saving_date ON dbo.saving_product.id = dbo.saving_date.link_id
GROUP BY dbo.saving_product.name, dbo.saving_product_variant.from_date, dbo.saving_product_variant.tier, dbo.saving_product_variant.gross,
dbo.saving_product_variant.net, dbo.saving_product_variant.aer, dbo.saving_date.footnotes, dbo.saving_product.id
ORDER BY MAX(dbo.saving_product_variant.date_id) DESC|||date_id? :S

Are you getting the most recent row based on the value date_id? If so then you should know that order by clauses are not guaranteed to work in views. Better to create a view with no order by clause and order it when required.

From BoL:
The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.|||What version are you running BTW?|||INSERT #CurrentRates
SELECT TOP 1 v.*
FROM ( SELECT * FROM dbo.GetProductRateView Order By date_id desc) as v
WHERE v.[id] = @.Id

and remove the order by clause from the view?

Regards

Shaun McGuile|||You can do - no need for the inner query BTW. I'm thinking more than that though.

What version are you running?|||SQLServer 2000 is the db.|||Heh - turns out I didn't need it - apols.

SELECT v.*
FROM dbo.GetProductRateView as v
INNER JOIN--"Last" date per product.
(SELECT dbo.saving_product.id
, MAX(dbo.saving_product_variant.date_id) AS last_date_id
FROM dbo.saving_product
INNER JOIN
dbo.saving_product_variant
ON dbo.saving_product.id = dbo.saving_product_variant.link_id
INNER JOIN
(SELECT *
FROM dbo.split_function(@.IdsString)) AS ids
ON ids.Value = dbo.saving_product.id
GROUP BY dbo.saving_product.id) AS last_prods
ON last_prods.id = v.id
AND last_prods.last_date_id = v.date_id
How is that for the data?|||I'll give it a go and let you know.

Cheers Pootle.

Haven't looked in on Yak Coral in ages. Might do it today if I get time.|||Yeah that works splen-diddly (its how you say it out loud that gets ya).

Only modifictions I had to make were field name for the productID instead of value, altered the view to return date_id field and a DISTINCT is needed as in
SELECT DISTINCT v.* ...

Bril thats my homework done! On with the next assignment!

lol only joking! I dont do homework!

Cheers Pootle

Shaun McGuile|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 seconds

Amazing!|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 secondsThat's set based programming for you. The other thing to remember is that speed of the cursor will be linear. Each additional iteration will take ~ as long as the last. Set based stuff mitigates against changes in scale much better.

Combined Primary Key - Why?

In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:

> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql

Combine tables from previous db to new db (uhh...?)

I'm wanting to migrate an existing customer's database into a new products db. The previous contractor used seperate tables for each product type, where I chose to use one "products" table.

My challenge has been that the previous db uses attributes that aren't common across all products. Would it be best to do a products_attribute table? If so, how would I query the previous products db and seperate the information during an insert between "product A" and "product A attributes"?

Sample existing table:
ID, Name, Price, Weight, Attr1, Attr2, Attr3, Attr4, Attr5, Attr6

New table: Products
ID (auto), ProductName, Price, Weight
- Products_Attribute table
- ... ?you can use a cursor...and loop through each record...get the values into variables and insert into the 2 tables...

your products_attributes table can be somthing like

ID,Attr1, Attr2, Attr3, Attr4, Attr5, Attr6.... you can have the product name too if you need to..

HTH|||could you post a link or some example code for doing this? Thanks!|||sorry i misread your post...did u mean move data from one table in a DB to two tables in ANOTHER db ? or is this all within the same db ? if you are moving from one DB to another,then it wud be easier to move the entire table into the new db first and then separate the data into two tables inside the new db...

HTH|||from one source db to a different target db, then split into two tables at the target db.|||there are prbly many ways to do this...you can do the export/import wizard to transfer the table to the target db and then use tht table to split it..thts when you can use cursors to go through each row and insert the values into diff tables...

HTH|||Any links for cursor samples you're referring to? I haven't used them before.

Column Level Permissions Security Issue

SQL2K5 SP2
I’m trying to lock down PCI sensitive data columns in some product databas
es
from our developers. We need developers to have data reader rights to the
production database to perform general maintenance and troubleshooting of th
e
application BUT minus the specific secured columns like credit card number,
etc.
I have a user role setup called RWE created by:
CREATE ROLE RWE
GRANT SELECT TO RWE
GRANT INSERT TO RWE
GRANT UPDATE TO RWE
GRANT DELETE TO RWE
GRANT EXECUTE TO RWE
A standard SQL user is placed inside this role allowing them full read,
write, and execute rights on everything in the DB which is fine.
BUT, now we want those same rights except for the sensitive data files so I
updated the rule with the following script:
DENY SELECT ON [dbo].[TableNameHere] ([strCC]) TO [RWE]
Loggin in a developer and doing a SELECT * FROM TableNameHere throws a
permission error as expected, so far so good.
But, I did a SELECT * FROM ViewThatContainsField_strCC and shows them the
denied field. Oh, oh! I also did EXEC spProcThatShows_strCC and again it
shows the denied credit card field. Again, oh, oh.
So, long story short, I’m stuck. My understanding of SQL permissions, I wa
s
under the understanding that placing a deny at the lowest level (column)
should throw errors in all methods of access that column (either by direct
select, or select through views, procs, etc).
Any suggestions or any hints on what I’m missing here?
Thx ahead of time!Ross Nornes (RossNornes@.discussions.microsoft.com) writes:
> A standard SQL user is placed inside this role allowing them full read,
> write, and execute rights on everything in the DB which is fine. BUT,
> now we want those same rights except for the sensitive data files so I
> updated the rule with the following script:
> DENY SELECT ON [dbo].[TableNameHere] ([strCC]) TO [RWE]
> Loggin in a developer and doing a SELECT * FROM TableNameHere throws a
> permission error as expected, so far so good.
> But, I did a SELECT * FROM ViewThatContainsField_strCC and shows them the
> denied field. Oh, oh! I also did EXEC spProcThatShows_strCC and again it
> shows the denied credit card field. Again, oh, oh.
Ownership chaining I suppose. That is, the procedure and the view are owned
by the same database user that owns the table. In such case the permissions
of the owner applies.
You could deny permission on the view column. You should probably deny
execution on procedures that disclose sensitive data as well.
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|||Thx for the reply Erland!
All objects are owned by DBO.
Ya, I could deny access on the view column and the procs, if I have to I
will, but the issue is these are legacy applications and will take HOURS and
HOURS to dig through all the code to determine all places where sensitive
data fields are located and grant specific permissions on each object.
I'm hoping I just have a huge laps in my understanding here, but, if a role
is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY is placed
on a table or column, it should not matter how the table is called, the deny
should be enforced. Since the deny works if I hit table directly with a
SELECT * FROM [TableNameHere], why would a view, proc, function or anyth
ing
else not obey the deny permission? This seems to be a HUGE hold in basic
security enforcement unless I'm totally missing how this works. If this is
true, shops like my shop that need to allow developers read access to their
databases but also need to not allow them access to secure files like credit
card numbers, we are going to have to code review ever single view, proc,
function, etc, thats being moved out since those appear to not obey the deny
permissions.
Is there a better way to achieve this same protection without the huge hours
of item by item code review? Whats the point of deny permissions if this is
how they function?
I really hope I'm just missing something here, but at this point I pretty
frustrated with this aspect of the security system right now...
Ross
"Erland Sommarskog" wrote:

> Ross Nornes (RossNornes@.discussions.microsoft.com) writes:
> Ownership chaining I suppose. That is, the procedure and the view are owne
d
> by the same database user that owns the table. In such case the permission
s
> of the owner applies.
> You could deny permission on the view column. You should probably deny
> execution on procedures that disclose sensitive data as well.
> --
> 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
>|||Ross Nornes (RossNornes@.discussions.microsoft.com) writes:
> All objects are owned by DBO.
> I'm hoping I just have a huge laps in my understanding here, but, if a
> role is granted SELECT rights as in "GRANT SELECT TO RWE", then a DENY
> is placed on a table or column, it should not matter how the table is
> called, the deny should be enforced. Since the deny works if I hit table
> directly with a SELECT * FROM [TableNameHere], why would a view, proc,
> function or anything else not obey the deny permission? This seems to be
> a HUGE hold in basic security enforcement unless I'm totally missing how
> this works.
When working with security in SQL Server it's imperative to understand
how ownership chaining works. I have an article that among other things
discusses ownership chaining: http://www.sommarskog.se/dynamic_sql.html.
There are also topics in Books Online that are good reading

> If this is true, shops like my shop that need to allow developers read
> access to their databases but also need to not allow them access to
> secure files like credit card numbers, we are going to have to code
> review ever single view, proc, function, etc, thats being moved out
> since those appear to not obey the deny permissions.
My thought is that since these views and procedures expose this
sensitive data, there are users who have permission to access this
data. I don't know how you grant permission to users, but I would
expect you to be very restrictive with giving permissions to views
and procs that expose sensitive data. And unless you are very
restrictive with granting access to the database as a whole, you would
need to have in place a mechanism where you determine whether a view
or procedure is only for a chosen few or for the general masses.
If you are on SQL 2005, and you think ownership chaining gets in the
way, you can transfer ownership of the objects to a different user.
But that is likely to have repercussions not only for the developers,
but also the users of the system.
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