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.
| Author |
Topic |
|
gpc44
Starting Member
35 Posts |
 Posted - 2011-11-03 : 05:37:55
|
Hello,this Function will get the Value from a Table based onColumn, Table, and Where-Criteria. The SQL-String was generated but I have a Problem, to execute the SQL-Statement with EXEC in a tsql-function. I only can get back the tSQL-String - not the Search-Value.Is there a way, to realise ist.many thanks !!LGNicole-- =============================================CREATE FUNCTION [dbo].[Dlookup] ( @ColumnName NVARCHAR(512) , @TableOrViewName NVARCHAR(512) , @Criteria NVARCHAR(512) )RETURNS VARCHAR(512)-- =====================================================================================-- Author: -- Create date: <01.11.2011>-- Description: Wert eines bestimmten Felds aus einer angegebenen Datensatzgruppe (einer Domäne) abzurufen-- Bsp.: SET @st = dbo.DLookup('LastName','tPerson','pkPerson=''4197''')-- =======================================================================================AS BEGIN DECLARE @sql NVARCHAR(MAX) DECLARE @sqlValue SQL_VARIANT DECLARE @rc INT DECLARE @ReturnValue NVARCHAR(2000) SET @ColumnName = NULLIF(LTRIM(RTRIM(@ColumnName)), '') IF @ColumnName IS NULL BEGIN RETURN 'ColumName fehlt' END SET @TableOrViewName = NULLIF(LTRIM(RTRIM(@TableOrViewName)), '') IF @TableOrViewName IS NULL BEGIN RETURN 'TableOrViewName fehlt' END SET @Criteria = NULLIF(LTRIM(RTRIM(@Criteria)), '') SET @sql = N'SELECT TOP(1) ' + @ColumnName + N' FROM ' + @TableOrViewName IF @Criteria IS NOT NULL SET @sql = @sql + N' WHERE ' + @Criteria RETURN (@sql) END |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
TSQL Dlookup Function