String Parameter in Where clause

Last Post 10 Apr 2008 11:18 AM by SwePeso. 12 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Mikara
New Member
New Member

--
02 Apr 2008 12:19 PM
I have a parameter in my stored procedure that is passed from Access.

The parameter N'Name value would sometimes come back as lets say 'ABC001','ABC002'

in my Where clause i have this:

WHERE tblCustomers.CustomerCode IN (N'Name)

if i run this i dont get anything back, however if i run this which is the same:

WHERE tblCustomers.CustomerCode IN ('ABC001','ABC002')

I get results back!

can anyone help me out of what am i doing wrong? or any ideas of what i can do to improve?

the Parameter is Varchar(255) since sometimes i may have 5 of the CodeNames as above!

SQLUSA
New Member
New Member

--
07 Apr 2008 06:59 AM
You should convert the CSV list into a @tablevariable (or #temptable).

You can do a JOIN that way without the need for WHERE... in .....

This is a table-valued parsing function link: http://www.sqlusa.com/bestpractices2005/listparsing/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices...stparsing/

Let us know how it works out for you.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
Business Intelligence Class NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/
Mikara
New Member
New Member

--
08 Apr 2008 08:11 AM
Hi, thanks for all the help, i got it to work with the following:

WHERE CHARINDEX (tblCustomers.CustomerCode, @CustomerCodes) > 0

and it works perfectly, but im encountering another issue with this stored procedure. down the line in the sp i have 2 cursors where it find all the info that i need and then it deletes the records according to the stuff i got from the two cursors and then at the end i want to see the Temp table, the table comes up as a original one, like no records were deleted or anything. However if i walk through it with those two ft#'s and MktForms i get back the results from the temp table that i want which is 12 out of 30 original records.

The code executes but i think there is soemthing in the cursors that its not going right. I'm not that experiance with sql so idk if anyone can spot any errors in this code below:


DECLARE @FTNumber VARCHAR(15)
DECLARE @MFSN VARCHAR(30)

DECLARE curCustInfo

CURSOR LOCAL FOR

SELECT DISTINCT FTNumber FROM #PartResults

OPEN curCustInfo

FETCH NEXT FROM curCustInfo
INTO @FTNumber

WHILE(@@FETCH_STATUS = 0)
BEGIN

DECLARE CurMktForm
CURSOR LOCAL FOR

SELECT DISTINCT MktFormSpecificName FROM IngredientMaterial WHERE FTNumber = @FTNumber

OPEN CurMktForm
FETCH NEXT FROM curMktForm
INTO @MFSN

WHILE(@@FETCH_STATUS = 0)
BEGIN

DELETE FROM #PartResults WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "' AND GPPartNum NOT IN (SELECT GPPartNum FROM IngredientMaterial WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "')

FETCH NEXT FROM curMktForm
INTO @MFSN

END

CLOSE CurMktForm
DEALLOCATE CurMktForm

FETCH NEXT FROM curCustInfo
INTO @FTNumber

END
CLOSE curCustInfo

SELECT * FROM #PartResults


SwePeso
New Member
New Member

--
08 Apr 2008 08:56 AM
Seems you can rewrite whole SP with this

DELETE pr
FROM #PartResults AS pr
INNER JOIN IngredientMaterial AS im ON im.FTNumber = pr.FTNumber
AND im.MktFormSpecificName = pr.MktFormSpecificName
WHERE im.GPPartNum <> pr.GPPartNum
SwePeso
New Member
New Member

--
08 Apr 2008 08:58 AM
If you want to keep your original code, please notice that you do NOT build sql statements to execute here.

replace

DELETE FROM #PartResults WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "' AND GPPartNum NOT IN (SELECT GPPartNum FROM IngredientMaterial WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "')

with

DELETE FROM #PartResults WHERE FTNumber = @FTNumber AND MktFormSpecificName = @MFSN AND GPPartNum NOT IN (SELECT GPPartNum FROM IngredientMaterial WHERE FTNumber = @FTNumber AND MktFormSpecificName = @MFSN)
Mikara
New Member
New Member

--
08 Apr 2008 09:10 AM
I did the deallocate for the curCustInfo. I have also printed out the row count and it would be 0 after the delete statement.

The entire sp is below:

ALTER PROCEDURE [dbo].[CustomerInformation] (
@CustomerCodes VARCHAR(255),
@SiteID VARCHAR(3)
)
AS


CREATE TABLE #PartResults (
CustomerCode VARCHAR(6),
CustomerName VARCHAR(100),
FTNumber VARCHAR(15),
MktFormSpecificName VARCHAR(60),
SupplierCode VARCHAR(10),
ManufacturerName VARCHAR(100),
CountryOfOrigin VARCHAR(40),
ProductNumber VARCHAR(50),
GPPartNum varchar(20),
SiteID varchar(3),
Status INT
)

INSERT INTO #PartResults (
CustomerCode,
CustomerName,
FTNumber,
MktFormSpecificName,
SupplierCode,
ManufacturerName,
CountryOfOrigin,
ProductNumber,
GPPartNum,
SiteID,
Status
)

SELECT tblCustomers.CustomerCode,
tblCustomers.CustomerName,
tblFormJoiner.FTNumber,
tblIngredients.MktFormSpecificName,
tblManufacturers.SupplierCode,
tblManufacturers.SupplierName,
tblOEMMaterial.CountryOrigin,
tblOEMMaterial.ProductNumber,
tblOEMMaterialAttributes_Parent.GPPartNum,
tblOEMMaterialAttributes_Parent.SiteID,
tblOEMMaterialAttributes_Parent.Status

FROM tblOEMMaterialAttributes_Parent INNER JOIN ((tblManufacturers INNER JOIN tblOEMMaterial ON tblManufacturers.SupplierCode = tblOEMMaterial.SupplierCode) INNER JOIN (tblIngredients INNER JOIN (tblCustomers INNER JOIN tblFormJoiner ON tblCustomers.CustomerCode = tblFormJoiner.CustomerCode) ON tblIngredients.FTNumber = tblFormJoiner.FTNumber) ON tblOEMMaterial.MktFormSpecificName = tblIngredients.MktFormSpecificName) ON tblOEMMaterialAttributes_Parent.GPPartNum = tblOEMMaterial.GPPartNum

WHERE CHARINDEX(tblCustomers.CustomerCode, @CustomerCodes)>0 AND tblOEMMaterialAttributes_Parent.SiteID LIKE @SiteID AND tblOEMMaterialAttributes_Parent.Status > 0
ORDER BY tblFormJoiner.FTNumber;

--SELECT * FROM #PartResults

DECLARE @FTNumber VARCHAR(15)
DECLARE @MFSN VARCHAR(30)
DECLARE @ErrorVar INT

DECLARE curCustInfo


CURSOR LOCAL FOR

SELECT DISTINCT FTNumber FROM #PartResults

OPEN curCustInfo

FETCH NEXT FROM curCustInfo
INTO @FTNumber

WHILE(@@FETCH_STATUS = 0)
BEGIN

DECLARE CurMktForm
CURSOR LOCAL FOR

SELECT DISTINCT MktFormSpecificName FROM IngredientMaterial WHERE FTNumber = @FTNumber

OPEN CurMktForm
FETCH NEXT FROM curMktForm
INTO @MFSN

WHILE(@@FETCH_STATUS = 0)
BEGIN

DELETE FROM #PartResults WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "' AND GPPartNum NOT IN (SELECT GPPartNum FROM IngredientMaterial WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "')
print @@ROWCOUNT


FETCH NEXT FROM curMktForm
INTO @MFSN

END

CLOSE CurMktForm
DEALLOCATE CurMktForm

FETCH NEXT FROM curCustInfo
INTO @FTNumber

END
CLOSE curCustInfo
DEALLOCATE curCustInfo

SELECT * FROM #PartResults


what it does is get the customer code and siteid from access form, then it passes it to the sp to execute it with those parameters. the results for one customer code is 30, and its stored in temporary table.

Then what happens with first cursor it selects only the distinct ftnumbers that are there, this results is 2 of them.

Then in the next cursor it finds the MktformSpecificName with each of those 2 Ftnumbers, then it deletes any records in temporary table that h
SwePeso
New Member
New Member

--
08 Apr 2008 09:13 AM
quote:

Originally posted by: Mikara
Any help would be appreciated.

See posts made

04/08/2008 07:56 PM
04/08/2008 07:58 PM

Mikara
New Member
New Member

--
08 Apr 2008 09:15 AM
quote:

Originally posted by: Pesomannen
If you want to keep your original code, please notice that you do NOT build sql statements to execute here.

replace

DELETE FROM #PartResults WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "' AND GPPartNum NOT IN (SELECT GPPartNum FROM IngredientMaterial WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "')

with

DELETE FROM #PartResults WHERE FTNumber = @FTNumber AND MktFormSpecificName = @MFSN AND GPPartNum NOT IN (SELECT GPPartNum FROM IngredientMaterial WHERE FTNumber = @FTNumber AND MktFormSpecificName = @MFSN)


I have changed this on my code im still receiving all 30 records.

quote:

Originally posted by: Pesomannen
Seems you can rewrite whole SP with this

DELETE pr
FROM #PartResults AS pr
INNER JOIN IngredientMaterial AS im ON im.FTNumber = pr.FTNumber
AND im.MktFormSpecificName = pr.MktFormSpecificName
WHERE im.GPPartNum <> pr.GPPartNum



rewrite the Delete statement with the code above?

SwePeso
New Member
New Member

--
08 Apr 2008 09:21 AM
Replace all this


DECLARE @FTNumber VARCHAR(15)
DECLARE @MFSN VARCHAR(30)
DECLARE @ErrorVar INT

DECLARE curCustInfo


CURSOR LOCAL FOR

SELECT DISTINCT FTNumber FROM #PartResults

OPEN curCustInfo

FETCH NEXT FROM curCustInfo
INTO @FTNumber

WHILE(@@FETCH_STATUS = 0)
BEGIN

DECLARE CurMktForm
CURSOR LOCAL FOR

SELECT DISTINCT MktFormSpecificName FROM IngredientMaterial WHERE FTNumber = @FTNumber

OPEN CurMktForm
FETCH NEXT FROM curMktForm
INTO @MFSN

WHILE(@@FETCH_STATUS = 0)
BEGIN

DELETE FROM #PartResults WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "' AND GPPartNum NOT IN (SELECT GPPartNum FROM IngredientMaterial WHERE FTNumber = '" + @FTNumber + "' AND MktFormSpecificName = '" + @MFSN + "')
print @@ROWCOUNT


FETCH NEXT FROM curMktForm
INTO @MFSN

END

CLOSE CurMktForm
DEALLOCATE CurMktForm

FETCH NEXT FROM curCustInfo
INTO @FTNumber

END
CLOSE curCustInfo
DEALLOCATE curCustInfo


with this


DELETE pr
FROM #PartResults AS pr
INNER JOIN IngredientMaterial AS im ON im.FTNumber = pr.FTNumber
AND im.MktFormSpecificName = pr.MktFormSpecificName
WHERE im.GPPartNum <> pr.GPPartNum
Mikara
New Member
New Member

--
08 Apr 2008 09:29 AM
hmm that worked but it came back with 26 records now....time to walk through it and see what is erased and what not.

thanks alot for all the help.
SwePeso
New Member
New Member

--
08 Apr 2008 09:29 AM
Why DELETE at all? Why not exclude those records not wanted in the first place?


ALTER PROCEDURE dbo.CustomerInformation
(
@CustomerCodes VARCHAR(255),
@SiteID VARCHAR(3)
)
AS

SET NOCOUNT ON

CREATE TABLE #PartResults
(
CustomerCode VARCHAR(6),
CustomerName VARCHAR(100),
FTNumber VARCHAR(15),
MktFormSpecificName VARCHAR(60),
SupplierCode VARCHAR(10),
ManufacturerName VARCHAR(100),
CountryOfOrigin VARCHAR(40),
ProductNumber VARCHAR(50),
GPPartNum varchar(20),
SiteID varchar(3),
Status INT
)

INSERT #PartResults
(
CustomerCode,
CustomerName,
FTNumber,
MktFormSpecificName,
SupplierCode,
ManufacturerName,
CountryOfOrigin,
ProductNumber,
GPPartNum,
SiteID,
Status
)
SELECT tblCustomers.CustomerCode,
tblCustomers.CustomerName,
tblFormJoiner.FTNumber,
tblIngredients.MktFormSpecificName,
tblManufacturers.SupplierCode,
tblManufacturers.SupplierName,
tblOEMMaterial.CountryOrigin,
tblOEMMaterial.ProductNumber,
tblOEMMaterialAttributes_Parent.GPPartNum,
tblOEMMaterialAttributes_Parent.SiteID,
tblOEMMaterialAttributes_Parent.Status
FROM tblOEMMaterialAttributes_Parent
INNER JOIN tblOEMMaterial ON tblOEMMaterial.GPPartNum = tblOEMMaterialAttributes_Parent.GPPartNum
INNER JOIN tblManufacturers ON tblManufacturers.SupplierCode = tblOEMMaterial.SupplierCode
INNER JOIN tblIngredients ON tblIngredients.MktFormSpecificName = tblOEMMaterial.MktFormSpecificName
INNER JOIN tblFormJoiner ON tblFormJoiner.FTNumber = tblIngredients.FTNumber
INNER JOIN tblCustomers ON tblCustomers.CustomerCode = tblFormJoiner.CustomerCode
INNER JOIN IngredientMaterial AS im ON im.FTNumber = tblFormJoiner.FTNumber
AND im.MktFormSpecificName = tblIngredients.MktFormSpecificName
AND im.GPPartNum <> tblOEMMaterialAttributes_Parent.GPPartNum
WHERE CHARINDEX(tblCustomers.CustomerCode, @CustomerCodes) > 0
AND tblOEMMaterialAttributes_Parent.SiteID LIKE @SiteID
AND tblOEMMaterialAttributes_Parent.Status > 0
ORDER BY tblFormJoiner.FTNumber

SELECT *
FROM #PartResults
Mikara
New Member
New Member

--
10 Apr 2008 11:10 AM
thanks alot Pesomannen

the statement below worked perfecty i just had to edit it a bit.


DELETE pr
FROM #PartResults AS pr
INNER JOIN IngredientMaterial AS im ON im.FTNumber = pr.FTNumber
AND im.MktFormSpecificName = pr.MktFormSpecificName
WHERE im.GPPartNum <> pr.GPPartNum

SwePeso
New Member
New Member

--
10 Apr 2008 11:18 AM
quote:

Originally posted by: Mikara
thanks alot Pesomannen

the statement below worked perfecty i just had to edit it a bit.


DELETE pr
FROM #PartResults AS pr
INNER JOIN IngredientMaterial AS im ON im.FTNumber = pr.FTNumber
AND im.MktFormSpecificName = pr.MktFormSpecificName
WHERE im.GPPartNum <> pr.GPPartNum




What did you change from my post 04/08/2008 08:21 PM ?


Acceptable Use Policy
---