Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

Sql query

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
RIAS
Asked:
RIAS
  • 15
  • 11
  • 6
  • +1
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
Hi Rias ,

Not very clear? explain more?
0
 
RIASAuthor Commented:
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
 
RIASAuthor Commented:
need the syntax .

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Lee SavidgeCommented:
 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
 
RIASAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
RIASAuthor Commented:
Cheers!
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
I changed that bit Pawan.
Your comment came just a second after accepting the slution.
Will split points.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
See the last one is also uses "AND"

Only I posted the correct version.. Any ways No issues.
0
 
RIASAuthor Commented:
Pawan i changed that bit. Thanks.
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
Vitor,
i changed And to OR.  It give me the number.But,yes I need to recheck it .
Apologies.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
Vitor,
I needed to check it the column has value 0 or null
0
 
Pawan KumarDatabase ExpertCommented:
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
RIAS, so requirement is not to ADD but REPLACE?
0
 
RIASAuthor Commented:
Vitor,
It is just to check where it is null or given value(passed parameter)

Cheers
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
Thanks Vitor! Will try yuor solution as well.
Pawan's is giving me correct results as well..
0
 
RIASAuthor Commented:
Vitor,
Where is parameter value ?
0
 
RIASAuthor Commented:
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
 
RIASAuthor Commented:
Can't thank you both enough.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
Vitor,Ignore my comment:

Vitor,
Where is parameter value ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. It's your question and I'm good with your decision.
Cheers
1

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 11
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now