Get Column Value
-
I'm sure you're reading this post because you're laughing to yourself..."What a dumb question!".
However, what I need to do is to be able to get a Value from a Table's Column Dynamically. In other words, "Table" and "Column" are Parameters which must be executed Dynamically. The only way I could find to do this was to build a Dynamic SQL Statement and execute it. However, Dynamic SQL can only be used in Stored Procs. Thus, the value can never be returned to some other calling procedure because the EXEC statement cannot return a Value, only a Table with a value(s) in it.
The Columns MUST be determined at Execution Time and NOT be "hard-coded" because I'd like to run this Procedure for ANY Table.
What I am ultimately trying to accomplish it something like this:
- ColID ColName OldValue NewValue
- 1 ID 1 2
- 2 FName George Martha
- 3 LName Wilson Wilson
Any ideas??
-
While the EXEC statement can't return a value, sp_ExecuteSQL surely can.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Can you or anyone possibly give me an Example of sp_ExecuteSQL? I can never get that thing to work with Parameters! Thanks for the Help!
-
USE Pubs
GO
DECLARE @stmt nvarchar(4000)
DECLARE @rowcount int
DECLARE @table nvarchar(255)
SET @table = 'authors'
SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table
EXEC sp_executesql @stmt, N' @count bigint output', @rowcount OUTPUT
IF @rowcount > 0
BEGIN
SELECT @rowcount AS Anzahl
END
RETURN
Anzahl
-----------
23
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks, here's what I came up with
CREATE PROCEDURE
dbo.spGetColumnValue
/*
This Procedure Returns a Value for a Column Dynamically
*/
(
@Table
VarChar(128),
@Column
VarChar(128),
VarChar(256), -- For Composite Keys, Pass Values seperated by Colons... 1:1:2
@Value
VarChar(2000) = Null Output
)
AS
-- Variables
Set NoCount On
Declare
@Keys Cursor, @ColName VarChar(128), @KeyCount Int, @KeyColumn VarChar(8000)
Declare
@sql NVarChar(4000)
-- Determine the Key Columns from the Table
Select
@KeyColumn = '', @KeyCount = 0
Select
@KeyCount = Count(*) From Information_Schema.Key_Column_Usage CU
Inner Join Information_Schema.Columns C ON (CU.Table_Name = C.Table_Name) And (CU.Column_Name = C.Column_Name)
Where (CU.Table_Name = @Table) And (CU.Constraint_Name IN(Select Name From SysObjects Where Xtype = 'PK'))
If
(@KeyCount = 0)
RETURN(0) -- Exit if there are no Keys
Else If
(@KeyCount = 1)
BEGIN
Select @KeyColumn = C.Column_Name
From Information_Schema.Key_Column_Usage CU
Inner Join Information_Schema.Columns C ON (CU.Table_Name = C.Table_Name) And (CU.Column_Name = C.Column_Name)
Where (CU.Table_Name = @Table) And (CU.Constraint_Name IN(Select Name From SysObjects Where XType = 'PK'))
END
Else
BEGIN
Set @Keys = Cursor Fast_Forward For
Select C.Column_Name
From Information_Schema.Key_Column_Usage CU
Inner Join Information_Schema.Columns C ON (CU.Table_Name = C.Table_Name) And (CU.Column_Name = C.Column_Name)
Where (CU.Table_Name = @Table) And (CU.Constraint_Name IN(Select Name From SysObjects Where XType = 'PK'))
Open @Keys
Fetch Next From @Keys Into @ColName
While (@@Fetch_Status = 0)
BEGIN
Set @KeyColumn = @KeyColumn + ' Convert(VarChar(50), ' + @ColName + ') + '':'' +'
Fetch Next From @Keys Into @ColName
END
Close @Keys
DeAllocate @Keys
Set @KeyColumn = SubString(@KeyColumn, 1, Len(@KeyColumn) - 7)
END
-- Determine the Column Value
Set
@sql = N'Select @Value = ' + @Column + ' From ' + @Table + ' Where ' + @KeyColumn + ' = ''' + @key + ''''
Exec
sp_ExecuteSQL @sql, N'@Value VarChar(2000) Output', @Value Output
-- Return the Value
RETURN
(1)
-
Glad I could help
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply