asked on

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!

lludden

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

wiggy353

ASKER

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?

lludden

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.

wiggy353

ASKER

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'.

lludden

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

wiggy353

ASKER

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'.

lludden

What are the values for @control AND @CMOPIDs

wiggy353

ASKER

@control = A00
@CMOPIDs = 762

lludden

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.

wiggy353

ASKER

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.

ASKER CERTIFIED SOLUTION

lludden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wiggy353

ASKER

OMG it was 1000. What a bonehead move! Thanks man for all the information.