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.
Author |
Topic |
seidel1
Starting Member
2 Posts |
Posted - 2005-12-28 : 13:34:07
|
I have a PROC that I want to modify via a variable I will pass in from ASP.Net.Currently the PROC is as follows:ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(30)AS Set @Search = '%'+ @Search + '%' SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1, dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP, dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWOFROM dbo.EDCclient INNER JOIN dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_IDWHERE (dbo.EDCclient.CLIENT like @Search) OR (dbo.EDCproperty.PROPERTY like @Search)I now want to modify it so that the entire Where clause is created in ASp and passed in. So I think my new PROC should look as follows:ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(200)AS SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1, dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP, dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWOFROM dbo.EDCclient INNER JOIN dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID@Search)But I can't get SQL to accept the @Search in place of the Where... Any ideas? |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-28 : 13:45:30
|
What you are trying to do is called dynamic SQL. You will receive a performance hit for this type of SQL. It is not recommended. Check out this article on how to implement a dynamic WHERE clause without using dynamic SQL.http://www.sqlteam.com/item.asp?ItemID=2077Using dynamic SQL is also bad for security as the account needs direct table access rather than just EXEC on the stored procedure. So not only is it a performance issue, but it is also a security issue. Tara Kizeraka tduggan |
|
|
seidel1
Starting Member
2 Posts |
Posted - 2005-12-28 : 15:31:09
|
THANKS! Thats exactly why I joined this forum. I really appreciate the help |
|
|
newwaysys
Starting Member
9 Posts |
Posted - 2015-03-31 : 08:11:51
|
ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(30)ASSet @Search = '%'+ @Search + '%'SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1, dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP, dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWOFROM dbo.EDCclient INNER JOINdbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_IDWHERE (dbo.EDCclient.CLIENT like @Search) OR(dbo.EDCproperty.PROPERTY like @Search)unspammedUsing dynamic SQL is also bad for security as the account needs direct table access rather than just EXEC on the stored procedure. So not only is it a performance issue, but it is also a security issue. |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-01 : 05:16:17
|
quote: Originally posted by newwaysys Using dynamic SQL is also bad for security as the account needs direct table access rather than just EXEC on the stored procedure. So not only is it a performance issue, but it is also a security issue.
The dynamic query can be set to EXECUTE AS in a controlled manner, thus the logged on user doesn't not have to be granted Table SELECT permission |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-01 : 05:18:07
|
quote: Originally posted by newwaysys ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(30)ASSet @Search = '%'+ @Search + '%'
This is better done as :ALTER Procedure [dbo].[spu_FindProperties] @Search varChar(30)ASDECLARE @WorkingSearch varchar(32)Set @WorkingSearch = '%'+ @Search + '%' because there is an edge-condition where a value is provided to the SProc which is more than 28 characters long, and the addition of "%" at each end exceeds the declared size. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2015-04-01 : 05:58:50
|
10 year old thread is active now In any case for dynamic sql one has to read this article fully www.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-01 : 06:33:17
|
quote: Originally posted by madhivanan 10 year old thread is active now
Blast! Missed that ... although I did spot "Unspammed" and should have just "moved on" |
|
|
|
|
|
|
|