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.
| Author |
Topic |
|
Chainwhip
Starting Member
33 Posts |
 Posted - 2003-07-21 : 17:04:19
|
| I have some code which I am finding myself using a lot across different tables in differnet databases. The code is a random string generator that I can specify the length for and it is used to generate passwords, etc. I would like to be able to create a globally accessible function for this code instead of having to keep pasting it into my sprocs. I would like to pass it an argument telling it how many characters the random string should be. What is the best way to do this so I can call it from any sproc in any database? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-21 : 17:11:07
|
| Well you can store it in the master database or in a user database. If you store it in the master database and have the first three character be sp_, then you won't have to reference master to be able to use it. I would recommend creating another user database that contains your useful objects in it. Then you can call from any stored procedure by using the 3 part naming convention if the other database is on the same server or use the 4 part naming convention if the database is on another server and need to use a linked server.Tara |
 |
|
|
Chainwhip
Starting Member
33 Posts |
Posted - 2003-07-21 : 17:16:04
|
| Thanks. I'm a bit new to all this and am missing some syntax. How do I assign a variable to the return value of a sproc?SET @var = dbAnother.dbo.spRandomStringGenerator ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-21 : 17:18:59
|
| EXEC dbAnother.dbo.spRandomStringGenerator @var OUTPUTTo verify this, just run (change the data type if needed):DECLARE @var VARCHAR(100)EXEC dbAnother.dbo.spRandomStringGenerator @var OUTPUTPRINT @varTara |
|
|
|
Chainwhip
Starting Member
33 Posts |
Posted - 2003-07-21 : 17:24:37
|
| Thanks very much - got this working nicely now. |
|
|
|
|
|
|
Best way to reuse code across dbs and tables