Stored Procedures Variables - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedures Variables

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.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
WHERE (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.SWO
FROM 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=2077

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.

Tara Kizer
aka 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)
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.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
WHERE (dbo.EDCclient.CLIENT like @Search) OR
(dbo.EDCproperty.PROPERTY like @Search)

unspammed

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.

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)
AS
Set @Search = '%'+ @Search + '%'

This is better done as :


ALTER Procedure [dbo].[spu_FindProperties] 
@Search varChar(30)
AS
DECLARE @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