Solved

Sql query

Posted on 2016-11-29
34
40 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 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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 49

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 49

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 49

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 49

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 49

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 49

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 49

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 49

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 49

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 49

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 49

Expert Comment

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

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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