how to write this in dynamic sql

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

how to write this in dynamic sql

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-07 : 00:47:03

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

declare @sql varchar(8000)

select u.userid,u.user_name,u.password,c.code_description as role_code,u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.userid like'1%'
order by u.userid

GO

in this stored procedure after where condition i need to use the u.@columnname like '@recordname' order by u.@columnname

how to use this lines in dynamic sql pease help me to do

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-02-07 : 01:15:15

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

Exec ('
select u.userid,u.user_name, u.password, c.code_description as role_code,
u.expiry_date,u.created_date,u.active
from [usermaster] u
inner join [codeMaster] c
on u.role_code=c.code
where u.userid like'1%'
And u.' + @columnname + ' like ''' + @recordname + '''
order by u.' + @columnname
')
[/code]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-07 : 01:32:17

hi russell if i write the following it gives error:

select @sql= ' select u.userid,u.user_name, u.password, c.code_description as role_code,
u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.' + @columnname + ' like ''' + @recordname + '''
order by u.' + @columnname''

please help me to rectify this error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 02:04:48

extra 2 single quote at the end


select @sql= ' select u.userid,u.user_name, u.password, c.code_description as role_code,
u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.' + @columnname + ' like ''' + @recordname + '''
order by u.' + @columnname

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-07 : 02:38:45

hi khtan thanks a lot for reply

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-07 : 03:12:36

hi khtan

this is the query i tried in query analyser: it give two rows as result.

select u.userid,u.user_name,u.password,c.code_description as role_code,u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.user_name like'k%'
order by u.user_name

but i changed this query and i passed variale, and i executedit gives no records.

select @sql= ' select u.userid,u.user_name, u.password, c.code_description as role_code,u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.' + @columnname + ' like ''' + @recordname + ''' order by u.' + @columnname
exec (@sql)

so please what mistake i did in dynamic sql please clear me

it gives null value

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 03:28:29

what is the value you pass to @recordname ? 'k' or 'k%' ?

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-07 : 03:51:43

hi khtan instead of like'k%' i am passing like ''' + @recordname + '%''

so please now help me

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-07 : 03:54:09

hi hi khtan i found my mistake, thanks for ur good help