• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

View Sql in stored procedure

Hello,

How can I view this sql in the stored procedure
  IF NOT EXISTS( SELECT Ref
					FROM [CLIENT_REFERENCE] WHERE(					
				
				    ([OrgAuth] = @OrgAuth OR 
					([OrgAuth] is null AND @OrgAuth is null) OR
					([OrgAuth] is null AND @OrgAuth ='') OR
					([OrgAuth] ='' AND @OrgAuth is null))	
	
	   AND 
					([ClientName] = @ClientName OR 
					([ClientName] is null AND @ClientName is null) OR
					([ClientName] is null AND @ClientName ='') OR
					([ClientName] ='' AND @ClientName is null))
	   AND 
					([Sex] = @Sex OR 
					([Sex] is null AND @Sex is null) OR
					([Sex] is null AND @Sex ='') OR
					([Sex] ='' AND @Sex is null))					
	   AND 
					([Comments] = @Comments OR 
					([Comments] is null AND @Comments is null) OR
					([Comments] is null AND @Comments ='') OR
					([Comments] ='' AND @Comments is null))
       AND 
					([Field1] = @Field1 OR 
					([Field1] is null AND @Field1 is null) OR
					([Field1] is null AND @Field1 ='') OR
					([Field1] ='' AND @Field1 is null))))
	

Open in new window


Setting
DECLARE @SQL NVARCHAR(MAX)

    SET @SQL ='

allows me to view the sql .
Any suggestion on the above query
0
RIAS
Asked:
RIAS
  • 8
  • 5
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry but I don't understand what do you pretend.
Can you rephrase please?
0
 
RIASAuthor Commented:
Need to see the query when I debug the stored procedure.

Sql = "If exists ..."

When you set the query in set sql = we can view it or else there is no way we can debug it.
How can i set the above query as set sql =
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Still not sure if I got it.
If you run the command PRINT @SQL it will show you the query.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RIASAuthor Commented:
Yes agreed but , how do I print this query ?
IF NOT EXISTS( SELECT Ref
					FROM [CLIENT_REFERENCE] WHERE(					
				
				    ([OrgAuth] = @OrgAuth OR 
					([OrgAuth] is null AND @OrgAuth is null) OR
					([OrgAuth] is null AND @OrgAuth ='') OR
					([OrgAuth] ='' AND @OrgAuth is null))	
	
	   AND 
					([ClientName] = @ClientName OR 
					([ClientName] is null AND @ClientName is null) OR
					([ClientName] is null AND @ClientName ='') OR
					([ClientName] ='' AND @ClientName is null))
	   AND 
					([Sex] = @Sex OR 
					([Sex] is null AND @Sex is null) OR
					([Sex] is null AND @Sex ='') OR
					([Sex] ='' AND @Sex is null))					
	   AND 
					([Comments] = @Comments OR 
					([Comments] is null AND @Comments is null) OR
					([Comments] is null AND @Comments ='') OR
					([Comments] ='' AND @Comments is null))
       AND 
					([Field1] = @Field1 OR 
					([Field1] is null AND @Field1 is null) OR
					([Field1] is null AND @Field1 ='') OR
					([Field1] ='' AND @Field1 is null))))

Open in new window

0
 
RIASAuthor Commented:
Entire Sp is
ALTER PROCEDURE  [dbo].[TL_AdClieference]





  
    @Ref nvarchar(50) = NULL,	@OrgAuth nvarchar(3) = NULL,		@ClientName nvarchar(255) = NULL,
		@Sex [nvarchar](1) = NULL,	@Comments nvarchar(max)=  NULL,	@Field1 nvarchar(50)= NULL
	
	  
         
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  

   
 


				
					
	
		IF NOT EXISTS( SELECT Ref
					FROM [CLIENT_REFERENCE] WHERE(					
				
				    ([OrgAuth] = @OrgAuth OR 
					([OrgAuth] is null AND @OrgAuth is null) OR
					([OrgAuth] is null AND @OrgAuth ='') OR
					([OrgAuth] ='' AND @OrgAuth is null))	
	
	   AND 
					([ClientName] = @ClientName OR 
					([ClientName] is null AND @ClientName is null) OR
					([ClientName] is null AND @ClientName ='') OR
					([ClientName] ='' AND @ClientName is null))
	   AND 
					([Sex] = @Sex OR 
					([Sex] is null AND @Sex is null) OR
					([Sex] is null AND @Sex ='') OR
					([Sex] ='' AND @Sex is null))					
	   AND 
					([Comments] = @Comments OR 
					([Comments] is null AND @Comments is null) OR
					([Comments] is null AND @Comments ='') OR
					([Comments] ='' AND @Comments is null))
       AND 
					([Field1] = @Field1 OR 
					([Field1] is null AND @Field1 is null) OR
					([Field1] is null AND @Field1 ='') OR
					([Field1] ='' AND @Field1 is null))))
	
	BEGIN 
	 DECLARE @SQL NVARCHAR(MAX)


    SET @SQL ='	INSERT INTO [dbo].[CLIENT_REFERENCE] ( '

  	
    IF  @Ref <> ''
            SET @SQL = @SQL + '  [Ref] '	
			
    IF  @OrgAuth <> ''
            SET @SQL =@SQL + ',' + '  [OrgAuth]  ' 			 
    
    IF  @ClientName <> ''
            SET @SQL =@SQL + ',' + '  [ClientName]  ' 			 
  		 
     IF  @Sex <> ''
            SET @SQL =@SQL + ',' + '  [Sex]  ' 			 
     IF  @Comments <> ''
            SET @SQL =@SQL + ',' + '  [Comments] ' 			 
     IF  @Field1 <> ''
            SET @SQL =@SQL + ',' + '  [Field1]  ' 			 
    
	 SET @SQL = @SQL +')' + '      
     
     VALUES
	 ('

	 	
	    IF  @Ref <> ''
            SET @SQL =@SQL + '''' +   CAST(@Ref  AS  Nvarchar(50))  + '''' 		 
     	IF  @OrgAuth <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@OrgAuth   AS  Nvarchar(255))  + ''''
	  
   		IF  @ClientName <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@ClientName   AS  Nvarchar(20))  + '''' 		
	 		
		IF  @Sex  <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@Sex   AS  Nvarchar(1))  + '''' 	
	    IF  @Comments  <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@Comments    AS  Nvarchar(50))  + ''''  
	    IF  @Field1  <> ''
            SET @SQL = @SQL + ' , ''' +  CAST(@Field1  AS  Nvarchar(50))  + '''' 
	   
		
	  SET @SQL =  @SQL + ')'

       EXECUTE (@SQL)
	     INSERT INTO TRAVEL_DEBUG(SQL) VALUES (@SQL)
		Return @@Rowcount
	END
	
	ELSE 	
	BEGIN
	
	    Return 2
	END
	END

Open in new window

0
 
RIASAuthor Commented:
need to print or view
 IF NOT EXISTS( SELECT Ref
					FROM [CLIENT_REFERENCE] WHERE(					
				
				    ([OrgAuth] = @OrgAuth OR 
					([OrgAuth] is null AND @OrgAuth is null) OR
					([OrgAuth] is null AND @OrgAuth ='') OR
					([OrgAuth] ='' AND @OrgAuth is null))	
	
	   AND 
					([ClientName] = @ClientName OR 
					([ClientName] is null AND @ClientName is null) OR
					([ClientName] is null AND @ClientName ='') OR
					([ClientName] ='' AND @ClientName is null))
	   AND 
					([Sex] = @Sex OR 
					([Sex] is null AND @Sex is null) OR
					([Sex] is null AND @Sex ='') OR
					([Sex] ='' AND @Sex is null))					
	   AND 
					([Comments] = @Comments OR 
					([Comments] is null AND @Comments is null) OR
					([Comments] is null AND @Comments ='') OR
					([Comments] ='' AND @Comments is null))
       AND 
					([Field1] = @Field1 OR 
					([Field1] is null AND @Field1 is null) OR
					([Field1] is null AND @Field1 ='') OR
					([Field1] ='' AND @Field1 is null))))
	

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't print a query by itself.
However you can print a string and that's what you do when you deal with dynamic queries because you built it with a string variable and then execute the string content that's a T-SQL command.
0
 
RIASAuthor Commented:
Thats the question Vitor ,
How can I set this query to string?
0
 
RIASAuthor Commented:
example :


Set sql =  IF NOT EXISTS( SELECT Ref
                              FROM [CLIENT_REFERENCE] WHERE(                              
                        
                            ([OrgAuth] = @OrgAuth OR
                              ([OrgAuth] is null AND @OrgAuth is null) OR
                              ([OrgAuth] is null AND @OrgAuth ='') OR
                              ([OrgAuth] ='' AND @OrgAuth is null))      
      
         AND
                              ([ClientName] = @ClientName OR
                              ([ClientName] is null AND @ClientName is null) OR
                              ([ClientName] is null AND @ClientName ='') OR
                              ([ClientName] ='' AND @ClientName is null))
         AND
                              ([Sex] = @Sex OR
                              ([Sex] is null AND @Sex is null) OR
                              ([Sex] is null AND @Sex ='') OR
                              ([Sex] ='' AND @Sex is null))                              
         AND
                              ([Comments] = @Comments OR
                              ([Comments] is null AND @Comments is null) OR
                              ([Comments] is null AND @Comments ='') OR
                              ([Comments] ='' AND @Comments is null))
       AND
                              ([Field1] = @Field1 OR
                              ([Field1] is null AND @Field1 is null) OR
                              ([Field1] is null AND @Field1 ='') OR
                              ([Field1] ='' AND @Field1 is null))))
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, got it.
The following should work assuming that the variables are all strings:
DECLARE @SQL NVARCHAR(MAX)

SET @SQL ='
	IF NOT EXISTS( SELECT Ref
				FROM [CLIENT_REFERENCE] 
				WHERE (					
				    ([OrgAuth] = ' + @OrgAuth + ' OR 
					([OrgAuth] is null AND ' + @OrgAuth + ' is null) OR
					([OrgAuth] is null AND ' + @OrgAuth + ' ='''') OR
					([OrgAuth] ='''' AND ' + @OrgAuth + 'is null))	
				AND 
					([ClientName] = ' + @ClientName + ' OR 
					([ClientName] is null AND ' + @ClientName + ' is null) OR
					([ClientName] is null AND ' + @ClientName + ' ='''') OR
					([ClientName] ='''' AND ' + @ClientName + ' is null))
				AND 
					([Sex] = ' + @Sex + ' OR 
					([Sex] is null AND ' + @Sex + ' is null) OR
					([Sex] is null AND ' + @Sex + ' ='''') OR
					([Sex] ='''' AND ' + @Sex + ' is null))					
				AND 
					([Comments] = ' + @Comments + ' OR 
					([Comments] is null AND ' + @Comments + ' is null) OR
					([Comments] is null AND ' + @Comments + ' ='''') OR
					([Comments] ='''' AND ' + @Comments + ' is null))
				AND 
					([Field1] = ' + @Field1 + ' OR 
					([Field1] is null AND ' + @Field1 + ' is null) OR
					([Field1] is null AND ' + @Field1 + ' ='''') OR
					([Field1] ='''' AND ' + @Field1 + ' is null))))'

PRINT @SQL

Open in new window

1
 
RIASAuthor Commented:
Cool! Will try now and get back
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Btw, you can tune your query like this:
DECLARE @SQL NVARCHAR(MAX)=NULL

SET @OrgAuth=ISNULL(@OrgAuth,'')
SET @ClientName=ISNULL(@ClientName,'')
SET @Sex=ISNULL(@Sex,'')
SET @Comments=ISNULL(@Comments,'')
SET @Field1=ISNULL(@Field1,'')

SET @SQL ='
	IF NOT EXISTS (SELECT Ref
		FROM [CLIENT_REFERENCE] 
		WHERE ([OrgAuth] = ' + @OrgAuth + ' OR (ISNULL([OrgAuth],'''') is null AND ' + @OrgAuth + ' ='''')
		  AND ([ClientName] = ' + @ClientName + ' OR (ISNULL([ClientName],'''') ='''' AND ' + @ClientName + ' ='''')
		  AND ([Sex] = ' + @Sex + ' OR (ISNULL([Sex],'''') ='''' AND ' + @Sex + ' ='''')
		  AND ([Comments] = ' + @Comments + ' OR (ISNULL([Comments],'''') ='''' AND ' + @Comments + ' ='''')
		  AND ([Field1] = ' + @Field1 + ' OR (ISNULL([Field1],'''') ='''' AND ' + @Field1 + ' ='''')'

PRINT @SQL

Open in new window

1
 
RIASAuthor Commented:
Thanks ,minor syntax issues but,will fix it.Thanks for the solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now