Passing parameters in a stored proce

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

Passing parameters in a stored proce

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-19 : 10:50:11

I've created a stored procedure and I need to pass it a list of order IDs.

The OrderSourceOrderID field is NVARCHAR(100) in the DB. <-Not sure if this piece of info makes a difference.

It all works, except for the @orderList variable. I'm listing OrderSourceOrderIDs that I do not want to appear, but they appear in the final list anyway. How would I pass this information the right way?

EXEC uspGetBuyCancelledOrders @orderList ='''12343'',''1122'',''123123132''', @companyID='123'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE uspGetBuyCancelledOrders
	@orderList NVARCHAR(MAX),
	@CompanyID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT OrderSourceOrderID
	FROM [tmpDB].[dbo].[Order] 
	WHERE  [OrderSourceOrderID] NOT IN (@orderList)
		AND CompanyID=@CompanyID
		AND StatusCode NOT IN ('-1','999')
		AND ShippingStatus!='3'
		AND OrderSource='7'
		AND PaymentStatus='30'
END
GO

-Sergio
I use Microsoft SQL 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 11:01:51

A quick way to do it is shown below. Ideally, you should split the comma-separated string into individual tokens into a virual table, and join on those. Use a string splitter function such as DelimitedSplit8K ( http://www.sqlservercentral.com/articles/Tally+Table/72993/ ) if you want to do that.

EXEC uspGetBuyCancelledOrders @orderList ='12343,1122,123123132', @companyID='123'



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE uspGetBuyCancelledOrders
	@orderList NVARCHAR(MAX),
	@CompanyID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT OrderSourceOrderID
	FROM [tmpDB].[dbo].[Order] 
	WHERE  ','+@orderList+',' NOT LIKE '%,'+CAST([OrderSourceOrderID] AS varchar(32))+',%'
		AND CompanyID=@CompanyID
		AND StatusCode NOT IN ('-1','999')
		AND ShippingStatus!='3'
		AND OrderSource='7'
		AND PaymentStatus='30'
END
GO

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 11:06:13

where charindex (','+OrderSourceOrderID+',', ','+@orderList+',') = 0
and ...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 11:24:44

I's suggest creating a Table Type and pass a Table-Valued Parameter:

CREATE TYPE dbo.IntTableType AS TABLE 
(
	ID INT
);


DECLARE @OrderList dbo.IntTableType;

INSERT @OrderList (ID) VALUES
	(12343),
	(1122),
	(123123132);

EXEC uspGetBuyCancelledOrders @orderList = @OrderList, @companyID=123;


----

CREATE PROCEDURE uspGetBuyCancelledOrders
	@CompanyID INT,
	@OrderList dbo.IntTableType READONLY
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT 
		OrderSourceOrderID
	FROM 
		[tmpDB].[dbo].[Order] 
	LEFT OUTER JOIN
		@OrderList AS O
		ON [Order].OrderSourceID = O.ID
		AND CompanyID = @CompanyID
		AND StatusCode NOT IN ('-1','999')
		AND ShippingStatus!='3'
		AND OrderSource='7'
		AND PaymentStatus='30'
	WHERE  
		O.ID IS NULL

END
GO

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 11:55:28

Is not better move conditions from join-condition to WHERE clause?
like this:

LEFT OUTER JOIN
@OrderList AS O
ON [Order].OrderSourceID = O.ID
WHERE
O.ID IS NULL
AND CompanyID = @CompanyID
AND StatusCode NOT IN ('-1','999')
AND ShippingStatus!='3'
AND OrderSource='7'
AND PaymentStatus='30'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 12:02:15

quote:
Originally posted by sigmas

Is not better move conditions from join-condition to WHERE clause?
like this:

LEFT OUTER JOIN
@OrderList AS O
ON [Order].OrderSourceID = O.ID
WHERE
O.ID IS NULL
AND CompanyID = @CompanyID
AND StatusCode NOT IN ('-1','999')
AND ShippingStatus!='3'
AND OrderSource='7'
AND PaymentStatus='30'

Theoretically, you would want to put the predicates as close to the place then can be filtered/applied. However, in reality the optimizer is pretty darn good, so it, generally, won’t matter.

However, as with most things SQL, it depends. This may not apply to newer versions of SQL due to optimizer enhancements but, in the past, if you are joined many large tables it was possible to help eliminate rows by putting the predicates closer to the source in the query and not rely on the WHERE clause to filter them.

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 14:19:18

Please assume this example. we need select rows with color=red and product not in product table.

So your query is something like this:

SELECT t.*
FROM table_name t
LEFT OUTER JOIN Products P
ON t.prdouct_id = p.product_id
AND t.color = 'red'
WHERE p.product_id IS NULL;

But above query does not return correct result set.
Try this

declare @table_name table
(product_id int not null, color varchar(25) not null,
unique(product_id,color))

declare @product table
(product_id int not null primary key)

insert @product values (1)
insert @table_name values(2,'green'), (1, 'blue'), (3, 'red')
--Main Query
SELECT *
FROM @table_name
WHERE product_id NOT IN (SELECT product_id FROM @product)
AND color = 'red';

--Incorrect Query
SELECT t.*
FROM @table_name t
LEFT OUTER JOIN @Product P
ON t.product_id = p.product_id
AND t.color = 'red'
WHERE p.product_id IS NULL;

--Correct Query
SELECT t.*
FROM @table_name t
LEFT OUTER JOIN @Product P
ON t.product_id = p.product_id
WHERE t.color = 'red'
AND p.product_id IS NULL;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 16:32:47

It depends on your the logic you want to apply. The "Incorrect" query is showing that there are no products that have the color red and a matching product in the product table. Where as your "Correct" query is showing that the product with the color red that does not have a matching product in the product table. The logic is different for the two queries, thus different results.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 16:44:09

If you thought I was implying that applying the predicate(s) on the join or in the where clause was the same, that was not my intent. I was merely saying that they CAN be the same and that applying the predicates sooner rather than later can be desirable.

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-19 : 17:49:29

So is your query logically equivalent to the OP query?

EDIT: I was wrong. and I should not interfere to other posts.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-09-19 : 22:00:37

hi sigmas...maybe you can read about this.
http://weblogs.sqlteam.com/jeffs/archive/2007/10/11/mixing-inner-outer-joins-sql.aspx

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-20 : 09:34:41

quote:
Originally posted by James K

A quick way to do it is shown below. Ideally, you should split the comma-separated string into individual tokens into a virual table, and join on those. Use a string splitter function such as DelimitedSplit8K ( http://www.sqlservercentral.com/articles/Tally+Table/72993/ ) if you want to do that.
EXEC uspGetBuyCancelledOrders @orderList ='12343,1122,123123132', @companyID='123'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE uspGetBuyCancelledOrders
@orderList NVARCHAR(MAX),
@CompanyID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT OrderSourceOrderID
FROM [tmpDB].[dbo].[Order]
WHERE ','+@orderList+',' NOT LIKE '%,'+CAST([OrderSourceOrderID] AS varchar(32))+',%'
AND CompanyID=@CompanyID
AND StatusCode NOT IN ('-1','999')
AND ShippingStatus!='3'
AND OrderSource='7'
AND PaymentStatus='30'
END
GO

Thanks everyone! I'm going to read up on the optimization and other things later, but for this one task, this one works best for me.

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources