RIAS
asked on
Stored procedure
Hello I have a stored procedure :
in a format :
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
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)
Okay. What is your question?
ASKER
Helo,
Get the query in:
SET @SQL = '
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)
ASKER
Jim,
Where is end?
Where is end?
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)) >> ]
Sample Trial-
Table Creation
Query
Result
Hope it helps !!
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)
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
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)
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)
Hope it helps !!
ASKER
Thanks Pawan, will try and brb
ASKER
Pawan
Got error:
Incorrect syntax near ')'.
Got error:
Incorrect syntax near ')'.
Can you please post the entire script you are executing ?
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)
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)
its done?
ASKER
trying
ASKER
Msg 102, Level 15, State 1, Procedure TRoiceState, Line 50
Incorrect syntax near ')'.
Incorrect syntax near ')'.
Code is for -- [dbo].[TREL_CheiceState]
Error you are getting in TRoiceState
You are executing different stored procedure :)
Error you are getting in TRoiceState
You are executing different stored procedure :)
ASKER
Nope, just edited the name for confidentiality
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
trying
You are getting the error while altering the procedure. I have added END in the stored procedure. It should work fine now.
ASKER
ok
ASKER
Finally! Cheers!
Cheers Rias !!