Solved

Sql query

Posted on 2016-11-29
34
38 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
  • 15
  • 11
  • 6
  • +1
34 Comments
 
LVL 28

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 47

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 47

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 28

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 28

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 47

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 47

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 28

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 28

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 47

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 47

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 28

Expert Comment

by:Pawan Kumar
ID: 41905509
0
 
LVL 47

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 47

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 47

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 47

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 47

Expert Comment

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 46
Access denied running PowerPivot -SQL Server 2014 on Windows Server 2012 10 28
SQL Server 2012 r2 - Make Temp Table Query Faster 5 40
SQL Help 27 41
In this article I will describe the Copy Database Wizard 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 explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

786 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