Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Stored procedure

Hello I have a stored procedure :


ALTER PROCEDURE  [dbo].[TVEL_CheckIoiceState]
(
     @tableName varchar(100) = null,
	 @ColumnName1  varchar(100) = null
	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	

IF EXISTS ( SELECT  *
            FROM    PROCEEXTRACT
            WHERE   InvoiceNumber = '''' +   CAST(@ColumnName1 AS Nvarchar(100) )  + ''''	
                    AND Checked IS NULL )
    SELECT  0 AS IsCheckedAll;    
ELSE
    SELECT  1 AS IsCheckedAll;
		
   End 
	

Open in new window


in a format :


 DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = '			
			
       
             IF EXISTS ( SELECT  *
            FROM    PROCEEXTRACT
            WHERE   InvoiceNumber = '''' +   CAST(@ColumnName1 AS Nvarchar(100) )  + ''''	
                    AND Checked IS NULL )
    SELECT  0 AS IsCheckedAll;    
ELSE
    SELECT  1 AS IsCheckedAll;
		
	
	 
	  SET @SQL = @SQL  
	   		
	  
       EXECUTE (@SQL)

Open in new window

Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Okay.  What is your question?
Avatar of RIAS

ASKER

Helo,
Get the query in:

 SET @SQL = '
Give this a whirl..
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '			
   IF EXISTS ( 
         SELECT  *
         FROM PROCEEXTRACT
         WHERE InvoiceNumber = '''' + CAST(@ColumnName1 AS Nvarchar(100) )  + '''' AND Checked IS NULL )
      SELECT 0 AS IsCheckedAll    
   ELSE
      SELECT 1 AS IsCheckedAll'
SET @SQL = @SQL 
EXECUTE (@SQL)

Open in new window

Avatar of RIAS

ASKER

Jim,
Where is end?
Avatar of RIAS

ASKER

and @columnname1 is not a parameter, need some adjustment on quotes
Hi Rias,
Pls try..

[Note - if  @ColumnName1 is not a parameter just hardcode the value here at bold <<CAST(@ColumnName1 AS VARCHAR(100)) >> ]

DECLARE @SQL VARCHAR(MAX)
DECLARE @ColumnName1 AS VARCHAR(100)
SET @SQL = 'IF EXISTS ( SELECT  *
            FROM    PROCEEXTRACT
            WHERE   InvoiceNumber = ' + '''' +   CAST(@ColumnName1 AS VARCHAR(100))  + ''''	+ 'AND Checked IS NULL )
			SELECT  0 AS IsCheckedAll    
		ELSE
			SELECT  1 AS IsCheckedAll'
EXECUTE (@SQL)

Open in new window


Sample Trial-

Table Creation

CREATE table X 
(
     ProcessName VARCHAR(50)
     ,Parent VARCHAR(50)
     ,Child VARCHAR(50)
)
GO

INSERT INTO X VALUES
     ('ShareDrafts','Job12','Job03')
     ,('ShareDrafts','Job13','Job58')
     ,('ShareDrafts','Job03','Job81')
     ,('ShareDrafts','Job58',NULL)
     ,('ShareDrafts','Job81','Job13')
     ,('Deposits', 'Job85', 'Job32')
     ,('Deposits', 'Job32', NULL)
     ,('Deposits', 'Job21', 'Job85')
GO

Open in new window


Query

DECLARE @SQL VARCHAR(MAX)
DECLARE @ColumnName1 AS VARCHAR(100) = 'Job12'
SET @SQL = 'IF EXISTS ( SELECT  *
            FROM    X
            WHERE   Parent = ' + '''' +   CAST(@ColumnName1 AS VARCHAR(100))  + ''''	+ 'AND Child IS NULL )
			SELECT  0 AS IsCheckedAll    
		ELSE
			SELECT  1 AS IsCheckedAll'
EXECUTE (@SQL)

Open in new window


Result

/*------------------------
DECLARE @SQL VARCHAR(MAX)
DECLARE @ColumnName1 AS VARCHAR(100) = 'Job12'
SET @SQL = 'IF EXISTS ( SELECT  *
            FROM    X
            WHERE   Parent = ' + '''' +   CAST(@ColumnName1 AS VARCHAR(100))  + ''''	+ 'AND Child IS NULL )
			SELECT  0 AS IsCheckedAll    
		ELSE
			SELECT  1 AS IsCheckedAll'
EXECUTE (@SQL)
------------------------*/
IsCheckedAll
------------
1

(1 row(s) affected)

Open in new window


Hope it helps !!
Avatar of RIAS

ASKER

Thanks Pawan, will try and brb
Avatar of RIAS

ASKER

Pawan
Got error:
Incorrect syntax near ')'.
Can you please post the entire script you are executing ?
Avatar of RIAS

ASKER

ALTER PROCEDURE  [dbo].[TREL_CheiceState]
(
     @tableName varchar(100) = null,
	 @ColumnName1  varchar(100) = null
	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	



	DECLARE @SQL VARCHAR(MAX)

SET @SQL = 'IF EXISTS ( SELECT  *
            FROM     PROACTEEXTRACT
            WHERE   InvoiceNumber = ' + '''' +   CAST(@ColumnName1 AS VARCHAR(100))  + ''''	+ ' AND Checked IS NULL )

			SELECT  0 AS IsCheckedAll    
		ELSE
			SELECT  1 AS IsCheckedAll'
EXECUTE (@SQL)

Open in new window

Avatar of RIAS

ASKER

Thanks!
Give me output of this..

ALTER PROCEDURE  [dbo].[TREL_CheiceState]
(
     @tableName varchar(100) = null,
	 @ColumnName1  varchar(100) = null
	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	



	DECLARE @SQL VARCHAR(MAX)

SET @SQL = 'IF EXISTS ( SELECT  *
            FROM     PROACTEEXTRACT
            WHERE   InvoiceNumber = ' + '''' +   CAST(@ColumnName1 AS VARCHAR(100))  + ''''	+ ' AND Checked IS NULL )

			SELECT  0 AS IsCheckedAll    
		ELSE
			SELECT  1 AS IsCheckedAll'
PRINT (@SQL)

Open in new window

its done?
Avatar of RIAS

ASKER

trying
Avatar of RIAS

ASKER

Msg 102, Level 15, State 1, Procedure TRoiceState, Line 50
Incorrect syntax near ')'.
Code is for -- [dbo].[TREL_CheiceState]
Error you are getting in TRoiceState

You are executing different stored procedure :)
Avatar of RIAS

ASKER

Nope, just edited the name for confidentiality
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RIAS

ASKER

trying
You are getting the error while altering the procedure. I have added END in the stored procedure. It should work fine now.
Avatar of RIAS

ASKER

ok
Avatar of RIAS

ASKER

Finally! Cheers!
Cheers Rias !!