table name and sort by column as inputs to a proc | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

table name and sort by column as inputs to a proc

I want to create a stored procedure that accepts
1 – table name as input prameter
2 – a cloumn name to sort by as an input parameter
My code is: Create Procedure dbo.ReturnAllRecordset @TName VarChar (35) As
SET NOCOUNT ON
Begin
Declare @Exists Int, — Return Value
@dynSQL varchar (50),
@order varchar (50) select @dynSQL = ‘SELECT * from ‘ + @TName +’ + ‘ ‘order by’ + @order’
exec(@dynsql) If @@rowcount = 0 — No Record returned
Select @Exists = 2
Else
Select @Exists = 1
Return @Exists End But this code does not work + is there not any other way to do it without dynamic execution as it may hold some improper security issues Thanks a lot Rasha zaki
Web Developer
Cairo, Egypt
You had some problem with the quotes. It should be select @dynSQL = ‘SELECT * from ” + @TName + ”order by’ + @order But I’m not sure I understand what you are trying to do. if you only need to check if records exist, there is not need to SELECT * or to ORDER BY. A simple IF EXISTS (SELECT NULL FROM my_table) …. should be enough.
It would better to write a specific stored procedure for this task for avoid security issues.
if you still need to go with dynamic sql I’d suggest using sp_executesql with parameters.
Make sure you are not using account sa to access the database…
Hi Bambola, I thank you for your help.
I have tried your code but it gave me the following error:
quote:Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘ + @TName + ‘.
Stored Procedure: School.dbo.ReturnAllRecordset
Return Code = 2

My aim is to return all records from the table. And the reason why I need to create a genral procedure that has the table name and the sortby column as inputparams is that I need a lot to retrieve all records from certain tables in my application and I do not want to make a procedure for every one.
Thanks Rasha zaki
Web Developer
Cairo, Egypt
@order is never set, so its null. When you do this and concatinate null, you get a null string for @dynSQL Set @order and with the quotes Bambola said and it should work. Chris
quote:Originally posted by ChrisFretwell @order is never set, so its null. When you do this and concatinate null, you get a null string for @dynSQL Set @order and with the quotes Bambola said and it should work. Chris
I made sure a value is supplied for the param @order but the same error message occurs.
Any help?
Thanks Rasha zaki
Web Developer
Cairo, Egypt
Create Procedure dbo.ReturnAllRecordset @TName VarChar (35) As
SET NOCOUNT ON
Begin
Declare @Exists Int, — Return Value
@dynSQL varchar (50),
@order varchar (50) select @dynSQL = ‘SELECT * from ‘ + @TName + ‘ order by ‘ + @order
exec(@dynsql) If @@rowcount = 0 — No Record returned
Select @Exists = 2
Else
Select @Exists = 1
Return @Exists End MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hi derrickleggett,<br />Thank you very much, your answer has worked.<br />[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />Rasha zaki <br />Web Developer<br />Cairo, Egypt
]]>