stored proc
-
Hello,
Thanks for your replies - I need to implement paging in an aspx page which is the reason i am attempting this sproc. When i executed the proc without a where condition parameter it worked fine but when i try to set the where param it bombs, so i tried the following
declare @active_flg char(1), @SortFld varchar(20), @SortType varchar(4)
set @active_flg='y'
set @sortfld='project_desc'
set @sorttype='asc'
exec ('SELECT PROJECT_ID,PROJECT_DESC,ACTIVE FROM PROJECT WHERE ACTIVE = ' + @ACTIVE_Flg + ' ORDER BY ' + @SortFld + ' ' + @SortType)
Error message i get is
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'y'.
I am clueless as to why is the value 'Y' being treated as a column name.
Thanks for your help
Hey All,
Need some help with a stored proc. I am a newbie to SQL Server and this is the sproc i wrote
CREATE PROCEDURE TEST
@SortField VARCHAR(20),
@SortType VARCHAR(4)
AS
EXEC ('SELECT * FROM table WHERE ColumnName =' + @SortField + 'ORDER BY ' + @SortType)
GO
Purpose is to order the table by a columnname and the sort order as either descending or ascending.
when i execute this as
test 'columnName','desc' i get an error message as
Syntax error near desc.
If i exclude the sorttype then the procedure works fine. Can some one please help me out with this?
Thanks a bunch
Nask
Edited by - nask on 12/27/2002 11:03:44 AM
-
Tha statement you are creating is this:
SELECT *
FROM table
WHERE ColumnName =columnName
ORDER BY desc
I think you meant something like this:
SELECT *
FROM table
ORDER BY columnName desc
So change your code to this:
EXEC ('SELECT * FROM table ORDER BY ' + @SortField + ' ' + @SortType)
Better yet, do not sort in a stored proc. Do it after returning your recordset to avoid dynamic SQL. It is not good. Also, do not use 'SELECT *'. It is not good practice. List all the columns you need to select explicitly.
Best of luck.
Michael
-
I agree with mromm.
I also would suggest using sp_sqlexec instead of exec if you stay with dynamic sql. It generates a much more reusable execution plan, and has the added benefit of returning results if used correctly.
Oh, and the Y is being treated as a column because you don't have " around it.
Edited by - scorpion_66 on 12/27/2002 11:44:00 AM
-
I typically add 'set nocount on' at the very top, saves sending back info generally not used on the client. Agree with others about not using dynamic sql. If you only have a few options you can do some if/else logic to keep your code static. If it truly needs to be dynamic you're better off building it on the client and submitting it (ADO does use sp_executesql when you do this).
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply