Is it possible to strip quotation marks from SP Parameter?

[uncleroydee]

I'm trying to write a stored procedure that will select records from one table and then update another based on the selection. The SP contains a parameter that consists of a long list of part numbers that will be used in an "IN" portion of a "WHERE" clause. The only way I can pass the list from an Active Server Page is to enclose the entire list in quotes (single or double) eg ("123,456,789,012,...&quot.

On execution I do not receive any error message but no records are returned because SQL is looking for pns IN "123,456,789,012" instead of 123,456,789,012.

Evidently SQL Server 7.0 does not accept the quototation marks. I inserted a print command in the SP so I could view the part numbers, "pn" and "pn2" appear identical (without quotation marks).

Here's a brief example of the type of SP I'm trying to write.

CREATE PROCEDURE SP_SrgDB_MoveSomePN @pn VARCHAR (7710) OUTPUT

AS
DECLARE @pn2 varchar (7710)
SET @pn2 = REPLACE(@pn, ' " ', ' ')
BEGIN TRANSACTION
PRINT @pn
PRINT @pn2
SELECT *
FROM TBL_PN_CNTRCT
WHERE pn in (@pn2)
END TRANSACTION

If I run a simple select statement using the same PNs, sans quotes, it executes correctly and returns records, if I enclose the list of pns in quotes the query runs but returns no records.

Is it possible to remove or have SQL Server disregard the quotation marks within the parameter?

 

[tlbroadbent]

You need to dynamically create and execute the T-SQL statement. You cannot use the variable in the IN clause but you can evaluate the value and use the value. You don't need the transaction. The double quote (&quot should be acceptable but I recommend single quote (') around the parameter that yo pass to the SP.

CREATE PROCEDURE SP_SrgDB_MoveSomePN @pn VARCHAR (7710) OUTPUT

AS

DECLARE @sql ntext (8000)

SET @sql='SELECT *
FROM TBL_PN_CNTRCT
WHERE pn in (' + @pn2 + ')'

Exec sp_executesql @sql
Go

This should return the values you seek. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[uncleroydee]

Terry,

Thanks for your prompt reply.

Here's the SP that I am trying to execute. Please note that it is a snippet from a much larger SP that will be inserting as well as deleting records, hence the "Begin Transaction" and "End Transaction". In the interest of brevity and readability I previously substituted Part number (PN) for the actual term in my database which is "NSN".

CREATE PROCEDURE SP_SrgDB_MoveSomeNSN @nsn VARCHAR (7710) OUTPUT
AS
DECLARE @NSN2 VARCHAR (8000)
SET @NSN2 ='SELECT NSN FROM TBL_NSN_SOL WHERE NSN IN ('+@NSN+')'
--BEGIN TRANSACTION
PRINT @NSN
PRINT @NSN2

SELECT *
FROM TBL_NSN_SOL
WHERE nsn in (@nsn2)

Here is the executable:
exec SP_SrgDB_MoveSomeNSN @nsn = '5306013017793,5306013017794'

The SP still returns no records. "Print @nsn2" returns the following: SELECT NSN FROM TBL_NSN_SOL WHERE NSN IN (5306013017793,5306013017794). When I reduce the SP to the following simple select statement utilizing the output of "Print @NSN2"... ( SELECT * FROM TBL_NSN_SOL
WHERE (nsn in (SELECT NSN FROM TBL_NSN_SOL WHERE NSN IN (5306013017793,5306013017794))) )... records are returned.

I also tried using the NTEXT datatype as you suggested but received the error message "The text, ntext, and image data types are invalid for local variables."

Any ideas on what may be messing me up?

 

[tlbroadbent]

You print @NSN2 and it looks correct. That is what you need to execute.

CREATE PROCEDURE SP_SrgDB_MoveSomeNSN @nsn VARCHAR (7710) OUTPUT
AS
DECLARE @NSN2 NVARCHAR (8000)
SET @NSN2 ='SELECT NSN FROM TBL_NSN_SOL WHERE NSN IN ('+@NSN+')'
--BEGIN TRANSACTION

EXEC(@nsn2) /* This will execute the SQL statement */

The ntext variable data type was an error on my part. Sorry about that. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[uncleroydee]

Terry,

Thanks for the help. Your suggestion worked, @NSN2 returned a recordset. But, what I really need to know is how to use the result set of @NSN2 later in the SP.

When I try to add the statement:
SELECT *
FROM TBL_NSN_SOL
WHERE nsn in (exec (@nsn2))
to my SP it fails with an incorrect syntax error.

I even tried to create a temporary table with the results of @NSN2 to no avail.

I do appreciate your assistance.

Roy

 

[rajeevnandanmishra]

Hi Roy,

If you are not doing any calculation on the resultset of @NSN2 other than the Next SELECT than you can add following snippet into your existing code
--------------------
DECLARE @nsn3 VARHCAR(8000)
SET @nsn3='SELECT * FROM TBL_NSN_SOL '+
'WHERE nsn in ('+@nsn2+')'
--------------------

But if you want to do some other calculations also before executing next sql then following code snippet will help you.
--------------------------
CREATE TABLE #temporary (nsn INT)
-- make the datatype same as of actual nsn column
INSERT #temporary
EXEC(@nsn2)
SELECT * FROM TBL_NSN_SOL WHERE nsn in
(SELECT nsn FROM #temporary)
DROP TABLE #temporary
--------------------------

Let us know if you face any other problem.

 

[uncleroydee]

It always looks so simple in the end!!

Thanks, Rajeevnandanmishra. I had tried using a temporary table, but I used a "Select Into" statement that obviously didn't work. This helps me over a major obstacle and I do appreciate your, and Terry's, help.