Using Parameters in SQL query

I am working on a stored procedure that someone else built and I need to add to it. It the section the was previously written, part of the code is this:

Select @SQL = @SQL + '' + @dropdownlist + ' = ''' + @control + ''' and CMOPID in (' + @CMOPIDs + ')
Order by CMOPID, ProductCode'

Open in new window


Obviously before this statement the SQL variable was declared and some text was put in it. Then this combines that with these parameters and then makes a new statement that gets executed.

The section that I am adding looks like this:

			SELECT
					DISTINCT
				  fm.[Cmopid]
				  ,fm.[OnHandStock]
				  ,fm.[ProductCode]
				  ,fm.[DispensingPackSize]
				  ,vp.HostDescription
				  ,fm.[ProductEnabled]
			  FROM [vhacmncdb].[CDB].[dbo].[FormularyMaster] fm with (nolock)
				join [vhacmncdb].[CDB].[info].[va_productlist] vp with (NOLOCK)
				on
				fm.productcode = vp.productcode
			  where
				fm.productcode like '%'+@control+'%' 
				and
				CMOPID in (@CMOPIDs)

Open in new window


The problem is that the previous code works fine. The new code works fine but only if one parameter is passed to the @CMOPIDs. With the original code, I can send multiples separated by a comma and it works as an in clause would. But if I try to pass multiples to the new code it returns nothing. It doesn't error or anything, but it returns no results which is not correct.

Please help!
LVL 1
wiggy353Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
lluddenConnect With a Mentor Commented:
You are probably not declaring the @SQL as varchar(MAX).  You probably put (500) or something in it and filled it.
0
 
lluddenCommented:
The statment CMOPID in (@CMOPIDs) will not work.  You either need to use dymanic SQL to expand that into the component parts, or use an inline function to split it into a table.

You could do something like this;

DECLARE @CMOPIDs VARCHAR(100) = '1,2,3,4'
DECLARE @SQL varchar(max) = '
			SELECT
					DISTINCT
				  fm.[Cmopid]
				  ,fm.[OnHandStock]
				  ,fm.[ProductCode]
				  ,fm.[DispensingPackSize]
				  ,vp.HostDescription
				  ,fm.[ProductEnabled]
			  FROM [vhacmncdb].[CDB].[dbo].[FormularyMaster] fm with (nolock)
				join [vhacmncdb].[CDB].[info].[va_productlist] vp with (NOLOCK)
				on
				fm.productcode = vp.productcode
			  where
				fm.productcode LIKE ''%''+@control+''%'' 
				and
				CMOPID in (' + @CMOPIDs + ')'
EXECUTE (@SQL)

Open in new window

0
 
wiggy353Author Commented:
Thanks. I tried that at first but then had another problem. Later in the same query is this section:

			SELECT
				distinct 
				cmopid
				,'Outsource Vendor' as ndc
				,qtyavail
				,ProductCode
				,DispensingPackSize as packsize
				,vaprintname
				,'Unavailable' as Manufacturer
				,'Unavailable' as [Image]
			FROM
				@tmpFMResults tr

Open in new window


and it kept breaking because of the literals (is that the right term) that I am selecting; like 'Unavailable'. Is there a way to get around that?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
lluddenCommented:
There is nothing obviously wrong with that query.  It would help to see the full query, and exactly how it is breaking with the inputs.
0
 
wiggy353Author Commented:
Oh, I forgot to change it into the form for the dynamic SQL, so it would look like this:

		@SQL = '	SELECT
				distinct 
				cmopid
				,'+'Outsource Vendor'+' as ndc
				,qtyavail
				,ProductCode
				,DispensingPackSize as packsize
				,vaprintname
				,'+'Unavailable'+' as Manufacturer
				,'+'Unavailable'+' as [Image]
			FROM
				@tmpFMResults tr'

Open in new window


You can now see where it would have problems with the 'Outsource Vendor' and 'Unavailable'.
0
 
lluddenCommented:
When you are embedding string literals into dynamic SQL, you need to double up the quotes, or just add them as a constant.
		@SQL = '	SELECT
				distinct 
				cmopid
				,''Outsource Vendor'' as ndc
				,qtyavail
				,ProductCode
				,DispensingPackSize as packsize
				,vaprintname
				,''Unavailable'' as Manufacturer
				,''Unavailable'' as [Image]
			FROM
				@tmpFMResults tr'

Open in new window

0
 
wiggy353Author Commented:
OK, so that helps. Here is what I have now:

			set @SQL2 = '
			DECLARE @tmpFinalResults TABLE
			(
				   [cmopid]						varchar(50)
				  ,[ndc]						varchar(50)		
				  ,[qtyavail]					int
				  ,[ProductCode]				VARCHAR(50)
				  ,[DispensingPackSize]			real
				  ,[vaprintname]				varchar(50)
				  ,[manufacturer]				varchar(50)
				  ,[image]						varchar(50)
			)
			
			DECLARE @tmpFMResults TABLE
			(
				   [cmopid]						varchar(50)		
				  ,[qtyavail]					int
				  ,[ProductCode]				VARCHAR(50)
				  ,[DispensingPackSize]			VARCHAR(50)
				  ,[vaprintname]				varchar(100)
				  ,[ProductEnabled]				INT
			)
			
			INSERT INTO @tmpFMResults
			(
				CmopID
				,qtyavail
				,ProductCode
				,DispensingPackSize
				,vaprintname
				,ProductEnabled
			)
			SELECT
					DISTINCT
				  fm.[Cmopid]
				  ,fm.[OnHandStock]
				  ,fm.[ProductCode]
				  ,fm.[DispensingPackSize]
				  ,vp.HostDescription
				  ,fm.[ProductEnabled]
			  FROM [servername].[dbname].[dbo].[tablename] fm with (nolock)
				join [servername].[dbname].[info].[table2name] vp with (NOLOCK)
				on
				fm.productcode = vp.productcode
			  where
				fm.productcode like ''%'+@control+'%'' 
				and
				CMOPID in ('+@CMOPIDs+') 
			insert into @tmpFinalResults
			(
				cmopid
				,ndc
				,qtyavail
				,productcode
				,dispensingpacksize
				,vaprintname
				,manufacturer
				,[image]
			)

			SELECT 
				distinct cmopid
				, ndc
				, qtyavail
				, productcode
				, packsize
				, vaprintname
				, Manufacturer
				, NDC as "Image"
			FROM 
				CMOPProductLine.Formulary  
			Where 
				productcode like ''%'+@control+'%'' 
				and
				CMOPID in ('+@CMOPIDs+') 
			insert into @tmpFinalResults
			(
				cmopid
				,ndc
				,qtyavail
				,productcode
				,dispensingpacksize
				,vaprintname
				,manufacturer
				,[image]
			)	

			SELECT
				distinct 
				cmopid
				,''Outsource Vendor'' as ndc
				,qtyavail
				,ProductCode
				,DispensingPackSize as packsize
				,vaprintname
				,''Unavailable'' as Manufacturer
				,''Unavailable'' as [Image]
			FROM
				@tmpFMResults tr
			WHERE
				tr.ProductCode IN
				(
					SELECT
						ProductCode
					FROM
						@tmpFMResults 
					GROUP BY
						ProductCode
					HAVING
						(COUNT(ProductCode) = 1)
				
				)
				AND tr.ProductCode IN
				(
					SELECT
						ProductCode
					FROM
						[servername].[dbname].[dbo].[tablename] with (nolock)
					GROUP BY
						ProductCode
					HAVING
						COUNT(ProductCode) = 7
				)
				AND ProductEnabled = 1
			ORDER BY
				tr.ProductCode
						
			select 
				cmopid
				,ndc
				,qtyavail
				,productcode
				,dispensingpacksize as packsize
				,vaprintname
				,manufacturer
				,[image]
			from
				@tmpFinalResults'
	exec ( @SQL2)

Open in new window


and I am getting this error:

Msg 102, Level 15, State 1, Line 42
Incorrect syntax near 'jo'.
0
 
lluddenCommented:
What are the values for @control AND @CMOPIDs
0
 
wiggy353Author Commented:
@control = A00
@CMOPIDs = 762
0
 
lluddenCommented:
Instead of EXECUTE, try just PRINT and then take that query and past it back into SSMS and run it and see if it runs.  It looks ok to me on first glance, and the only place you have the letters 'JO' is in a JOIN, so I don't see where it is erroring.
0
 
wiggy353Author Commented:
I did that and ran it. The message in SSMS was:

                  DECLARE @tmpFinalResults TABLE
                  (
                           [cmopid]                                    varchar(50)
                          ,[ndc]                                    varchar(50)            
                          ,[qtyavail]                              int
                          ,[ProductCode]                        VARCHAR(50)
                          ,[DispensingPackSize]                  real
                          ,[vaprintname]                        varchar(50)
                          ,[manufacturer]                        varchar(50)
                          ,[image]                                    varchar(50)
                  )
                  
                  DECLARE @tmpFMResults TABLE
                  (
                           [cmopid]                                    varchar(50)            
                          ,[qtyavail]                              int
                          ,[ProductCode]                        VARCHAR(50)
                          ,[DispensingPackSize]                  VARCHAR(50)
                          ,[vaprintname]                        varchar(100)
                          ,[ProductEnabled]                        INT
                  )
                  
                  INSERT INTO @tmpFMResults
                  (
                        CmopID
                        ,qtyavail
                        ,ProductCode
                        ,DispensingPackSize
                        ,vaprintname
                        ,ProductEnabled
                  )
                  SELECT
                              DISTINCT
                          fm.[Cmopid]
                          ,fm.[OnHandStock]
                          ,fm.[ProductCode]
                          ,fm.[DispensingPackSize]
                          ,vp.HostDescription
                          ,fm.[ProductEnabled]
                    FROM [vhacmncdb].[CDB].[dbo].[FormularyMaster] fm with (nolock)
                        jo

I don't know why it is stopping at the join.
0
 
wiggy353Author Commented:
OMG it was 1000. What a bonehead move! Thanks man for all the information.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.