Solved

Using Parameters in SQL query

Posted on 2012-04-04
12
312 Views
Last Modified: 2012-04-05
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!
0
Comment
Question by:wiggy353
  • 6
  • 6
12 Comments
 
LVL 18

Expert Comment

by:lludden
Comment Utility
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
 
LVL 1

Author Comment

by:wiggy353
Comment Utility
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
 
LVL 18

Expert Comment

by:lludden
Comment Utility
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
 
LVL 1

Author Comment

by:wiggy353
Comment Utility
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
 
LVL 18

Expert Comment

by:lludden
Comment Utility
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
 
LVL 1

Author Comment

by:wiggy353
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 18

Expert Comment

by:lludden
Comment Utility
What are the values for @control AND @CMOPIDs
0
 
LVL 1

Author Comment

by:wiggy353
Comment Utility
@control = A00
@CMOPIDs = 762
0
 
LVL 18

Expert Comment

by:lludden
Comment Utility
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
 
LVL 1

Author Comment

by:wiggy353
Comment Utility
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
 
LVL 18

Accepted Solution

by:
lludden earned 500 total points
Comment Utility
You are probably not declaring the @SQL as varchar(MAX).  You probably put (500) or something in it and filled it.
0
 
LVL 1

Author Closing Comment

by:wiggy353
Comment Utility
OMG it was 1000. What a bonehead move! Thanks man for all the information.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now