January 23, 2013 at 6:12 pm
hi guys i need your help for the below scenario
i need to create a function which returns primary key value , the input parameters are tablename and id( this value is unique).
so if i pass tablename and id value, the primary key value should correspond to the id value in the tablename .
this is what i tried but i am not able to find how to insert the id value
IF OBJECT_ID('fgetGuidbyId') IS NOT NULL
DROP FUNCTION DBO.fgetGuidbyId;
go
CREATE FUNCTION dbo.fgetGuidbyId
(
@Tablename VARCHAR(50),
@TM_GUID varchar(50)
)
RETURNS varchar(1000)
AS
begin
Declare @GUID varchaR(1000);
SET @GUID = ''
DECLARE @AvtTable table (TABLE_NAME varchar(1000))
INSERT INTO @AvtTable(TABLE_NAME)
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS a
inner join sys.tables b
on a.table_name=b.name
where COLUMN_NAME like '%TM_GUID%' and type='u'
DECLARE TABLE @TEMP (PCOL varchar(100),TAB VARCHAR(100), TM varchar(100))
INSERT INTO @TEMP (PCOL ,TAB , TM )
SELECT CU.COLUMN_NAME,cu.table_NAME,D.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
ON CU.table_name = tc.table_name
AND CU.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS D
ON D.table_name = tc.table_name
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND D.COLUMN_NAME like '%TM_GUID%'
SET @GUID = (SELECT (SELECT COLU_NAME FROM @PRIMARYTABLE)FROM @AvtTable WHERE TM_GUID = @TM_GUID)
return @GUID
END
any suggestions is much appreciated
thanks in advance
January 23, 2013 at 7:34 pm
I am confused and perhaps just missing the obvious. You just want to return the column name for the column that's the PK in any given table? DECLARE @Table sysname = 'YourTable'
SELECT
c.name [PKColumn]
FROM sys.tables b
INNER JOIN sys.columns c
ON b.object_id = c.object_id
WHERE
b.type = 'u'
AND c.is_identity = 1
AND b.object_id = OBJECT_ID(@Table)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 23, 2013 at 8:40 pm
MyDoggieJessie (1/23/2013)
I am confused and perhaps just missing the obvious. You just want to return the column name for the column that's the PK in any given table?DECLARE @Table sysname = 'YourTable'SELECT
c.name [PKColumn]
FROM sys.tables b
INNER JOIN sys.columns c
ON b.object_id = c.object_id
WHERE
b.type = 'u'
AND c.is_identity = 1
AND b.object_id = OBJECT_ID(@Table)
Not really, this is what you want:
declare @TableName sysname;
-- set @TableName = 'YourTableName' << Put your table name here and uncomment the line
select
idx.name
from
sys.indexes idx
where
idx.object_id = object_id(@TableName) and
idx.is_primary_key = 1;
January 23, 2013 at 9:06 pm
thanks for quick reply but thats not what iam looking for
here is the scenario
there is a column called guid with unique values , if i pass a guid value with the table name i should get the primary key value of that table with corresponding guid value that i pass as input parameter
i.e
table xyz
guid pk value column
3243 1
34 2
34134 4
34 5
2144 6
if i pass tablename as xyz and guid as 34 i should get 2 as return value
this is how iam trying with stored proc
IF OBJECT_ID('getGuidbyId') IS NOT NULL
DROP procedure DBO.getGuidbyId;
go
CREATE procedure dbo.getGuidbyId
(
@Tablename VARCHAR(50),
@TM_GUID varchar(50)
)
--@AvtTable table (COLUMN_NAME varchar(1000))
AS
begin
declare @sql varchar(1000)
DECLARE @SSql varchar(500)
DECLARE @NCount int
DECLARE @Parm varchar(50)
SET @Parm ='@NCountOutput int'
--DECLARE @AvtTable table (COLUMN_NAME varchar(1000))
--INSERT INTO @AvtTable(COLUMN_NAME)
if OBJECT_ID('tempdb..#tempvalue') is not null
drop table #tempvalue
create table #tempvalue
(colname varchar(1000)
)
insert into #tempvalue
SELECT @SSql = 'SELECT @NCountOutput = ' + CU.COLUMN_NAME + ' FROM '+ @Tablename +' WHERE TM_GUID ='+@TM_GUID
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
ON CU.table_name = tc.table_name
AND CU.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS D
ON D.table_name = tc.table_name and d.column_name = cu.column_name
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
and d.table_name = @Tablename
exec sp_executesql @SSql, @Parm,@NCountOutput = @NCount
January 23, 2013 at 9:18 pm
Nope, still confused. Perhaps if you took the time to read the first article I reference below regarding asking for help and provided us with DDL (CREATE TABLE statement(s)) for the table(s) involved, some sample data (not live production data) that mimics live data (as a series of INSERT statements) for the table(s) involved, the expected results based on the sample data and input value(s) to the function we might be able to help.
What you have posted so far really doesn't make much sense to me, but perhaps others may have better luck than I.
January 23, 2013 at 9:43 pm
CREATE TABLE [dbo].[ALIAS_TYP](
[USER_ID] [varchar](50) NOT NULL,
[EFFECTIVE_DATE] [datetime] NOT NULL,
[END_DATE] [datetime] NOT NULL,
[LOCATION_ID] [int] NOT NULL,
[TM_ID] [int] NOT NULL,
[ALIAS_ID] [int] NOT NULL,
[TM_GUID] [varchar](50) NULL,
CONSTRAINT [PK_ALIAS_TYP] PRIMARY KEY CLUSTERED
(
[ALIAS_ID] ASC
) ON [PRIMARY]
insert into alias_typ
values('admin','1900-01-01 00:00:00.000','2008-12-05 16:45:14.700',1,0,1,1)
values('appuser','1900-01-01 00:00:00.000','2008-12-05 16:45:14.700',1,1,2,2)
values('admin','1900-01-01 00:00:00.000','2008-12-05 16:45:14.700',1,1,3,4)
input paramteters for function or stored proc
i.e alias_typ,4
required output value should be 3 which is alias_id column value for respective tm_GUID value 4
January 23, 2013 at 10:10 pm
achtro (1/23/2013)
CREATE TABLE [dbo].[ALIAS_TYP]([USER_ID] [varchar](50) NOT NULL,
[EFFECTIVE_DATE] [datetime] NOT NULL,
[END_DATE] [datetime] NOT NULL,
[LOCATION_ID] [int] NOT NULL,
[TM_ID] [int] NOT NULL,
[ALIAS_ID] [int] NOT NULL,
[TM_GUID] [varchar](50) NULL,
CONSTRAINT [PK_ALIAS_TYP] PRIMARY KEY CLUSTERED
(
[ALIAS_ID] ASC
) ON [PRIMARY]
insert into alias_typ
values('admin','1900-01-01 00:00:00.000','2008-12-05 16:45:14.700',1,0,1,1)
values('appuser','1900-01-01 00:00:00.000','2008-12-05 16:45:14.700',1,1,2,2)
values('admin','1900-01-01 00:00:00.000','2008-12-05 16:45:14.700',1,1,3,4)
input paramteters for function or stored proc
i.e alias_typ,4
required output value should be 3 which is alias_id column value for respective tm_GUID value 4
Just to be sure, the table being queried is dynamic, passed in to the procedure being called, correct? Also, for any table name passed in, the primary key of the table is always the column to be queried?
January 23, 2013 at 10:17 pm
yes thats correct
January 23, 2013 at 11:08 pm
This is the best stab I can take at it, perhaps Lynn can drum up a much nicer approach 🙂
Main Scalar FunctionALTER FUNCTION dbo.fx_GetAliasID (
@Table sysname, @Value varchar(6)
)
RETURNS varchar(500) AS
BEGIN
DECLARE @Name varchar(25), @Object varchar(25), @Column varchar(25)
;WITH X (iName, iObject, iColumn) AS (
SELECT b.name, b.object_id, c.column_id
FROM sys.tables b
INNER JOIN sys.indexes s
ON b.object_id = s.object_id
INNER JOIN sys.index_columns c
ON s.object_id = c.object_id
WHERE
b.type = 'u'
AND s.is_primary_key = 1
AND c.object_id = OBJECT_ID(@Table)
) SELECT @Name = iName, @Object = iObject, @Column = iColumn FROM X
SET @Column = (SELECT c.name FROM sys.tables b
INNER JOIN sys.columns c ON b.object_id = c.object_id
WHERE b.type = 'u' AND b.object_id = OBJECT_ID(@Name) AND c.column_id = @Column)
RETURN ('SELECT ' + @Column + ' FROM ' + @Name + ' WHERE TM_GUID = ' + @Value)
END
How to call it:DECLARE @SQL varchar(500)
SET @SQL = (SELECT dbo.fx_GetAliasID ('ALIAS_TYP', 4))
EXEC (@SQL) Returns 3
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 24, 2013 at 6:50 am
Still confused, how do you know that the value you want is in the column named ALIAS_ID? I can see how you find the column you want to query on as it is always the primary key, in this case TM_GUID.
January 24, 2013 at 8:01 am
we doesn't know the value for ALIAS_ID , if you pass the TM_GUID value then the corresponding value for alias_id should result,thanks for all the help and i finally changed the code from function to stored proc and the final code is looked very simple ,
CREATE PROCEDURE getid(@TableName varchar(150), @TM_GUID VARCHAR(100))
AS
DECLARE @Sql nvarchar(500)
SELECT @Sql = ('SELECT ' + CU.COLUMN_NAME + ' FROM ' + @TableName + ' WHERE TM_GUID = ''' + @TM_GUID + '''')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON CU.table_name = tc.table_name AND CU.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS D ON D.table_name = tc.table_name and d.column_name = cu.column_name
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' and d.table_name = @TableName
exec sp_executesql @Sql
thanks again for your patience and all the help
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply