Solved

Sql query

Posted on 2016-11-29
34
44 Views
Last Modified: 2016-11-29
Hello,

I have a stored procedure:
ALTER PROCEDURE  [dbo].[TEL_SelectCountInVoiceChecked]     
  
   @ColumnName1 nvarchar(255)= NULL,  
   @TableName varchar(100) = NULL,
   @Value varchar(100) = NULL
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  
	
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT COUNT (*)'
	SET @SQL = @SQL    + ' FROM  [dbo].[' + @tableName + ']'  				
			
       IF  @ColumnName1  <> ''
              SET @SQL = @SQL + '  WHERE  ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '' +'=' +  CAST(@Value  AS NVARCHAR(30))
	
	 
	  SET @SQL = @SQL  
	   		
	  
       EXECUTE (@SQL)
	
END

Open in new window


Need to add one more parameter to check on the columnname1  where it is 0 or null example :
WHERE ( DeleteRecord is null or DeleteRecord = 0)'  

How can I modify the above SP to fit the requirement.

Cheers
0
Comment
Question by:RIAS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 11
  • 6
  • +1
34 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905474
Hi Rias ,

Not very clear? explain more?
0
 

Author Comment

by:RIAS
ID: 41905479
My current where claues is :

WHERE  ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '' +'=' +  CAST(@Value  AS NVARCHAR(30))

Open in new window

Need to change it to

WHERE ( @ColumnName1 is @value or @ColumnName1 = 0)'
0
 

Author Comment

by:RIAS
ID: 41905480
need the syntax .

Thanks
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41905481
 SET @SQL = @SQL + '  WHERE  isnull(DeleteRecord, 0) = 0 and ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '  ' +'=' +  CAST(@Value  AS NVARCHAR(30))

Open in new window


Try that.
0
 

Author Comment

by:RIAS
ID: 41905483
Thanks Lee,
But deleterecord was an example.What I really need is



My current where claues is :

WHERE  ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '' +'=' +  CAST(@Value  AS NVARCHAR(30))

Select all
 
Open in new window
Need to change it to

WHERE ( @ColumnName1 is @value or @ColumnName1 = 0)'
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905485
Is this that you want?
ALTER PROCEDURE  [dbo].[TEL_SelectCountInVoiceChecked]      
   @ColumnName1 nvarchar(255)= NULL,  
   @TableName varchar(100) = NULL,
   @Value varchar(100) = NULL
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  
	
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT COUNT (*) FROM  [dbo].[' + @tableName + ']'  				
			
    IF  @ColumnName1  <> ''
		SET @SQL = @SQL + '  WHERE  ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '' +'=' +  CAST(@Value  AS NVARCHAR(30)) + ' AND ISNULL(DeleteRecord,0) = 0'   
	
    EXECUTE (@SQL)
	
END

Open in new window

NOTE: Changed a little bit your code just for better understanding when seeing it.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905486
Now with the @ColumnName1:
ALTER PROCEDURE  [dbo].[TEL_SelectCountInVoiceChecked]      
   @ColumnName1 nvarchar(255)= NULL,  
   @TableName varchar(100) = NULL,
   @Value varchar(100) = NULL
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  
	
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT COUNT (*) FROM  [dbo].[' + @tableName + ']'  				
			
    IF  @ColumnName1  <> ''
		SET @SQL = @SQL + '  WHERE  ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '' +'=' +  CAST(@Value  AS NVARCHAR(30)) + ' AND ISNULL(@ColumnName1,0) = 0'   
	
    EXECUTE (@SQL)
	
END

Open in new window

0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41905488
Try..

ALTER PROCEDURE  [dbo].[TEL_SelectCountInVoiceChecked]     
  
   @ColumnName1 nvarchar(255)= NULL,  
   @TableName varchar(100) = NULL,
   @Value varchar(100) = NULL
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  
	
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT COUNT (*)'
	SET @SQL = @SQL    + ' FROM  [dbo].[' + @tableName + ']'  				
			
       IF  @ColumnName1  <> ''
              SET @SQL = @SQL + '  WHERE  ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '' +'=' +  CAST(@Value  AS NVARCHAR(30))
				+ 'OR ' + CAST(@ColumnName1  AS NVARCHAR(30)) + ' IS NULL '
				
	  SET @SQL = @SQL  
	   		
	  
       EXECUTE (@SQL)
	
END

Open in new window

0
 

Author Comment

by:RIAS
ID: 41905489
Cheers!
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905490
Rias  ,  You selection will never give you the result....

At a single row you cannot have value and NULL simultaneously. Any way your choice..

You wanted WHERE ( @ColumnName1 is @value or @ColumnName1 = 0)'

and you accepted..

WHERE ( @ColumnName1 is @value AND @ColumnName1 = 0)'
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905492
RIAS, did you try my query? I think it has a bug so I'm posting a new version:
ALTER PROCEDURE  [dbo].[TEL_SelectCountInVoiceChecked]      
   @ColumnName1 nvarchar(255)= NULL,  
   @TableName varchar(100) = NULL,
   @Value varchar(100) = NULL
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  
	
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT COUNT (*) FROM  [dbo].[' + @tableName + ']'  				
			
    IF  @ColumnName1  <> ''
		SET @SQL = @SQL + '  WHERE  ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '' +'=' +  CAST(@Value  AS NVARCHAR(30)) + ' AND ISNULL(' + @ColumnName1 + ',0) = 0'   
	
    EXECUTE (@SQL)
	
END

Open in new window

0
 

Author Comment

by:RIAS
ID: 41905493
I changed that bit Pawan.
Your comment came just a second after accepting the slution.
Will split points.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905495
Rias  ,  You selection will never give you the result....

 You wanted WHERE ( @ColumnName1 is @value or @ColumnName1 = 0)'

 and you accepted..

 WHERE ( @ColumnName1 is @value AND  @ColumnName1 = 0)'
Go check again since I don't have that in my code.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905496
See the last one is also uses "AND"

Only I posted the correct version.. Any ways No issues.
0
 

Author Comment

by:RIAS
ID: 41905499
Pawan i changed that bit. Thanks.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905500
Checked.....

            
    IF  @ColumnName1  <> ''
            SET @SQL = @SQL + '  WHERE  ' +  CAST(@ColumnName1  AS NVARCHAR(30))+ '' +'=' +  CAST(@Value  AS NVARCHAR(30)) + ' AND ISNULL(@ColumnName1,0) = 0'  
      
    EXECUTE (@SQL)
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905501
I changed that bit Pawan.
 Your comment came just a second after accepting the slution.
 Will split points.
RIAS, I already told you to not be so fast to accept solutions. Anyway my code doesn't have that kind of comparision. I used ISNULL function so I'm not sure what you changed. Also, the @columnName1 shouldn't be inside the string and that's why I've posted the fix.
0
 

Author Comment

by:RIAS
ID: 41905503
Vitor,
i changed And to OR.  It give me the number.But,yes I need to recheck it .
Apologies.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905506
Checked.....
So, where's in RIAS's requirement that she asked for one case or another?
Requirement was: "Need to add one more parameter to check on the columnname1  where it is 0 or null example :"
0
 

Author Comment

by:RIAS
ID: 41905507
Vitor,
I needed to check it the column has value 0 or null
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41905509
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905515
RIAS, so requirement is not to ADD but REPLACE?
0
 

Author Comment

by:RIAS
ID: 41905518
Vitor,
It is just to check where it is null or given value(passed parameter)

Cheers
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41905519
Then you don't even need an OR:
ALTER PROCEDURE  [dbo].[TEL_SelectCountInVoiceChecked]      
   @ColumnName1 nvarchar(255)= NULL,  
   @TableName varchar(100) = NULL,
   @Value varchar(100) = NULL
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  
	
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT COUNT (*) FROM  [dbo].[' + @tableName + ']'  				
			
    IF  @ColumnName1  <> ''
		SET @SQL = @SQL + '  WHERE ISNULL(' + @ColumnName1 + ',0) = 0'   
	
    EXECUTE (@SQL)
	
END

Open in new window

0
 

Author Comment

by:RIAS
ID: 41905520
Thanks Vitor! Will try yuor solution as well.
Pawan's is giving me correct results as well..
0
 

Author Comment

by:RIAS
ID: 41905521
Vitor,
Where is parameter value ?
0
 

Author Comment

by:RIAS
ID: 41905523
Actually,Vitor you are right , there is no need of value.
But Pawan followed the requirement and the query is working so will split points .
0
 

Author Comment

by:RIAS
ID: 41905524
Can't thank you both enough.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905525
Where is parameter value ?
Ok, you lost me now. You requirement was:
"Need to add one more parameter to check on the columnname1 where it is 0 or null example :"
0
 

Author Comment

by:RIAS
ID: 41905528
Vitor,Ignore my comment:

Vitor,
Where is parameter value ?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905530
But Pawan followed the requirement and the query is working so will split points .
Review your requirement because I don't think he followed it.
0
 

Author Comment

by:RIAS
ID: 41905531
He did, i had asked to check null or value.
And his query does check it .

i did not clearly mention about '0' but,when you commented with the new query with '0' i rethought that i did not need 'value'.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41905535
Ok. It's your question and I'm good with your decision.
Cheers
1

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

628 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