Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using Parameters in SQL query

Posted on 2012-04-04
12
Medium Priority
?
320 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
ID: 37807960
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
ID: 37807982
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
ID: 37808094
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:wiggy353
ID: 37808108
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
ID: 37808170
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
ID: 37808247
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
 
LVL 18

Expert Comment

by:lludden
ID: 37808361
What are the values for @control AND @CMOPIDs
0
 
LVL 1

Author Comment

by:wiggy353
ID: 37808423
@control = A00
@CMOPIDs = 762
0
 
LVL 18

Expert Comment

by:lludden
ID: 37808712
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
ID: 37812391
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 2000 total points
ID: 37812728
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
ID: 37812745
OMG it was 1000. What a bonehead move! Thanks man for all the information.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…

916 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