March 20, 2008 at 6:32 am
Hello to every one,
I have two parameters
@param1 = dbname.username.table_name
@param2= value
Is there any way that I pass these two parameters to a function and return a bit value either 0 or 1 based on the given @param2 value.
Thanks,
Sri.
March 20, 2008 at 7:32 am
Assuming you want to do something like select Aval from the table var where fld = second parm, the answer is no. You cannot use dynamic sql in a function.
March 20, 2008 at 7:40 am
SriSun (3/20/2008)
Hello to every one,I have two parameters
@param1 = dbname.username.table_name
@param2= value
Is there any way that I pass these two parameters to a function and return a bit value either 0 or 1 based on the given @param2 value.
Thanks,
Sri.
While not 100% sure what you would like to return (does a particular column contain the value? Is there a column with this name? ...) you could use a stored procedure with dynamic SQL, but not a function.
Regards,
Andras
March 20, 2008 at 11:47 am
You can only do this in a CLR function.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2008 at 1:24 pm
There are a couple of options that could work, but it depends on your {param2}. Could you please provide an example of {param2}?
Dave
March 25, 2008 at 9:21 am
Hi All,
This is one of the example code.
create table dbo.employee(ename nvarchar(32))
insert dbo.employee values('Sri')
insert dbo.employee values('Mike')
declare @tablename nvarchar(128), @column_name nvarchar(32), @value nvarchar(32)
declare @emp_status bit
select @tablename = 'dbo.employee' @column_name = 'ename', @value = 'Sri'
set @emp_status = 0
select @emp_status = dbo.fn_does_employee_exists(@tablename,@column_name,@value)
print @emp_status
March 25, 2008 at 10:10 am
SriSun (3/25/2008)
Hi All,This is one of the example code.
create table dbo.employee(ename nvarchar(32))
insert dbo.employee values('Sri')
insert dbo.employee values('Mike')
declare @tablename nvarchar(128), @column_name nvarchar(32), @value nvarchar(32)
declare @emp_status bit
select @tablename = 'dbo.employee' @column_name = 'ename', @value = 'Sri'
set @emp_status = 0
select @emp_status = dbo.fn_does_employee_exists(@tablename,@column_name,@value)
print @emp_status
Well, as mentioned, you cannot do this with a function. Not even CLR. However, you can use a stored procedure like:
CREATE PROC dbo.fn_does_employee_exists
( @tablename sysname
, @column_name sysname
, @value NVARCHAR(32)
, @returnvalue BIT OUTPUT)
AS
BEGIN
DECLARE @q NVARCHAR(1000)
SET @q = 'select 1 AS a from ' + QUOTENAME(@tablename) + ' where '
+ QUOTENAME(@column_name) + ' = N''' + @value + ''''
CREATE TABLE #foo ( a bit )
INSERT #foo
EXEC ( @q )
SELECT TOP 1
@returnvalue = a
FROM #foo
DROP TABLE #foo
END
To call it:
DECLARE @tablename NVARCHAR(128)
, @column_name NVARCHAR(32)
, @value NVARCHAR(32)
DECLARE @emp_status BIT
SELECT @tablename = 'employee'
, @column_name = 'ename'
, @value = 'Sri'
DECLARE @ret BIT
EXECUTE dbo.fn_does_employee_exists @tablename, @column_name, @value,
@ret OUTPUT
PRINT @ret
Still not sure why this is needed though 🙂 Performance wise, if you could, avoid dynamic SQL.
Regards,
Andras
March 25, 2008 at 10:54 am
If you will always be checking the same table, then there is a way to create a function to check a particular passed in column for a particular passed in value. In the 'WHERE' clause, you will be using a case statement for each column in the table.
here is what the function would look like for a table (Employees) that contains Fields: EmpId, First, Middle, Last.
create function fn_FindValue (@vcColumn varchar(25), @vcValue varchar(50))
returns tinyint
as
begin
declare @ICT int
select @ICT = CT
from (select count(*) AS CT
from Employees
where EmpId = CASE WHEN @vcColumn = 'EmpId' THEN @vcValue ELSE EmpId END
and First = CASE WHEN @vcColumn = 'First' THEN @vcValue ELSE First END
and Middle = CASE WHEN @vcColumn = 'Middle' THEN @vcValue ELSE Middle END
and Last = CASE WHEN @vcColumn = 'Last' THEN @vcValue ELSE Last END) a
IF @ICT > 0
RETURN 1
ELSE
RETURN 0
end
Dave Novak
March 25, 2008 at 11:42 am
Thank you so much for your valuable suggestions.
Final question:
There are lots of scenarios at my work where I have to use this type of code, so I wrote procedures instead of functions (which is not allowed).
My only concerned here was, is the stored procedure has same performance level as function.
Thanks,
Sri.
March 25, 2008 at 11:56 am
I think the big concern is that the way you would use this would force single-row processing, which in SQL server is VERY inefficient. That is often enough why functions are "banned".
Given how you intend to use it, on SQL's standard, wrapping it as a SP will be no more or less efficient than your function (if you were to not make it a dynamic SQL scenario), since you're removing its ability to operate on a set. You'd be much better off building this into an EXISTS construct in the actual query you're using - you'd see a lot of gain for doing it that way instead of one row at a time (or "RBAR" as we call that around here: Row By Agonizing Row).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2008 at 12:45 pm
I don't use these functions in a set based queires, only used for verification purposes/pre-checks.
Thanks guys, thanks miller.
Sri.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply