Solved

Using Parameters in SQL query

Posted on 2012-04-04
12
315 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Server time between records 14 47
average of calculation (TSQL) 4 26
Disable TLS1.0 on Win 2012 server 7 30
partitioning database after decade growth 8 29
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 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