February 15, 2008 at 11:09 am
Hello everyone,
I have a SQL statement thats looks correct but I can't seem to figure out why it's not working. first here is my statement:
declare @TableName varchar(30) --use for debugging
declare @ColumnName varchar(30) --use for debugging
set @TableName = 'Product' --use for debugging
set @ColumnName = 'PhotoID' --use for debugging
DECLARE @intCounter int
DECLARE @SQL varchar(200)
DECLARE @SuccessFlag bit
DECLARE @RandNum int
DECLARE @HolderNum varchar(7)
DECLARE @ParmDefinition varchar(100)
SET @ParmDefinition = '@SuccessFlag BIT OUTPUT'
SET @intCounter = 1
SET @SuccessFlag = 0
SET @HolderNum = ''
--WHILE @SuccessFlag = 0
BEGIN
WHILE @intCounter <= 7
BEGIN
SELECT @RandNum = (select numfrom dbo.random)
SET @HolderNum = @HolderNum + CAST(@RandNum AS varchar(1))
SET @RandNum = @HolderNum
--SELECT @HolderNum --Use for debugging
SET @intCounter = @intCounter + 1
END
--Take concatenated number and query for. Id it doesn't find, flip flag to true
SET @SQL = 'IF NOT EXISTS (SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + CAST(@HolderNum AS VARCHAR(7)) + ')
BEGIN
SET @SuccessFlag = 1
END'
print @SQL
EXECUTE sp_executesql N'@SQL', N'@ParmDefinition', @SuccessFlag OUTPUT
print @SuccessFlag --Use for debugging
END
print @RandNum --Use for debugging
The first issue looks to be syntax. The error I got was:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
When I looked at the line 1, it looks like it is correct, so I'm not sure why it is an error.
Line 1 is:
declare @TableName varchar(30)
The second issue is with the outut parameter. The execute statement runs some dynamic SQL:
ie IF NOT EXISTS (SELECT PhotoID FROM Product WHERE PhotoID = 5818318)
BEGIN
SET @SuccessFlag = 1
END
I know the PhotoID 5818318 doesnot exist in the table so it should set the output @Successflag parameter to 1, but its not. Can't seem to figure out why. Can anyone assist?
Thanks,
Strick
February 15, 2008 at 11:54 am
One thing I see is you are missing and end quote at the end of the SET @sql statement. The ')' isn't closed.
February 15, 2008 at 11:55 am
with dynamic sql it's always a good idea to print your @sql statements to see how they would execute.
February 15, 2008 at 11:58 am
On the output param, try changing the if exists to a select and checking @@rowcount. If @@rowcount <> 0 set your output param.
February 15, 2008 at 12:32 pm
Hi David looks like there is an end quote. Its right afer End
End'
Strick
February 15, 2008 at 12:42 pm
Sorry, I didn't notice that before. I would still keep the Begin End block out of the dynamic sql like the following:
SET @SQL = 'SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + CAST(@HolderNum AS VARCHAR(7)) + ')'
EXEC(@sql)
If @@rowcount > 0
BEGIN
SET @SuccessFlag = 1
END
February 15, 2008 at 12:43 pm
The print SQL returns
IF NOT EXISTS (SELECT PhotoID FROM Production.Product WHERE PhotoID = 3313553)
BEGIN
SET @SuccessFlag = 1)
END
Which looks correct
February 15, 2008 at 1:01 pm
I like how that @@rowcount looks. Ill try that.
Thanks,
Strick
February 15, 2008 at 1:03 pm
That should do the trick. Good luck.
February 15, 2008 at 1:46 pm
I think this has potential to work, but right now its not. The rowcount is not coming from the dynamic sql statement. It looks like its coming from the previous statement that ran in the procedure.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply