If exists(@sql) does not actually execute @sql and then evaluates the
resultset. What you want to do is this:
declare @presql varchar(4000)
select @presql = '
IF EXISTS(SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as
varchar(6))+')
BEGIN
UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate as varchar(12))+'''
END
ELSE
BEGIN
INSERT INTO ' + @CurrentDB + '(btDate, btTime) VALUES ('''+ cast(@FileDate
as varchar(12)) + ''',' + cast(@ImportTime as varchar(6)) + ')
END
'
exec sp_executesql @presql
[quoted text, click to view] "Bane" <bane@noname.net> wrote in message
news:p%pnd.65955$Vf.3307515@news000.worldonline.dk...
> Hi all
>
> In the SP below im (trying to) do some dynamic sql. As you can see the
> table
> to use is set as a variable and the 'exec' method used to run the
> sqlstatements.
> My problem is that the 'if exists' method is not doing what i was hoping
> it
> could do.
> The @presql command returns somewhere between 0 or 50 rows (give and
> take) -
> i just want the 'if exists' part to determine if the select statement
> returns something or not since i then will have to update a current row -
> or
> insert a new one.
> Even if there is no rows returned, the 'if exists' command will return
> true
> :-/
>
> Any suggestions to a different way of approach...?
>
> Thanks in advance :-)
>
> ######## Stored procedure start ########
> [various @ variables]
> ...
> declare @presql varchar(200)
> select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+
> cast(@SiteID as varchar(6))+''
>
> IF exists((@presql))
> BEGIN
> declare @UpdateSQL varchar(400)
> set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate as
> varchar(12))+''''
> exec(@UpdateSQL)
> END
>
> ELSE
>
> BEGIN
> declare @InsertSQL varchar(2000)
>
> select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '
> (btDate,
> btTime)
> VALUES
> ('''+ cast(@FileDate as varchar(12)) + ''','
> + cast(@ImportTime as varchar(6)) + ')'
>
> EXEC(@InsertSQL)
> END
> ######## Stored procedure end ########
>
>
