how to pass parameter which is also the table fied

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to pass parameter which is also the table fied

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2007-07-23 : 07:05:31

Dear all,
i am using sql server 2005.
i have wrote stored procedure where field to select is also passed b by paramter but when i execute the query it does not show any data but when i run same query through analyzer it show the record.
i am not able to understand the problem. please guide me .

my Sp is

-- style search
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter procedure [search_d2]
@para1 varchar(50),
@value1 varchar(50),
@para2 varchar(50) = null,
@value2 varchar(50) = null,
@para3 varchar(50)=null,
@value3 varchar(50) = null
as
if (@para1 is not null and @para2 is null)
begin;
select photopath , @para1 from stylesearchtbl where @para1 = @value1;

end;
else
if(@para2 is not null and @para3 is null)

begin;
select photopath ,@para1 , @para2 from stylesearchtbl where @para1 = @value1 and @para2 = @value2;
end;
else
if(@para3 is not null and @para4 is null )
begin;
select photopath ,@para1,@para2 , @para3 from stylesearchtbl where @para1 = @value1 and @para2 = @value2 and @para3 =@value3;
end;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-23 : 10:27:25

You need to use dynamic sql, you cannot do SELECT @Var FROM table.

SET @sql = 'SELECT photopath, ' + @para1 + ' FROM stylesearch '

EXEC (@sql)

Jim

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2007-07-23 : 10:46:55

i have figured out the problem as i pass field name as parameter query executes as

select photopath ,'categoryid' from stylesearchtbl where 'categoryid' = 'BAN'

instead of

select photopath ,categoryid from stylesearchtbl where categoryid= 'BAN'

how i can make the thing correct pleease help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources