stored procedure gives null values

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

General SQL Server Forums

New to SQL Server Programming

stored procedure gives null values

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-01-18 : 06:06:41

hi this is my stored procedure.i am passing mu column nam and recordname has to be fetched.if run this proceedure i am getting null records only.but i am having records in my table

CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)

)
As
if(@columnname !=' ' and @recordname !=' ')
begin
select userid,user_name,password,role_code,expiry_date from usermaster where '+@columnname+' like '+@recordname+"%"'
end
GO

can any one help to solve this please

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-18 : 06:09:16

You can make use of dynamic sql. But I don't see any point in writing such an SP. Why you are passing column name as a parameter?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-01-18 : 06:18:12

hi harsh_athalye, becos that is client requirement, i want to use column search. can u please help me now

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-18 : 06:24:05

[code]CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)
)
As
IF(@columnname !=' ' and @recordname !=' ')
BEGIN
EXEC('select userid,user_name,password,role_code,expiry_date from usermaster where ' + @columnname + ' like '''+ @recordname + '%'''
END
GO[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 06:24:57

[code]CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)
)
As

declare @sql varchar(8000)

if isnull(@columnname, '') = '' or isnull(@recordname, '') = ''
select @sql = 'select userid, user_name, password, role_code, expiry_date from usermaster where 1 = 0'
else
select @sql = 'select userid, user_name, password, role_code, expiry_date from usermaster where ' + quotename(@columnname) + ' like ''' + @recordname + '%'''

print @sql
--exec (@sql) [/code]
Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-01-18 : 22:08:37

hi harsh_athalye ,peso thanks for ur help i got it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources