Multi Values Variable - 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 2005 Forums

Transact-SQL (2005)

Multi Values Variable

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-01-01 : 08:40:59

hi

It seems that using dynamic sql is generally not recomended:

CREATE PROC dbo.GetOrderList1
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderID IN (' + @OrderList + ')'

EXEC(@SQL)
END
GO

How do I rewrite this above sql statement that is easy to read and understand so that it will be easy for someone takes over this job? Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-01-01 : 11:41:38

That's a pretty easy query to understand. What you might want to do is look at some of the parsing functions you can find on this site and re-write the query so that it isn't dynamic. Short of that, just add comments into your query explaining what you are doing at each step.

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:14:05

[code]
CREATE PROC dbo.GetOrderList1
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE ',' + @OrderList + ',' LIKE '%,' + CAST(OrderID AS varchar(10)) + ',%'

END
GO
[/code]

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-01-01 : 22:31:41

Thanks so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:17:27

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-02 : 02:02:24

quote:
Originally posted by sg2255551

hi

It seems that using dynamic sql is generally not recomended:

CREATE PROC dbo.GetOrderList1
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderID IN (' + @OrderList + ')'

EXEC(@SQL)
END
GO

How do I rewrite this above sql statement that is easy to read and understand so that it will be easy for someone takes over this job? Thanks

For more methods refer
http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-02 : 07:50:52

Hi,

try this also

CREATE PROC dbo.GetOrderList1
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE PATINDEX('%,' + CAST(OrderID AS varchar(10))+',%', ',' + @OrderList + ',') > 0

END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 08:44:28

quote:
Originally posted by raky

Hi,

try this also

CREATE PROC dbo.GetOrderList1
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE PATINDEX('%,' + CAST(OrderID AS varchar(10))+',%', ',' + @OrderList + ',') > 0

END
GO

did you compare performance of PATINDEX with LIKE?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources