To Dynamic SQL or not? - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

To Dynamic SQL or not?

Author

Topic

Tapalotapus
Starting Member

22 Posts

Posted - 2006-10-26 : 13:44:05

I need to do a search of a table where the column name is passed in.
I have a procedure that gets passed a column name and a search value. In the application the column name is a drop down with every Column in the table tblAnnouncement. There are 15 procedures like this each for a different tabel with up to 25 columns. Is there a way to do this without Dynamic SQl and should I? here is how one of them now is.

-----------------------------------------------------
CREATE TABLE [dbo].[tblAnnouncement] (
[AnnID] [int] IDENTITY (1, 1) NOT NULL ,
[AnnDate] [datetime] NOT NULL ,
[Announcement] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EffDate] [datetime] NOT NULL ,
[ExpDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
-----------------------------------------------------

CREATE PROCEDURE dbo.spAnnouncement_GetDataByFilters
@Column varchar(100),
@Value varchar(100)

AS

DECLARE @SQL varchar(1000)
SET @SQL = 'SELECT *
FROM tblAnnouncement
WHERE ' + @Column + ' LIKE ''%' + @Value + '%''
ORDER BY ' + @Column

EXEC (@SQL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-26 : 14:00:04

There is no way to do this inside a stored procedure without dynamic SQL. And no you shouldn't do this. You might as well write inline sql in your application.

Tara Kizer

Tapalotapus
Starting Member

22 Posts

Posted - 2006-10-26 : 15:34:47

So it would be better for the application to build the query and do it directly from the application? I thought you would want al your calls from an application to go through a proc? Any other alternatives?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-26 : 15:39:49

Yes I would want all of data access through stored procedures due to the security and performance benefits of them. Since you are using dynamic SQL, you aren't getting the security or the performance benefit, so there isn't a need for a stored procedure.

Where I work, you'd be prevented from this type of code in a stored procedure. We care about security and performance, so it wouldn't be allowed.

You need to look into ways around dynamic SQL, such as:
http://www.sqlteam.com/item.asp?ItemID=2077
http://www.sqlteam.com/item.asp?ItemID=2209

You also should be aware of the huge performance issue you have with your LIKE clause.

Very bad:
LIKE '%' + @Value + '%'

Much better:
LIKE @Value + '%'

Tara Kizer

Tapalotapus
Starting Member

22 Posts

Posted - 2006-10-26 : 17:28:25

What do you think of this solution?

SELECT AnnID,
AnnDate,
Announcement,
EffDate,
ExpDate
FROM dbo.tblAnnouncement
WHERE AnnID = CASE WHEN @Field = 'AnnID' THEN @Value ELSE NULL END
OR AnnDate = CASE WHEN @Field = 'AnnDate' THEN @Value ELSE NULL END
OR Announcement LIKE CASE WHEN @Field = 'Announcement' THEN @Value + '%' ELSE NULL END
OR EffDate = CASE WHEN @Field = 'EffDate' THEN @Value ELSE NULL END
OR ExpDate = CASE WHEN @Field = 'ExpDate' THEN @Value ELSE NULL END
ORDER BY CASE WHEN @Field = 'AnnID' THEN AnnID
WHEN @Field = 'AnnDate' THEN AnnDate
WHEN @Field = 'Announcement' THEN Announcement
WHEN @Field = 'EffDate' THEN EffDate
WHEN @Field = 'ExpDate' THEN ExpDate
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-26 : 17:33:14

That looks fabulous! But does it produce the results that you want? If it does, then that is leaps and bounds better than the dynamic SQL approach. You do get the benefit of security and performance with this non-dynamic approach.

Tara Kizer

Tapalotapus
Starting Member

22 Posts

Posted - 2006-10-26 : 19:35:54

Awesome, yes the results where as expected I was just woried about when I convert the one with 25 columns but enev though it is long it is quick and easily readable.

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources