Solved

Sql query

Posted on 2016-11-29
34
20 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 17

Expert Comment

by:Pawan Kumar Khowal
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
 
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 45

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 45

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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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 17

Expert Comment

by:Pawan Kumar Khowal
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 45

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 45

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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 45

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 45

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 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41905509
0
 
LVL 45

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 45

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 45

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 45

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 45

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now