Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 DocID's and Coresponding Reference Keys

Author  Topic 

10Dawgs
Starting Member

5 Posts

Posted - 2011-01-03 : 15:54:19
How do i write a script to input Doc ID (unique key) and return Reference key (foreign key) and visa versa?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-03 : 20:32:34
Depends on where they are held.
maybe

insert tbl (DOcID, ...) select @DocID, ...
select scope_identity()


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

10Dawgs
Starting Member

5 Posts

Posted - 2011-01-04 : 09:32:38
That gives me a start. I need to know the DB? table? where both keys are located? How do i save the script when I get it running as a report on the report services?

quote:
Originally posted by nigelrivett

Depends on where they are held.
maybe

insert tbl (DOcID, ...) select @DocID, ...
select scope_identity()


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 09:37:41
I would put it in a stored procedure and if necessary base a report on that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

10Dawgs
Starting Member

5 Posts

Posted - 2011-01-04 : 10:39:10
I have posed the following questions to the person requesting the table.
1- I need to know where we are pulling this information from (Data Base(s) & table(s)).
2- Is the user going to put in a list of Doc IDs or input one at a time?
3- Do we want to save it as a report on the report server or just save it as a stored proceedure?

Nigel,
In your code above, Can I assume ... means location i.e.(dbo.MediaStatusCatalog)

What other questions do I need to pose (to the requesting party) to get this thing done? (He thinks I just waive a magic wand and it's done.)
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 10:51:19
It means everything that is missing. You haven't given a lot to go on here.

I would assume that the data will be saved in a table or tables but maybe it's just a list that the user wants a report based on - in which case it probably wouldn't be persisted and would be a criteria in a report.
If there is a report involved what form should it take - i.e. what will the user want to do with the result.
And finally what data and structures are available.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

10Dawgs
Starting Member

5 Posts

Posted - 2011-01-04 : 12:01:19
"You haven't given a lot to go on here." Right, now you're starting to feel my pain. Plus, I was hired as a DBA, not a programmer. So please pardon the dumb questions, I'm completely out of my element. With that in mind, The answers I got back were:

1- I need to know where we are pulling this information from (Data Base(s) & table(s)). THE VENDOR WILL HAVE TO TELL US (So for our purposes we will say it is dbo.tables)
2- Is the user going to put in a list of Doc IDs or input one at a time? IS A LIST
3- Do we want to save it as a report on the report server or just save it as a stored proceedure? IT IS A DYNAMIC REPORT

Nigel,
What other information do I need to ask?

Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 23:55:07
Ok so what you want is an SP that takes a list of IDs and returns a resultset.
If you want to use reporting services for the report then that list will probably be a csv list - maybe a selection from a drop down list.

So you need to know
What information does the user require for each ID.
Does the user need any aggregation?
Is there a time range needed
Do they need a search capability to select the IDs. Or maybe they want everything added yesterday - that would be a scheduled report and much simpler.
What will they want to do with the data.
How much data is expected - could effect how it is delivered.

Then you will have to find out whether the data expected is available and how it is stored.

Once you have that you can think about building the SP and report.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -