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-10 : 03:04:06

CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)
)
As
declare @count bigint
declare @sql varchar(8000)

begin
select @sql='select ' + @count + '=count(*) from usermaster where ' + @columnname + ' like ''' + @recordname + '%'' '
exec(@sql)
end
GO

if i use like this it gives error,please help me to rectify this error

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-02-10 : 03:57:02

You need to use the sp_ExecuteSql procedure
http://support.microsoft.com/kb/262499

That article describes it pretty well.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-10 : 04:10:31

hi Vinnie881,can u please modify my coding for nmy needs please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-10 : 04:55:28

[code]
CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50),
@count bigint OUTPUT
)
As
begin

declare @sql nvarchar(4000)

select @sql='select @count = count(*) from usermaster where ' + @columnname + ' like ''' + @recordname + '%'' '
~~exec(@sql)~~
exec sp_executesql @sql, N'@count bigint OUTPUT', @count OUTPUT
end
GO
[/code]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-10 : 06:26:19

hi khtan,

this is my spc:

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

declare @sql varchar(8000)
declare @count1 bigint
create table #temp(userid varchar(50),user_name varchar(50))

if isnull(@columnname, '') = '' or isnull(@recordname, '') = ''
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 order by u.userid'
else
begin
declare @sq1l nvarchar(4000)

select @sq1l='select @count = count(*) from usermaster where ' + @columnname + ' like ''' + @recordname + '%'' '
exec(@sq1l)
exec sp_executesql @sq1l, N'@count bigint OUTPUT', @count OUTPUT
set @count1= @count
if(@count1>=1)
begin
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)
end
else
if(@count1=0)
begin

insert into #temp values('no','no')
select * from #temp
end
end
GO

i used ur coding in my spc it compiled sucess fully. actually if i pass column name correctly and record name worngly my result has to be

userid user_name
no no.

but i am getting this as my result

becos @count value is not passing in the @count1.so please help me to solve the error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-10 : 09:44:29

can you explain what are you trying to achieve here ?

Kristen
Test

22859 Posts

Posted - 2007-02-10 : 10:21:56

khtan: FWIW I would parameterise @recordname too - to improve the chances of getting the query cached.


CREATE PROCEDURE HRUser_spsearch
(
    @columnname varchar(50),
    @recordname varchar(51),
    @count bigint OUTPUT
)
As
begin

    declare @sql nvarchar(4000)

    SELECT @recordname = @recordname + '%'

    select @sql='select @count = count(*) from usermaster where ' + @columnname + ' like @recordname'
    exec sp_executesql @sql, N'@count bigint OUTPUT, @recordname varchar(51)', @count OUTPUT, @recordname
end
GO