CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005t-sql
Moon
Moon

Reputation: 35265

SQL SP: Dynamic Query

I am trying get a col and value as a function parameter and planning to use them in a query.

Unfortunately, my value for @Col is being treated like a string and not a column identifier.

ie, if I specify name as a value for the @Col parameter, it will be treated like 'name' instead of name and hence, the call to the function always returns NULL as a result

Have you came across similar situations? Could you please recommand me a better way to deal with this situation?

Here is my Function for your reference:

CREATE FUNCTION [dbo].[FN_FindIdBy] 
(
    @Col NVARCHAR(255),
    @Value NVARCHAR(255)
)
RETURNS INT
AS
BEGIN
    DECLARE @Id INT = NULL

    SET @Id = (SELECT ID FROM dbo.MYWORK WHERE (CONVERT(NVARCHAR(255), @Col) = @Value))
    IF @Id IS NOT NULL RETURN @Id

    RETURN NULL
END

Thanks a lot for looking into this.

Upvotes: 0

Views: 184

Answers (2)

Vinnie
Vinnie

Reputation: 3929

The following works, but you have to use it as a procedure and create dynamic sql. 

create table MYWORK (ID int identity, Name nvarchar(255))

insert into MYWORK(Name)
select 'a'
union select 'b'
union select 'c'
union select 'd'
union select 'e'
union select 'f'


CREATE procedure [dbo].[EPG_FN_FindIdBy] 
@Col NVARCHAR(255),
@Value NVARCHAR(255)
AS
BEGIN
    DECLARE @Id nvarchar(255)
    , @ParmDefinition nvarchar(255)
    , @sql nvarchar(max)

    set @sql = N'SELECT @IdOUT = ID FROM dbo.MYWORK WHERE '+ @Col +' = ''' + @Value + ''''
    set @ParmDefinition = N'@IdOUT nvarchar(255) OUTPUT'

    PRINT @sql

    EXECUTE sp_executesql @sql,@ParmDefinition, @IdOUT = @Id OUTPUT
    SELECT @Id as ID

END

Run this and it'll return the matching row

Exec dbo.EPG_FN_FindIdBy @Col = 'Name', @Value = 'a'

And for a NULL

Exec dbo.EPG_FN_FindIdBy @Col = 'Name', @Value = 'g'

Upvotes: 2

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Yeah, there's almost always a better way to query than using dynamic SQL.

Check out this usage of the CASE operator.

SELECT id
FROM dbo.MYWORK
WHERE CASE @Col
    WHEN 'a' THEN [a]
    WHEN 'b' THEN [b]
    WHEN 'c' THEN [c]
    END = @Value

Where the table has columns [a], [b] and [c].

Upvotes: 0

Related Questions