CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user3066155
user3066155

Reputation: 157

Dynamic Column Name and Value into Where clause

I have a procedure that I working on and I don't know what's going wrong. I have reviewed all other sites and could not find the issue that I'm having

I want to create procedure that has a dynamic where clause base on a combination of bits being sent to the procedure. I don't want to have to create a bunch of similar procedures because they have slightly different conditions.

I'm placing the below query into a cursor then looping through the cursor. Please help.

CREATE PROCEDURE [dbo].[procContainTest] (
    @USE_A BIT,
    @USE_B BIT,
    @ValueA VARCHAR(50),
    @ValueB VARCHAR(50),
    @USERID VARCHAR(50)
    )
AS
DECLARE @TEMP_Col1 INT,
    @TEMP_Col2 INT,
    @TEMP_Col3 VARCHAR(50),
    @TEMP_Col4 VARCHAR(50),
    @TEMP_Col5 VARCHAR(50),
    @POINT_ONE NVARCHAR(50),
    @POINT_TWO NVARCHAR(50)

SET @TRIGGER = 0

WHILE @TRIGGER = 0
BEGIN
    -- F2 Booking Term
    IF @USE_A = 1
        AND @USE_B = 1
    BEGIN
        SET @POINT_ONE = 'ColName2'
        SET @POINT_TWO = 'ColName3'
    END

    -- F6 Booking Term
    IF @USE_A = 0
        AND @USE_B = 1
    BEGIN
        SET @POINT_ONE = 'ColName1'
        SET @POINT_TWO = 'ColName2'
    END

    DECLARE INNER_CURSOR CURSOR
    FOR
    SELECT TOP 1 TEMP_Col1 INT,
        TEMP_Col2,
        TEMP_Col3,
        @TEMP_Col4,
        @TEMP_Col5
    FROM TEMP_Table
    WHERE @POINT_ONE = + '''' + @ValueA + ''''
        AND @POINT_TWO = + '''' + @ValueB + ''''
        AND USERID = @USERID
    ORDER BY LENGTH

Upvotes: 2

Views: 14532

Answers (3)

Tanmay Nehete
Tanmay Nehete

Reputation: 2196

this is probably help you:

SELECT
    id, first, last, email, notes
FROM
    My_Table
WHERE
    CASE ''''+@column_name_variable+''''
        WHEN ''''+column_1+''''=1 THEN column_1
        WHEN ''''+column_2+''''=2 THEN column_2
        ...
        ELSE 'not null'
    END IS NOT NULL

Upvotes: 3

Aram
Aram

Reputation: 5705

l You can put your Select Statmement in a variable like:

declare @YourSelectStatement nvarchar(max)

set @YourSelectStatement = '    SELECT TOP 1 TEMP_Col1 INT,
        TEMP_Col2,
        TEMP_Col3,
    FROM TEMP_Table
    WHERE ' + @POINT_ONE + '=' + @ValueA + '
        AND ' + @POINT_TWO + '=' + @ValueB + '
        AND USERID = ' + @USERID + '
    ORDER BY LENGTH'

sp_executesql(@YourSelectStatement)

Upvotes: 4

Jeff Hornby
Jeff Hornby

Reputation: 13640

I would avoid the use of dynamic SQL altogether. You can eliminate your IF statements and embed the same logid in your SELECT statement like this:

SELECT TOP 1 
    TEMP_Col1,
    TEMP_Col2,
    TEMP_Col3,
    TEMP_Col4,
    TEMP_Col5
FROM TEMP_Table
WHERE (@USE_A = 1 AND @USE_B = 1 AND ColName2 = '''' + @ValueA + '''' AND ColName3 = '''' + @ValueB + '''') 
OR (@USE_A = 0 AND @USE_B = 1 AND ColName1 = '''' + @ValueA + '''' AND ColName2 = '''' + @ValueB + '''')

Upvotes: 2

Related Questions