parameter stored procedure select fileds

Question

parameter stored procedure select fileds

roi.reuven

SSCommitted

Points: 1700
More actions
December 22, 2010 at 8:49 pm

#231005

i want to select only few fileds in a pramater store proc
know i can do it white dinamic sql
create proc [dbo].[MySelect](@filed varchar(max))
as
begin
declare @sql varchar(max)
select @sql = 'select ' + @filed + ' from Customers'
exec (@sql)
end
i do not want to use dinamic sql because sql injection is there outer way

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

You can use sp_ExecuteSQL command with parameters that will help to avoid SQL injection as well as SQL Server can reuse the plan. Query engine needs to just replace the variable values due to parameterized query.

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

Do let me know if you require further help.

Thanks

roi.reuven SSCommitted Points: 1700 More actions · Answer 2

i try do this

declare @SQLString nvarchar(max)

DECLARE @fileds nvarchar(500);

SET @fileds = N'@fileds_value nvarchar(500)';

SET @SQLString =

N'SELECT @fileds_value from Customers';

EXECUTE sp_executesql @SQLString,@fileds,@fileds_value='custid,custname'

the result was this

custid,custname

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

Try this

create proc [dbo].[MySelect](@filed varchar(max))

as

begin

declare @sql nvarchar(max)

select @sql = 'select ' + quotename(@filed,'[') + ' from Customers'

EXECUTE sp_executesql @sql,N'@filed varchar(max)',@filed

end

go

exec [MySelect] N'custid,custname'

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

roi.reuven SSCommitted Points: 1700 More actions · Answer 4

thanks for the help !!

it work for me like this

alter proc [dbo].[MySelect](@filed varchar(max))

as

begin

declare @sql nvarchar(max)

select @sql = 'select ' + @filed + ' from Customers'

EXECUTE sp_executesql @sql,N'@filed varchar(max)',@filed

end

go

exec [MySelect] N'[custname],[custid]'

thanks again