August 28, 2018 at 11:16 pm
I am creating a number of stored procs for a particular category of things. These store procs will all have one thing in common and that will be the values they query upon. These values come from a particular table and up until now I am storing these values in temp tables within EACH stored proc.
Is there any way I can put these values somewhere else in one place and just access them in each stored procedure instead of storing the same values in EVERY stored proc?
August 29, 2018 at 5:11 am
NikosV - Tuesday, August 28, 2018 11:16 PMI am creating a number of stored procs for a particular category of things. These store procs will all have one thing in common and that will be the values they query upon. These values come from a particular table and up until now I am storing these values in temp tables within EACH stored proc.Is there any way I can put these values somewhere else in one place and just access them in each stored procedure instead of storing the same values in EVERY stored proc?
I must be missing something here, but sure.
Put them in a permanent table and then query that from your stored procs.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 29, 2018 at 6:18 am
Phil Parkin - Wednesday, August 29, 2018 5:11 AMNikosV - Tuesday, August 28, 2018 11:16 PMI am creating a number of stored procs for a particular category of things. These store procs will all have one thing in common and that will be the values they query upon. These values come from a particular table and up until now I am storing these values in temp tables within EACH stored proc.Is there any way I can put these values somewhere else in one place and just access them in each stored procedure instead of storing the same values in EVERY stored proc?
I must be missing something here, but sure.
Put them in a permanent table and then query that from your stored procs.
Sorry. I should have mentioned it but totally forgot. I don't have permission to create tables or views and was wondering if I had any other possibilities.
August 29, 2018 at 6:41 am
NikosV - Wednesday, August 29, 2018 6:18 AMPhil Parkin - Wednesday, August 29, 2018 5:11 AMNikosV - Tuesday, August 28, 2018 11:16 PMI am creating a number of stored procs for a particular category of things. These store procs will all have one thing in common and that will be the values they query upon. These values come from a particular table and up until now I am storing these values in temp tables within EACH stored proc.Is there any way I can put these values somewhere else in one place and just access them in each stored procedure instead of storing the same values in EVERY stored proc?
I must be missing something here, but sure.
Put them in a permanent table and then query that from your stored procs.
Sorry. I should have mentioned it but totally forgot. I don't have permission to create tables or views and was wondering if I had any other possibilities.
That's an important piece of information. Can you create Table Valued Functions?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 29, 2018 at 7:13 am
NikosV - Wednesday, August 29, 2018 6:18 AMPhil Parkin - Wednesday, August 29, 2018 5:11 AMNikosV - Tuesday, August 28, 2018 11:16 PMI am creating a number of stored procs for a particular category of things. These store procs will all have one thing in common and that will be the values they query upon. These values come from a particular table and up until now I am storing these values in temp tables within EACH stored proc.Is there any way I can put these values somewhere else in one place and just access them in each stored procedure instead of storing the same values in EVERY stored proc?
I must be missing something here, but sure.
Put them in a permanent table and then query that from your stored procs.
Sorry. I should have mentioned it but totally forgot. I don't have permission to create tables or views and was wondering if I had any other possibilities.
It seems you have permission to create tables in tempdb.
You can create permanent tables in there too, you know...
You're gonna need to check in your code if the table exists and populated (all objects in tempdb are dropped when server is restarted for some reason, like cluster failover), and repopulate it if it's not there. But still - it will be happeneing not on every execution of multiple stored procedures.
_____________
Code for TallyGenerator
August 29, 2018 at 7:32 am
Phil Parkin - Wednesday, August 29, 2018 6:41 AMNikosV - Wednesday, August 29, 2018 6:18 AMPhil Parkin - Wednesday, August 29, 2018 5:11 AMNikosV - Tuesday, August 28, 2018 11:16 PMI am creating a number of stored procs for a particular category of things. These store procs will all have one thing in common and that will be the values they query upon. These values come from a particular table and up until now I am storing these values in temp tables within EACH stored proc.Is there any way I can put these values somewhere else in one place and just access them in each stored procedure instead of storing the same values in EVERY stored proc?
I must be missing something here, but sure.
Put them in a permanent table and then query that from your stored procs.
Sorry. I should have mentioned it but totally forgot. I don't have permission to create tables or views and was wondering if I had any other possibilities.
That's an important piece of information. Can you create Table Valued Functions?
Yes I believe I can. Never used one though so this should be a nice start.
Would this be ok if multiple stored procedures that will use this tvf run simultaneously? Would this be a normal insert?
August 29, 2018 at 11:54 am
I wouldn't create the permanent table in tempdb. If you aren't granted permissions in the database your procedures are in, ask for the permanent table to be created for you with DML permission for the login you are using to execute your procedures.
August 29, 2018 at 12:22 pm
Joe Torre - Wednesday, August 29, 2018 11:54 AMI wouldn't create the permanent table in tempdb. If you aren't granted permissions in the database your procedures are in, ask for the permanent table to be created for you with DML permission for the login you are using to execute your procedures.
What about that tvf Phil was telling me about? Can't that work?
I prefer not to go to the database guy because I am client premises.
August 29, 2018 at 12:49 pm
NikosV - Wednesday, August 29, 2018 12:22 PMJoe Torre - Wednesday, August 29, 2018 11:54 AMI wouldn't create the permanent table in tempdb. If you aren't granted permissions in the database your procedures are in, ask for the permanent table to be created for you with DML permission for the login you are using to execute your procedures.What about that tvf Phil was telling me about? Can't that work?
I prefer not to go to the database guy because I am client premises.
Technically, it works. You define a TVF containing the values you want & then you can select from it as if it were a table.
You won't necessarily get a great execution plan ...
Here is an example:
CREATE FUNCTION dbo.ReturnSomeConstantData
()
RETURNS TABLE
AS
RETURN SELECT *
FROM
(
VALUES
(
1
)
, (
2
)
) t1 (Value);
GO
SELECT * FROM dbo.ReturnSomeConstantData() rscd
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 29, 2018 at 12:58 pm
Phil Parkin - Wednesday, August 29, 2018 12:49 PMNikosV - Wednesday, August 29, 2018 12:22 PMJoe Torre - Wednesday, August 29, 2018 11:54 AMI wouldn't create the permanent table in tempdb. If you aren't granted permissions in the database your procedures are in, ask for the permanent table to be created for you with DML permission for the login you are using to execute your procedures.What about that tvf Phil was telling me about? Can't that work?
I prefer not to go to the database guy because I am client premises.Technically, it works. You define a TVF containing the values you want & then you can select from it as if it were a table.
You won't necessarily get a great execution plan ...Here is an example:
CREATE FUNCTION dbo.ReturnSomeConstantData
()
RETURNS TABLE
AS
RETURN SELECT *
FROM
(
VALUES
(
1
)
, (
2
)
) t1 (Value);GO
SELECT * FROM dbo.ReturnSomeConstantData() rscd
Ok cool. I'll check it tomorrow. 2 questions. If I got this right no parameter is being passed right? Also, what does rscd mean?
August 29, 2018 at 1:29 pm
NikosV - Wednesday, August 29, 2018 12:58 PMOk cool. I'll check it tomorrow. 2 questions. If I got this right no parameter is being passed right? Also, what does rscd mean?
No parameter is correct.
rcsd is an (unused) alias for the ReturnSomeConstantData table. SQL Prompt automatically aliases my tables like this. In this case, it can be deleted with no effect.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 29, 2018 at 1:36 pm
NikosV - Wednesday, August 29, 2018 12:58 PMPhil Parkin - Wednesday, August 29, 2018 12:49 PMNikosV - Wednesday, August 29, 2018 12:22 PMJoe Torre - Wednesday, August 29, 2018 11:54 AMI wouldn't create the permanent table in tempdb. If you aren't granted permissions in the database your procedures are in, ask for the permanent table to be created for you with DML permission for the login you are using to execute your procedures.What about that tvf Phil was telling me about? Can't that work?
I prefer not to go to the database guy because I am client premises.Technically, it works. You define a TVF containing the values you want & then you can select from it as if it were a table.
You won't necessarily get a great execution plan ...Here is an example:
CREATE FUNCTION dbo.ReturnSomeConstantData
()
RETURNS TABLE
AS
RETURN SELECT *
FROM
(
VALUES
(
1
)
, (
2
)
) t1 (Value);GO
SELECT * FROM dbo.ReturnSomeConstantData() rscd
Ok cool. I'll check it tomorrow. 2 questions. If I got this right no parameter is being passed right? Also, what does rscd mean?
You should be able to figure it out from the code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 29, 2018 at 2:02 pm
Phil Parkin - Wednesday, August 29, 2018 1:29 PMNikosV - Wednesday, August 29, 2018 12:58 PMOk cool. I'll check it tomorrow. 2 questions. If I got this right no parameter is being passed right? Also, what does rscd mean?No parameter is correct.
rcsd is an (unused) alias for the ReturnSomeConstantData table. SQL Prompt automatically aliases my tables like this. In this case, it can be deleted with no effect.
Thanks Phil. I'll post back tomorrow.
August 29, 2018 at 11:40 pm
NikosV - Wednesday, August 29, 2018 2:02 PMPhil Parkin - Wednesday, August 29, 2018 1:29 PMNikosV - Wednesday, August 29, 2018 12:58 PMOk cool. I'll check it tomorrow. 2 questions. If I got this right no parameter is being passed right? Also, what does rscd mean?No parameter is correct.
rcsd is an (unused) alias for the ReturnSomeConstantData table. SQL Prompt automatically aliases my tables like this. In this case, it can be deleted with no effect.Thanks Phil. I'll post back tomorrow.
You're the man. Many thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply