Dynamic Function - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Function

Author

Topic

velliraj
Yak Posting Veteran

59 Posts

Posted - 2010-06-29 : 09:34:44

Hi
Please help me to create a function to pass the input values as Table name and column of that table.

Should return the coulmn values

please help

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 09:36:45

What do you mean by column values on the basis of table name and column name.
I mean value of which row you want to return ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

velliraj
Yak Posting Veteran

59 Posts

Posted - 2010-06-29 : 09:41:25

hi vaibhav,

This is like
select empid from employee

now im pasing the function input as dbo.employee,empid

I need the result of the empid

this should work for any table

hope you have the idea now

velliraj
Yak Posting Veteran

59 Posts

Posted - 2010-06-29 : 09:49:15

DECLARE @colName VARCHAR(500),
@colValue VARCHAR(500),
@tblName VARCHAR(500),
@sql VARCHAR(MAX)

SET @colName = 'consultant_ssn'
SET @tblName = 'consultants'
SET @sql = 'select ' + @colName + ' from ' + @tblName

EXEC ( @sql )

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 09:57:20

For this you need to write stored procedure instead of function
as function doesnot return result set.

try this -


CREATE Procedure GetColumnData 
(
@TableName AS VARCHAR(100),
@ColumnName AS VARCHAR(100)
)
AS
BEGIN

	DECLARE @SQL AS VARCHAR(1000)
	SELECT @SQL = ''	

	IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @TableName AND Column_Name = @ColumnName )
	BEGIN 
		SET @SQL = 'SELECT ' + @ColumnName + ' FROM ' + @TableName
		EXEC ( @SQL )
	END
	ELSE
	BEGIN
		PRINT 'Invalid table or column name'
	END	

END
GO
EXEC GetColumnData 'tablename', 'columnname'

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

velliraj
Yak Posting Veteran

59 Posts

Posted - 2010-06-29 : 10:07:33

yes i got it.

thanks vaibhav for your quick response

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-29 : 10:10:34

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources