Share via

How to use Declare Variable In Where Condition in SQL Storedprocedure

Question

Monday, July 14, 2014 5:00 AM

Hi, 

Please i need your help in helping to solve this SQL Query. I wan to use Declare Variable at left side of where conditional in Sql storedprocedure,

Something Like

CREATE PROCEDURE [dbo].[GetInfo]
(
@Param nvarchar(50),
@ParamValue nvarchar(50)
)
AS

BEGIN

SET @Param = 'Name'
SET @ParamValue = 'Johnson'

Select FullName FROM PeopleData WHERE @Param = @ParamValue

END

But the @Param is not working because is at the left side of the Where Condition.

The @Param Is going to be a dynamic data e.g FirstName, LastName, Age, Gender. Please any ideal about this.

Thanks

All replies (13)

Monday, July 14, 2014 5:05 AM ✅Answered

You should use Dynamic sql for this. Read

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Wednesday, July 16, 2014 2:46 AM ✅Answered

Hi Dear,

This is your sample code as your requirement.

create table PeopleData (name varchar(100), fullname varchar(500))

insert into PeopleData values 
('Johnson','abc Johnson')
,('xyz','abc xyz')
,('ttt','tt abc')

go
create PROCEDURE [dbo].[GetInfo]
(
    @Param nvarchar(50),
    @ParamValue nvarchar(50)
)
AS

BEGIN

--SET @Param = 'Name'
--SET @ParamValue = 'Johnson'

--Select FullName FROM PeopleData WHERE @Param = @ParamValue
declare @strSql nvarchar(max);
set @strSql = 'Select FullName FROM PeopleData WHERE '  + @Param + ' = '''+@ParamValue +''''
exec sp_executesql @strSql
print @strSql
end
go

--check this various parameter entry to get desired result
exec GetInfo 'name','johnson'
exec GetInfo 'name','xyz'
exec GetInfo 'name','abc'
exec GetInfo 'name','ttt'
go

drop table PeopleData
drop procedure GetInfo

Monday, July 14, 2014 5:21 AM

Thank wmec

What i meant is that the OUT Of @Param Could be FirstName Or LastName Or Age Or Gender to be use on the Where Condition at the left hand side of the Query

Thanks

Monday, July 14, 2014 5:23 AM

Yes, using Dynamic Sql, you can check against different columns dynamically.

Tuesday, July 15, 2014 4:58 PM

Hi,

Yes but it won't work. Parameters are to to transmit literal values. It won't replace an arbitrary part of an SQL Statement such as a table or column name. So you do have to use dynamic sql in this case.

Wednesday, July 16, 2014 3:06 AM

Hi Larshittu,

You can do this using dynamic queries.

DECLARE @COLUMNNAME VARCHAR(50) SET @COLUMNNAME='LastName'

Declare @SQL VARCHAR(4000)
SET @SQL = 'SELECT * FROM TableName WHERE ' + @COLUMNNAME + '= ''XYZ''';
exec(@SQL);

Regards

Pawan

Wednesday, July 16, 2014 5:49 AM

CREATE PROCEDURE [dbo].[GetInfo] 
(
    @Param nvarchar(50),
    @ParamValue nvarchar(50)
)
AS
BEGIN

SET @Param = 'Name'
SET @ParamValue = 'Johnson'

You can use case statement to do condition of two variable
Select FullName 
FROM PeopleData 
WHERE 1=CASE WHEN @Param = @ParamValue THEN 1 ELSE 0 END


END

Wednesday, July 16, 2014 5:58 AM

Thanks so much my friend.

let me show you the real query

ALTER PROCEDURE [dbo].[CalculateBill] 
(
@Param nvarchar(50),
@ParamValue nvarchar(50),
@Locationnvarchar(50)
)
AS
        
BEGIN
    
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        SET NOCOUNT ON;
        DECLARE @CustCoden varchar(50)
    BEGIN TRY
        DECLARE HCodeCursor Cursor FAST_FORWARD  for Select CustCode FROM CustomerInfo WHERE  @Param = @ParamValue AND Location= @Location

        Open HCodeCursor
        Fetch Next From HCodeCursor Into @CustCode

        WHILE @@FETCH_STATUS = 0
            BEGIN
                
                --Actions i want to the tables
                
                Fetch Next From HCodeCursor Into @CustCode
                --BREAK
            END
            Close HCodeCursor
            Deallocate HCodeCursor
    END TRY
    BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
END

But i want @Param and @ParamValue value to work base on what is coming from the front-end of the application which is C#.

@Param if the only problem i have in this stored-procedure to get the value passed to it for the query

Thanks

Wednesday, July 16, 2014 6:22 AM

Thanks Yrb

When i don't Understand what you're trying to do with CASE If you can explain it i will reply appreciate thanks

WHERE 1=CASE WHEN @Param = @ParamValue THEN 1 ELSE 0 END

Wednesday, July 16, 2014 6:44 AM

WHERE 1=CASE WHEN @Param = @ParamValue THEN 1 ELSE 0 END

In case statement, comparison between two variable is done. IF condition is true, we are returning 1 which make (1=1, so where statement is true) else 0.

So based on condition, we are returning value 1/0 to make where statement to execute.

Wednesday, August 6, 2014 9:36 AM

Please use dynamic query and execute that

Wednesday, August 6, 2014 9:48 AM

Edit : The @Param = @ParamValue approach won't work.

It won't work. It would match only if @ParamValue contains the column name as the @Param *variable* contains just that.

The point is that @Param is NOT replaced by the underlying string value. It is just a string variable so here you just compare two variables.

As pointed several times the only way to do that is :

  1. to use dynamic SQL
  2. or to do something such as : WHERE (@Param='Col1' AND Col1=@ParamValue ) OR (@Param='Col2' AND Col2=@ParamValue)

Wednesday, August 6, 2014 1:03 PM

If you want to gain more information about your question, you should give Erland's "The Curse and Blessings of Dynamic SQL" a try. It is very helpful to gain knowledge in your coding career.

"

http://www.sommarskog.se/dynamic_sql.html

  • Last updated on