February 14, 2008 at 4:00 pm
Hello,
Trying to get this statement working:
IF NOT EXISTS('SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @HolderNum)
BEGIN
--Stuff
END
The three variables which are being fed into this procedure to build this sample statement:
SELECT ProductID FROM Products WHERE ProductID = 123456
I'm then checking to see if there are any records. The error message I'm getting is:
Msg 102, Level 15, State 1, Procedure UniqueNumber, Line 50
Incorrect syntax near 'SELECT '.
Can anyone see what I'm doing wrong?
Thanks,
Strick
February 14, 2008 at 5:09 pm
You cant do it like that. You have to use truly dynamic sql.
e.g.
DECLARE @sql nvarchar(500)
SET @sql = N'
IF NOT EXISTS(SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @HolderNum + ')
BEGIN
--Stuff
END
'
execute sp_executesql @sql
February 14, 2008 at 5:24 pm
You cannot do it as they say.
Do something like this:
DECLARE @sql varchar(1000)
DECLARE @Table varchar(1000)
DECLARE @ID1 int
DECLARE @ID2 int
SET @Table = 'sysobjects'
SET @ID1 = 1
SET @ID2 = 5
CREATE TABLE #TMP (ID int)
SET @sql = 'INSERT INTO #TMP SELECT ID FROM ' + @Table + ' WHERE id BETWEEN ' + CONVERT(varchar, @ID1) + ' AND ' + CONVERT(varchar, @ID2)
EXEC (@SQL)
IF EXISTS(SELECT * FROM #TMP) BEGIN
print 'STUFF'
END
DROP TABLE #TMP
It would be much better to create a function which performes the required check.
You know about functions in MSSQL2005 don't you?
Regards,
Valentin.
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 14, 2008 at 5:33 pm
Echoing the previous response but avoiding the local temp table and having to convert all of your stuff to do into dynamic sql as well as the test.
For exists() you generally want to let the server figure out what field to select instead of selecting a particular field.
create table #Products (ProductId int, ProdName varchar(10))
insert into #Products select 1, 'apple' union select 2, 'pear' union select 3, 'orange'
-- select * from #products
declare
@cSentence NVARCHAR(500),
@ParmDefinition NVARCHAR(500),
@ReturnCode int,
@TableName varchar(100),
@RowCount int,
@NotExist bit,
@ColumnName varchar(20),
@HolderNum int
set @TableName = '#Products'
set @ColumnName = 'ProductId'
set @HolderNum = 99
set @ParmDefinition = N'@NotExist bit output'
set @cSentence = N'if not exists (Select * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + convert(nvarchar, @HolderNum) + ') set @NotExist = 1 '
exec @ReturnCode = Sp_Executesql @cSentence, @ParmDefinition, @NotExist output
if @NotExist = 1
begin
print 'stuff 2222'
end
February 14, 2008 at 5:48 pm
Good solution ksullivan. I like it. It is much better than making the entire code block dynamic sql and is quite efficient.
February 14, 2008 at 5:51 pm
I mentioned MSQSQL function unthoughtfully.
EXEC is not allowed in functions.
So now how do we make the code ksullivan suggested reusable? I am sure people would like to call this procedure more than once and parameterise the input (table name, column name, value).
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 14, 2008 at 6:02 pm
How does this one look?
CREATE PROCEDURE checknotexists
(
@TableName varchar(100),
@ColumnName varchar(20),
@Value int
)
AS
declare
@cSentence NVARCHAR(500),
@ParmDefinition NVARCHAR(500),
@ReturnCode int,
@RowCount int,
@NotExist bit
SET @NotExist = 0
set @ParmDefinition = N'@NotExist bit output'
set @cSentence = N'if not exists (Select * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + convert(nvarchar, @Value) + ') set @NotExist = 1 '
exec @ReturnCode = Sp_Executesql @cSentence, @ParmDefinition, @NotExist output
IF @NotExist = 1
BEGIN
SELECT @NotExist
END
GO
and then run this
EXEC checknotexists 'sysobjects','id','11'
IF @@ROWCOUNT > 0
BEGIN
PRINT 'stuff'
END
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 14, 2008 at 6:15 pm
I'm partial to stating conditions positively so instead of not exists this uses exists and may finish a little quicker.
create proc CheckExists
@TableName varchar(100),
@ColumnName varchar(20),
@HolderNum int,
@DoesExist bit = 0 output
as
declare
@cSentence NVARCHAR(500),
@ParmDefinition NVARCHAR(500),
@ReturnCode int,
@RowCount int
set @ParmDefinition = N'@DoesExist bit output'
set @cSentence = N'if exists (Select * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + convert(nvarchar, @HolderNum) + ') set @DoesExist = 1 else set @DoesExist = 0 '
exec @ReturnCode = Sp_Executesql @cSentence, @ParmDefinition, @DoesExist output
-------- then run
declare @DoesExist bit
exec CheckExists @TableName = 'Products', @ColumnName = 'ProductId', @HolderNum = 3, @DoesExist = @DoesExist output
if @DoesExist = 0
begin
print 'stuff 2222'
end
February 14, 2008 at 7:23 pm
Thanks guys,
Look like what Adam has works good. Now, for my "stuff" in the if statement how would I set a variable bit value equal to 1? I'm really close but it looks like its my syntax:
DECLARE @sql nvarchar(500)
SET @sql = N'IF NOT EXISTS(SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @HolderNum + ')
BEGIN
SET ' + @SuccessFlag + ' = ' + 1 + '
END'
execute sp_executesql @sql
Error get is:
Msg 402, Level 16, State 1, Line 37
The data types varchar and bit are incompatible in the add operator.
Strick
February 14, 2008 at 9:18 pm
+ ' = ' + @HolderNum + ')
HolderNum is an integer so when you are trying to concatenate it to the string, SQL server is trying to add the value to the string and cannot covert the string to an integer. You need to cast the value to a varchar.
I have posted code below that will resolve your issue. You will note that you do not need to set the execution of the stored procedure equal to anything to gain access to the output parameter. This is a step that can be excluded. This code will look pretty much identical to code that has already been posted, but prettier :D.
Additionally, I do not know how you plan to use this process, but you may want to look at making this a stored procedure, so that the code becuase reusable for more than this particular query as ksullivan suggested.
SET NOCOUNT ON
GO
CREATE TABLE #Products(
ProductId INT,
ProdName VARCHAR(10)
)
INSERT INTO #Products
SELECT 1, 'apple' UNION
SELECT 2, 'pear' UNION
SELECT 3, 'orange'
DECLARE@sql NVARCHAR(4000),
@ParmDefinition NVARCHAR(500),
@TableName VARCHAR(100),
@SuccessFlag INT,
@ColumnName VARCHAR(20),
@HolderNum INT
SET @TableName = '#Products'
SET @ColumnName = 'ProductId'
SET @HolderNum = 99
SET @ParmDefinition = N'@SuccessFlag BIT OUTPUT'
SET @sql = N'
IF NOT EXISTS
(SELECT ' + @ColumnName + '
FROM ' + @TableName + '
WHERE ' + @ColumnName + ' = ' + CAST(@HolderNum AS VARCHAR(5)) + ')
BEGIN
SET @SuccessFlag = 1
END
'
EXECUTE sp_executesql @sql, @ParmDefinition, @SuccessFlag OUTPUT
--IF SUCCESS FLAG = 1 THE OBJECT DOES NOT EXIST
IF @SuccessFlag = 1
BEGIN
--DO STUFF
PRINT 'stuff 2222'
END
GO
DROP TABLE #Products
GO
SET NOCOUNT OFF
GO
February 14, 2008 at 9:26 pm
I dont get why you want to keep the variable (@SuccessFlag) outside of the SQL string?
What you want to do is this:
@SuccessFlag=1
And it should be placed in a string as ASCII text and will be executet by sp_executesql
Look at this, was it what you wanted?
DECLARE @sql nvarchar(500)
SET @sql = N'IF NOT EXISTS(SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @HolderNum + ') SET @SuccessFlag = 1'
execute sp_executesql @sql
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply