May 31, 2011 at 11:31 am
I have to write a stored procedure which serves as a universal stored procedure for all the tables in the database...
From the application side he will pass me the table name then in return
i have to pass all the columns for that table.
for example if he passes the table name as ABC
then i have to pass all the column values to application side related to table ABC...so dynamically i have to generate the select code with the table name inside the procedure
May 31, 2011 at 11:34 am
There's no point in using a sp for that... just do it adhoc.
What's the real business need behind this request?
May 31, 2011 at 11:37 am
Best tools for that would be any of the ORM tools, like Linq or nHibernate. Don't do it in a proc, you'll just end up with injection vectors all over the place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 31, 2011 at 11:37 am
There are like 50 look up tables in our database...
then in all the look up tables he has like Active or Inactive values...
so based onthe acitve/inactive values he has to set the colors onthe application side...
so he was asking me to create a stored procedure where he will pass the table name then i have to return all the column values related to that table.
May 31, 2011 at 11:40 am
Sorry to hit the same nail over and over again. But a SINGLE sp is not the correct tool for this.
ORM or linq seems much better.
Or you can go the route of generating the 50 procs once and then calling the correct one. That one could make some sort of sense but not 1 proc for all tables.
May 31, 2011 at 1:44 pm
How about some dynamic sql to dynamically create the stored procedure for doing this properly instead?
SELECT 'CREATE PROCEDURE dbo.[spGet_' + ss.NAME + '_' + st.NAME + '] AS SELECT ' + STUFF((SELECT ',' + sc.name
FROM sys.columns sc
WHERE sc.object_id = st.OBJECT_ID
ORDER BY sc.column_id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'') +
' FROM ' + QUOTENAME(ss.NAME) + '.' + QUOTENAME(st.NAME) + ';'
FROM sys.tables st
JOIN sys.schemas ss
ON st.schema_id = ss.schema_id;
Then just have him dynamically generate the procedure name (should be easy since he knows the table name already).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 31, 2011 at 2:00 pm
Wayne, even that's a bad idea.
He'll end up having to query metadata for the returned dataset, unless all the tables have identical structures (in which case, why 50 tables, why not 1 table with a "category" column or some such?).
Linq or any of the other ORM products will that part of the work for him.
As per Leroy Jenkin's post ... I mean Joe Celko's post, there are some issues here beyond that, but it has to start being fixed somewhere.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 31, 2011 at 3:38 pm
Hi GSquared,
This is meant to be run one time to create the procs, that are then used to get the data. Of course, they need some modifications to add a where clause, but other than that, what is wrong with this?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 31, 2011 at 5:24 pm
CELKO (5/31/2011)
Do you also believe in the Magical, Universal Elixir that will cure all diseases and restore youth?
Hell YES!!! It's called "Beer"! 😀
--Jeff Moden
May 31, 2011 at 5:37 pm
Lucky9 (5/31/2011)
I have to write a stored procedure which serves as a universal stored procedure for all the tables in the database...From the application side he will pass me the table name then in return
i have to pass all the columns for that table.
for example if he passes the table name as ABC
then i have to pass all the column values to application side related to table ABC...so dynamically i have to generate the select code with the table name inside the procedure
I agree with the others. Without knowing a whole lot more about why this is being done, this is probably a really bad thing to do. That, notwithstanding, the following will easily do the trick. Change it to your liking and change it to a stored proc.
USE AdventureWorks
DECLARE @pSomeSchemaName SYSNAME,
@pSomeTableName SYSNAME;
SELECT @pSomeSchemaName = 'Production',
@pSomeTableName = 'ProductPhoto'
SELECT Ordinal_Position, Column_Name
FROM Information_Schema.Columns
WHERE Table_Schema = @pSomeSchemaName
AND Table_Name = @pSomeTableName
ORDER BY Ordinal_Position
Ah... my bad. I didn't see the word "values" in the requirements.
--Jeff Moden
June 1, 2011 at 9:01 am
WayneS (5/31/2011)
Hi GSquared,This is meant to be run one time to create the procs, that are then used to get the data. Of course, they need some modifications to add a where clause, but other than that, what is wrong with this?
Because if the proc is picked dynamically, the columns returned for each dataset will be different, and that means the front end code has to be MASSIVELY more complex to deal with querying the metadata of the proc before running the proc.
It's better than a single dynamic query for the same thing, but still has huge hurdles the dev doesn't realize he's creating for himself. Ignorance is bliss, till the thing you don't know is which side of the road to drive on, and whether there's an 18-wheeler ahead of you going the opposite way. That's pretty much where the original request is at. (Not the person who wrote the post, the dev who asked for the proc in the first place.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2011 at 10:27 am
Okay, I see where you're coming from. Yes, it would be a bit of front-end code pain to deal with that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2011 at 12:19 pm
Yeah, I would tell the developer, "No. Do it right."
June 2, 2011 at 1:55 pm
I understood the requirement differently.
If the routine would return, say, name and datatype for all columns, then that would be enough to pass
to generic table editors, simple display pages, and spreadsheet output. That seems pretty reasonable.
So I would write something like the following.
(User can omit fields that are not of interest to requestor.)
create proc [dbo].[dbi_tbl_getCols2]( @db varchar(31) = 'yourDB', @tbl varchar(31) = 'yourTable')
as
begin
set nocount on
declare @srvr varchar(15)
set @srvr = @@servername
declare @d1 varchar(31)
set @d1 = case when @db='' then '' else @db + '.' end
declare @x1 varchar(1023)
declare @x2 varchar(1023)
declare @xcmd varchar(2048)
set @x1 =
'select t.name tblName, c.column_name colName
, c.ordinal_position seq, c.data_type
, isnull(c.character_maximum_length,0) maxLen
, isNull(c.numeric_precision,0) num_prec
, isNull(c.numeric_scale,0) num_scale, ac.is_identity, ac.is_computed
'
set @x2 =
'from '+@d1+'sys.tables t
inner join '+@d1+'INFORMATION_SCHEMA.COLUMNS c on c.table_name = t.name
inner join '+@d1+'sys.all_columns ac
on ac.name = c.column_name and t.object_id = ac.object_id
where t.name = ''' + @tbl + ''' order by seq '
set @xcmd = @x1 + @x2
exec (@xcmd)
end
June 2, 2011 at 3:10 pm
Thomas Considine (6/2/2011)
I understood the requirement differently.If the routine would return, say, name and datatype for all columns, then that would be enough to pass
to generic table editors, simple display pages, and spreadsheet output. That seems pretty reasonable.
So I would write something like the following.
(User can omit fields that are not of interest to requestor.)
create proc [dbo].[dbi_tbl_getCols2]( @db varchar(31) = 'yourDB', @tbl varchar(31) = 'yourTable')
as
begin
set nocount on
declare @srvr varchar(15)
set @srvr = @@servername
declare @d1 varchar(31)
set @d1 = case when @db='' then '' else @db + '.' end
declare @x1 varchar(1023)
declare @x2 varchar(1023)
declare @xcmd varchar(2048)
set @x1 =
'select t.name tblName, c.column_name colName
, c.ordinal_position seq, c.data_type
, isnull(c.character_maximum_length,0) maxLen
, isNull(c.numeric_precision,0) num_prec
, isNull(c.numeric_scale,0) num_scale, ac.is_identity, ac.is_computed
'
set @x2 =
'from '+@d1+'sys.tables t
inner join '+@d1+'INFORMATION_SCHEMA.COLUMNS c on c.table_name = t.name
inner join '+@d1+'sys.all_columns ac
on ac.name = c.column_name and t.object_id = ac.object_id
where t.name = ''' + @tbl + ''' order by seq '
set @xcmd = @x1 + @x2
exec (@xcmd)
end
Going from this:
then i have to pass all the column values to application side related to table ABC...so dynamically i have to generate the select code with the table name inside the procedure
It mentions "column values", and "dynamically generate the select code".
That means, to me, actually run the select and return the "column values" in the dataset.
That particular situation is something I've seen more devs ask for than I care to remember. "How come we can't have one proc that selects everything for us?" is a pretty common request. It's always one of those things that seems brilliant until you actually try to do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply