Function to return primary key column value

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Function to return primary key column value

11 posts, Page 1 of 2

1 2 »»

Function to return primary key column value

Rate Topic

Display Mode

Topic Options

Author

Message

achtro

Posted Wednesday, January 23, 2013 6:12 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 16, Visits: 296

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

Post #1410862

MyDoggieJessie

Posted Wednesday, January 23, 2013 7:34 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 2,035, Visits: 3,759

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; They'll drag you down to their level and beat you with experience" Wink

Post #1410871

Lynn Pettis

Posted Wednesday, January 23, 2013 8:40 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 21,588, Visits: 27,379

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;

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1410877

achtro

Posted Wednesday, January 23, 2013 9:06 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 16, Visits: 296

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

Post #1410881

Lynn Pettis

Posted Wednesday, January 23, 2013 9:18 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 21,588, Visits: 27,379

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1410884

achtro

Posted Wednesday, January 23, 2013 9:43 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 16, Visits: 296

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

Post #1410886

Lynn Pettis

Posted Wednesday, January 23, 2013 10:10 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 21,588, Visits: 27,379

achtro (1/23/2013)

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?

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1410889

achtro

Posted Wednesday, January 23, 2013 10:17 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 16, Visits: 296

yes thats correct

Post #1410890

MyDoggieJessie

Posted Wednesday, January 23, 2013 11:08 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 2,035, Visits: 3,759

This is the best stab I can take at it, perhaps Lynn can drum up a much nicer approach Smile

Main Scalar Function

ALTER 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; They'll drag you down to their level and beat you with experience" Wink

Post #1410907

Lynn Pettis

Posted Thursday, January 24, 2013 6:50 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 21,588, Visits: 27,379

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.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1411113

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions